Populate A Dropdown From Another Dropdown

I have a page that has two dropdowns on it. When the page loads, the first box is populated from an Access table. Here's the tricky part for me...

(Just when I think I understand how "postback" works, I find I'm wrong again!)

I have the dropdown set to autopostback=true. When the user selects a choice in this dropdown, the page postsback (I think) and I want it to then query another table in the same database to fill the second dropdown list AND keep the original dropdown list's selection.

For some reason, I can get it to fill the original dropdown, but then when it postsback, I get an error because my SQL statement to load the second dropdown looks like this:

Dim objSeriesDA As New OleDb.OleDbDataAdapter("SELECT * FROM Series WHERE fldProviderID=" & lstProvider.SelectedItem.Value & " ORDER BY fldSeriesNumber DESC", objSeriesCN)

Apparently, when the page postsback, the original dropdown list loses its selection, causing the SQL statement above to error out on a null reference.

Any ideas?

If need be, I can throw this up on a server so you all can see what I'm talking about... I can post the full source as well.

Thanks!

HWKY
:)wrap the code that is filling your first drop down list with this:

If Not Page.IsPostBack Then
'...your code here.
End If
Does anyone have a link to some information that explains PostBack in depth?
well, this link (http://www.15seconds.com/Issue/020102.htm) helped me with some basics on the page lifecycle when i was having user control postback problems. Really, there's not much more to the kind of postback stuff in your first post. If you know how the page loads and when control events are evaluated, that's pretty much it. Were you looking for information on how to roll your own PostBack handler or something else?

Populate a DropDownList

I need to populate a dropdownlist with items form one SQL Table in SqlDataSource1 but the selected value and the data text need to match the ones that are already into another SQL Table in SqlDataSource2.

Any Sugestions?

You can manually compare the data between the tables in codebehind using the classes found in System.Data.SqlClient & System.Data.Sql. Then you can use the DropDownList.Items.Add(new ListItem()) methods to add new items to your dropdownlist.

Populate an Array with DB records

Hi Friends,
How can I populate an Array with the information of an Table in SQL?
Thanks... and sorry my english...
Grillo"Guilherme Grillo" <webdev@.rncomtotal.com.br> wrote in message
news:usThK6uIIHA.1212@.TK2MSFTNGP05.phx.gbl...

> How can I populate an Array with the information of an Table in SQL?
http://www.google.co.uk/search?hl=e...br />
st&meta=
Mark Rae
ASP.NET MVP
http://www.markrae.net
Thank you Mark Rae...
Solve my problem!
Now I have another problem.
try
{
int[] pointList = new Int32[60];
int[] idList = new Int32[60];
int i;
for (i = 1; i <= 57; i++)
{
// Cria o comando que seleciona os resultados atuais
commSELECT = new SqlCommand("SELECT * FROM contResults WHERE id = " + i +
"", conn);
// Executa o comando e coloca os resultados em uma Matriz
conn.Open();
readerSELECT = commSELECT.ExecuteReader(CommandBehavior.CloseConnection);
readerSELECT.Read();
pointList[i] = readerSELECT["contResults"];
idList[i] = readerSELECT["id"];
readerSELECT.Close();
Error 1 Cannot implicitly convert type 'object' to 'int'. An explicit
conversion exists (are you missing a cast?)
How can I convert this type'
Here I have the same error:
*int[] respostasList = new Int32[20];
respostasList[1] = RadioButtonList1.SelectedItem.Value;
Sorry my english and thanks again.
Grillo
"Mark Rae [MVP]" <mark@.markNOSPAMrae.net> wrote in message
news:OgWpENvIIHA.4296@.TK2MSFTNGP04.phx.gbl...
> "Guilherme Grillo" <webdev@.rncomtotal.com.br> wrote in message
> news:usThK6uIIHA.1212@.TK2MSFTNGP05.phx.gbl...
>
> http://www.google.co.uk/search?hl=e... />
list&meta=
>
> --
> Mark Rae
> ASP.NET MVP
> http://www.markrae.net
Problem Solved!
Thanks everybody...
Grillo
ps: sorry my english..
"Guilherme Grillo" <webdev@.rncomtotal.com.br> wrote in message
news:uJeouewIIHA.4712@.TK2MSFTNGP04.phx.gbl...
> Thank you Mark Rae...
> Solve my problem!
> Now I have another problem.
>
> try
> {
> int[] pointList = new Int32[60];
> int[] idList = new Int32[60];
> int i;
> for (i = 1; i <= 57; i++)
> {
> // Cria o comando que seleciona os resultados atuais
> commSELECT = new SqlCommand("SELECT * FROM contResults WHERE id = " + i +
> "", conn);
> // Executa o comando e coloca os resultados em uma Matriz
> conn.Open();
> readerSELECT = commSELECT.ExecuteReader(CommandBehavior.CloseConnection);
> readerSELECT.Read();
> pointList[i] = readerSELECT["contResults"];
> idList[i] = readerSELECT["id"];
> readerSELECT.Close();
>
> Error 1 Cannot implicitly convert type 'object' to 'int'. An explicit
> conversion exists (are you missing a cast?)
> How can I convert this type'
> Here I have the same error:
> *int[] respostasList = new Int32[20];
> respostasList[1] = RadioButtonList1.SelectedItem.Value;
>
> Sorry my english and thanks again.
> Grillo
> "Mark Rae [MVP]" <mark@.markNOSPAMrae.net> wrote in message
> news:OgWpENvIIHA.4296@.TK2MSFTNGP04.phx.gbl...
>

Populate an Array with DB records

Hi Friends,

How can I populate an Array with the information of an Table in SQL?

Thanks... and sorry my english...

Grillo"Guilherme Grillo" <webdev@.rncomtotal.com.brwrote in message
news:usThK6uIIHA.1212@.TK2MSFTNGP05.phx.gbl...

Quote:

Originally Posted by

How can I populate an Array with the information of an Table in SQL?

http://www.google.co.uk/search?hl=e...Arraylist&meta=
--
Mark Rae
ASP.NET MVP
http://www.markrae.net
Thank you Mark Rae...

Solve my problem!

Now I have another problem.

try

{

int[] pointList = new Int32[60];

int[] idList = new Int32[60];

int i;

for (i = 1; i <= 57; i++)

{

// Cria o comando que seleciona os resultados atuais

commSELECT = new SqlCommand("SELECT * FROM contResults WHERE id = " + i +
"", conn);

// Executa o comando e coloca os resultados em uma Matriz

conn.Open();

readerSELECT = commSELECT.ExecuteReader(CommandBehavior.CloseConn ection);

readerSELECT.Read();

pointList[i] = readerSELECT["contResults"];

idList[i] = readerSELECT["id"];

readerSELECT.Close();

Error 1 Cannot implicitly convert type 'object' to 'int'. An explicit
conversion exists (are you missing a cast?)

How can I convert this type??

Here I have the same error:

*int[] respostasList = new Int32[20];

respostasList[1] = RadioButtonList1.SelectedItem.Value;

Sorry my english and thanks again.

Grillo

"Mark Rae [MVP]" <mark@.markNOSPAMrae.netwrote in message
news:OgWpENvIIHA.4296@.TK2MSFTNGP04.phx.gbl...

Quote:

Originally Posted by

"Guilherme Grillo" <webdev@.rncomtotal.com.brwrote in message
news:usThK6uIIHA.1212@.TK2MSFTNGP05.phx.gbl...
>

Quote:

Originally Posted by

>How can I populate an Array with the information of an Table in SQL?

>
http://www.google.co.uk/search?hl=e...Arraylist&meta=
>
>
--
Mark Rae
ASP.NET MVP
http://www.markrae.net

Problem Solved!

Thanks everybody...
Grillo

ps: sorry my english..

"Guilherme Grillo" <webdev@.rncomtotal.com.brwrote in message
news:uJeouewIIHA.4712@.TK2MSFTNGP04.phx.gbl...

Quote:

Originally Posted by

Thank you Mark Rae...
>
Solve my problem!
>
Now I have another problem.
>
>
try
>
{
>
int[] pointList = new Int32[60];
>
int[] idList = new Int32[60];
>
int i;
>
for (i = 1; i <= 57; i++)
>
{
>
// Cria o comando que seleciona os resultados atuais
>
commSELECT = new SqlCommand("SELECT * FROM contResults WHERE id = " + i +
"", conn);
>
// Executa o comando e coloca os resultados em uma Matriz
>
conn.Open();
>
readerSELECT = commSELECT.ExecuteReader(CommandBehavior.CloseConn ection);
>
readerSELECT.Read();
>
pointList[i] = readerSELECT["contResults"];
>
idList[i] = readerSELECT["id"];
>
readerSELECT.Close();
>
>
Error 1 Cannot implicitly convert type 'object' to 'int'. An explicit
conversion exists (are you missing a cast?)
>
How can I convert this type??
>
Here I have the same error:
>
*int[] respostasList = new Int32[20];
>
respostasList[1] = RadioButtonList1.SelectedItem.Value;
>
>
Sorry my english and thanks again.
>
Grillo
>
"Mark Rae [MVP]" <mark@.markNOSPAMrae.netwrote in message
news:OgWpENvIIHA.4296@.TK2MSFTNGP04.phx.gbl...

Quote:

Originally Posted by

>"Guilherme Grillo" <webdev@.rncomtotal.com.brwrote in message
>news:usThK6uIIHA.1212@.TK2MSFTNGP05.phx.gbl...
>>

Quote:

Originally Posted by

>>How can I populate an Array with the information of an Table in SQL?

>>
>http://www.google.co.uk/search?hl=e...Arraylist&meta=
>>
>>
>--
>Mark Rae
>ASP.NET MVP
>http://www.markrae.net

>
>

populate array with sql table data vb.net

hello,
I wanted to populate an array with the data from sql table, but not
sure how to go about it.
This is the array iam using at present, but i dont want to provide the
values. Instead i want to query them from sql table t_holidays.
Dim HolidayList() As Date = {#7/4/2006#, #7/6/2006#, #7/13/2006#,
#7/19/2006#, #12/24/2006#, #12/25/2006#, #12/29/2006#, #1/1/2007#}
Any suggestions how to go about this one. Your time is greatly
appreciated.
cheers, Sharon.Simple example (i assume you have basic knowledge about ADO.NET)
Imports System.Data
Imports System.Data.SqlClient
Partial Class _Default
Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, ByVal e As
System.EventArgs) Handles Me.Load
Dim dates As System.Collections.Generic.List(Of DateTime) = GetDates()
End Sub
Private Const ConnectionString As String =
" server=ServerName;uid=UserName;password=
Password;database=DatebaseName"
Private Function GetDates() As System.Collections.Generic.List(Of DateTime)
Dim result As New System.Collections.Generic.List(Of DateTime)
Dim connection As New SqlConnection(ConnectionString)
Dim command As New SqlCommand("select DateColumn FROM TableName WHERE
Condition", connection)
Dim reader As SqlDataReader
Try
connection.Open()
reader = command.ExecuteReader()
While reader.Read()
result.Add(reader.GetDateTime(0))
End While
Catch ex As Exception
Throw ex
Finally
connection.Dispose()
End Try
Return result
End Function
End Class
Milosz Skalecki
MCP, MCAD
"Sharon" wrote:

> hello,
> I wanted to populate an array with the data from sql table, but not
> sure how to go about it.
> This is the array iam using at present, but i dont want to provide the
> values. Instead i want to query them from sql table t_holidays.
> Dim HolidayList() As Date = {#7/4/2006#, #7/6/2006#, #7/13/2006#,
> #7/19/2006#, #12/24/2006#, #12/25/2006#, #12/29/2006#, #1/1/2007#}
> Any suggestions how to go about this one. Your time is greatly
> appreciated.
> cheers, Sharon.
>
Thanks for the Reply, But its giving me an error
System.Collections.Generic.List is not defined, Please let me know
where did it went wrong.
cheers, Sharon.
Milosz Skalecki wrote:
> Simple example (i assume you have basic knowledge about ADO.NET)
> Imports System.Data
> Imports System.Data.SqlClient
> Partial Class _Default
> Inherits System.Web.UI.Page
> Protected Sub Page_Load(ByVal sender As Object, ByVal e As
> System.EventArgs) Handles Me.Load
> Dim dates As System.Collections.Generic.List(Of DateTime) = GetDates()
> End Sub
> Private Const ConnectionString As String =
> " server=ServerName;uid=UserName;password=
Password;database=DatebaseName"
> Private Function GetDates() As System.Collections.Generic.List(Of DateTim
e)
> Dim result As New System.Collections.Generic.List(Of DateTime)
> Dim connection As New SqlConnection(ConnectionString)
> Dim command As New SqlCommand("select DateColumn FROM TableName WHERE
> Condition", connection)
> Dim reader As SqlDataReader
> Try
> connection.Open()
> reader = command.ExecuteReader()
> While reader.Read()
> result.Add(reader.GetDateTime(0))
> End While
> Catch ex As Exception
> Throw ex
> Finally
> connection.Dispose()
> End Try
> Return result
> End Function
> End Class
>
> --
> Milosz Skalecki
> MCP, MCAD
>
> "Sharon" wrote:
>
I am using .NET Framework version, is this class available in this
version
Milosz Skalecki wrote:
> Simple example (i assume you have basic knowledge about ADO.NET)
> Imports System.Data
> Imports System.Data.SqlClient
> Partial Class _Default
> Inherits System.Web.UI.Page
> Protected Sub Page_Load(ByVal sender As Object, ByVal e As
> System.EventArgs) Handles Me.Load
> Dim dates As System.Collections.Generic.List(Of DateTime) = GetDates()
> End Sub
> Private Const ConnectionString As String =
> " server=ServerName;uid=UserName;password=
Password;database=DatebaseName"
> Private Function GetDates() As System.Collections.Generic.List(Of DateTim
e)
> Dim result As New System.Collections.Generic.List(Of DateTime)
> Dim connection As New SqlConnection(ConnectionString)
> Dim command As New SqlCommand("select DateColumn FROM TableName WHERE
> Condition", connection)
> Dim reader As SqlDataReader
> Try
> connection.Open()
> reader = command.ExecuteReader()
> While reader.Read()
> result.Add(reader.GetDateTime(0))
> End While
> Catch ex As Exception
> Throw ex
> Finally
> connection.Dispose()
> End Try
> Return result
> End Function
> End Class
>
> --
> Milosz Skalecki
> MCP, MCAD
>
> "Sharon" wrote:
>
Howdy,
i automatically assumed you were using framework 2.0. Please use
System.Collections.ArrayList instead of
System.Collections.Generic.List(Of DateTime)
regards
Milosz Skalecki
MCP, MCAD
"Sharon" wrote:

> I am using .NET Framework version, is this class available in this
> version
> Milosz Skalecki wrote:
>

populate array with sql table data vb.net

hello,

I wanted to populate an array with the data from sql table, but not
sure how to go about it.

This is the array iam using at present, but i dont want to provide the
values. Instead i want to query them from sql table t_holidays.

Dim HolidayList() As Date = {#7/4/2006#, #7/6/2006#, #7/13/2006#,
#7/19/2006#, #12/24/2006#, #12/25/2006#, #12/29/2006#, #1/1/2007#}

Any suggestions how to go about this one. Your time is greatly
appreciated.

cheers, Sharon.Simple example (i assume you have basic knowledge about ADO.NET)

Imports System.Data
Imports System.Data.SqlClient

Partial Class _Default
Inherits System.Web.UI.Page

Protected Sub Page_Load(ByVal sender As Object, ByVal e As
System.EventArgs) Handles Me.Load

Dim dates As System.Collections.Generic.List(Of DateTime) = GetDates()

End Sub

Private Const ConnectionString As String =
"server=ServerName;uid=UserName;password=Password;d atabase=DatebaseName"

Private Function GetDates() As System.Collections.Generic.List(Of DateTime)

Dim result As New System.Collections.Generic.List(Of DateTime)
Dim connection As New SqlConnection(ConnectionString)
Dim command As New SqlCommand("select DateColumn FROM TableName WHERE
Condition", connection)
Dim reader As SqlDataReader

Try

connection.Open()
reader = command.ExecuteReader()

While reader.Read()
result.Add(reader.GetDateTime(0))
End While

Catch ex As Exception
Throw ex
Finally
connection.Dispose()
End Try

Return result

End Function

End Class

--
Milosz Skalecki
MCP, MCAD

"Sharon" wrote:

Quote:

Originally Posted by

hello,
>
I wanted to populate an array with the data from sql table, but not
sure how to go about it.
>
This is the array iam using at present, but i dont want to provide the
values. Instead i want to query them from sql table t_holidays.
>
Dim HolidayList() As Date = {#7/4/2006#, #7/6/2006#, #7/13/2006#,
#7/19/2006#, #12/24/2006#, #12/25/2006#, #12/29/2006#, #1/1/2007#}
>
Any suggestions how to go about this one. Your time is greatly
appreciated.
>
cheers, Sharon.
>
>

Thanks for the Reply, But its giving me an error

System.Collections.Generic.List is not defined, Please let me know
where did it went wrong.

cheers, Sharon.

Milosz Skalecki wrote:

Quote:

Originally Posted by

Simple example (i assume you have basic knowledge about ADO.NET)
>
Imports System.Data
Imports System.Data.SqlClient
>
Partial Class _Default
Inherits System.Web.UI.Page
>
Protected Sub Page_Load(ByVal sender As Object, ByVal e As
System.EventArgs) Handles Me.Load
>
Dim dates As System.Collections.Generic.List(Of DateTime) = GetDates()
>
End Sub
>
Private Const ConnectionString As String =
"server=ServerName;uid=UserName;password=Password;d atabase=DatebaseName"
>
Private Function GetDates() As System.Collections.Generic.List(Of DateTime)
>
Dim result As New System.Collections.Generic.List(Of DateTime)
Dim connection As New SqlConnection(ConnectionString)
Dim command As New SqlCommand("select DateColumn FROM TableName WHERE
Condition", connection)
Dim reader As SqlDataReader
>
Try
>
connection.Open()
reader = command.ExecuteReader()
>
While reader.Read()
result.Add(reader.GetDateTime(0))
End While
>
Catch ex As Exception
Throw ex
Finally
connection.Dispose()
End Try
>
Return result
>
End Function
>
End Class
>
>
--
Milosz Skalecki
MCP, MCAD
>
>
"Sharon" wrote:
>

Quote:

Originally Posted by

hello,

I wanted to populate an array with the data from sql table, but not
sure how to go about it.

This is the array iam using at present, but i dont want to provide the
values. Instead i want to query them from sql table t_holidays.

Dim HolidayList() As Date = {#7/4/2006#, #7/6/2006#, #7/13/2006#,
#7/19/2006#, #12/24/2006#, #12/25/2006#, #12/29/2006#, #1/1/2007#}

Any suggestions how to go about this one. Your time is greatly
appreciated.

cheers, Sharon.

I am using .NET Framework version, is this class available in this
version
Milosz Skalecki wrote:

Quote:

Originally Posted by

Simple example (i assume you have basic knowledge about ADO.NET)
>
Imports System.Data
Imports System.Data.SqlClient
>
Partial Class _Default
Inherits System.Web.UI.Page
>
Protected Sub Page_Load(ByVal sender As Object, ByVal e As
System.EventArgs) Handles Me.Load
>
Dim dates As System.Collections.Generic.List(Of DateTime) = GetDates()
>
End Sub
>
Private Const ConnectionString As String =
"server=ServerName;uid=UserName;password=Password;d atabase=DatebaseName"
>
Private Function GetDates() As System.Collections.Generic.List(Of DateTime)
>
Dim result As New System.Collections.Generic.List(Of DateTime)
Dim connection As New SqlConnection(ConnectionString)
Dim command As New SqlCommand("select DateColumn FROM TableName WHERE
Condition", connection)
Dim reader As SqlDataReader
>
Try
>
connection.Open()
reader = command.ExecuteReader()
>
While reader.Read()
result.Add(reader.GetDateTime(0))
End While
>
Catch ex As Exception
Throw ex
Finally
connection.Dispose()
End Try
>
Return result
>
End Function
>
End Class
>
>
--
Milosz Skalecki
MCP, MCAD
>
>
"Sharon" wrote:
>

Quote:

Originally Posted by

hello,

I wanted to populate an array with the data from sql table, but not
sure how to go about it.

This is the array iam using at present, but i dont want to provide the
values. Instead i want to query them from sql table t_holidays.

Dim HolidayList() As Date = {#7/4/2006#, #7/6/2006#, #7/13/2006#,
#7/19/2006#, #12/24/2006#, #12/25/2006#, #12/29/2006#, #1/1/2007#}

Any suggestions how to go about this one. Your time is greatly
appreciated.

cheers, Sharon.

Howdy,

i automatically assumed you were using framework 2.0. Please use
System.Collections.ArrayList instead of
System.Collections.Generic.List(Of DateTime)

regards

--
Milosz Skalecki
MCP, MCAD

"Sharon" wrote:

Quote:

Originally Posted by

I am using .NET Framework version, is this class available in this
version
Milosz Skalecki wrote:

Quote:

Originally Posted by

Simple example (i assume you have basic knowledge about ADO.NET)

Imports System.Data
Imports System.Data.SqlClient

Partial Class _Default
Inherits System.Web.UI.Page

Protected Sub Page_Load(ByVal sender As Object, ByVal e As
System.EventArgs) Handles Me.Load

Dim dates As System.Collections.Generic.List(Of DateTime) = GetDates()

End Sub

Private Const ConnectionString As String =
"server=ServerName;uid=UserName;password=Password;d atabase=DatebaseName"

Private Function GetDates() As System.Collections.Generic.List(Of DateTime)

Dim result As New System.Collections.Generic.List(Of DateTime)
Dim connection As New SqlConnection(ConnectionString)
Dim command As New SqlCommand("select DateColumn FROM TableName WHERE
Condition", connection)
Dim reader As SqlDataReader

Try

connection.Open()
reader = command.ExecuteReader()

While reader.Read()
result.Add(reader.GetDateTime(0))
End While

Catch ex As Exception
Throw ex
Finally
connection.Dispose()
End Try

Return result

End Function

End Class

--
Milosz Skalecki
MCP, MCAD

"Sharon" wrote:

Quote:

Originally Posted by

hello,
>
I wanted to populate an array with the data from sql table, but not
sure how to go about it.
>
This is the array iam using at present, but i dont want to provide the
values. Instead i want to query them from sql table t_holidays.
>
Dim HolidayList() As Date = {#7/4/2006#, #7/6/2006#, #7/13/2006#,
#7/19/2006#, #12/24/2006#, #12/25/2006#, #12/29/2006#, #1/1/2007#}
>
Any suggestions how to go about this one. Your time is greatly
appreciated.
>
cheers, Sharon.
>
>

>
>

populate datagrid from listbox value

I have to populate a list box from a db table and when the user clicks on
the value in the listbox I need to then populate a datagrid with detailed
information for the highlighted row.
How can i do that?Try this?
ASP.NET: Master/Detail View using a DropDownList and a DataGrid
[url]http://authors.aspalliance.com/aldotnet/examples/masterdetail_ddl_datagrid.aspx[/u
rl]
"Mike" <me@.me.com> wrote in message
news:ORHOgJWXEHA.2364@.TK2MSFTNGP12.phx.gbl...
>I have to populate a list box from a db table and when the user clicks on
> the value in the listbox I need to then populate a datagrid with detailed
> information for the highlighted row.
> How can i do that?
>

populate datagrid from listbox value

I have to populate a list box from a db table and when the user clicks on
the value in the listbox I need to then populate a datagrid with detailed
information for the highlighted row.

How can i do that?Try this?

ASP.NET: Master/Detail View using a DropDownList and a DataGrid

http://authors.aspalliance.com/aldo...l_datagrid.aspx

"Mike" <me@.me.com> wrote in message
news:ORHOgJWXEHA.2364@.TK2MSFTNGP12.phx.gbl...
>I have to populate a list box from a db table and when the user clicks on
> the value in the listbox I need to then populate a datagrid with detailed
> information for the highlighted row.
> How can i do that?

Populate drop down list with sql query (contains IF)

I have a drop down list and it is to get it's 'text' and 'value' from a table. The table contains 3 columns:
Title, Name, Id1, Id2
if Id1 is NOT blank then the 'text' value of the drop down list should be:
Name + "(" + Id1 + ")"
else
Name + "(" + Id2 + ")"
I believe the easiest way is to construct the above IF statement is to use SQL query, then pass the parameter to the DataTextField property. However, i seemed to be getting an error. Also any help on the SQL IF statement will be appreciated...
This is what i have so far...(without the IF statement as i'm not sure how to construct the IF statement in SQL)
Sub Page_Load(sender as Object, e as EventArgs)
If Not Page.IsPostBack Then
dropDownList.DataSource = ToGetDataTable()
dropDownList.DataValueField = "MyNewColumn"
dropDownList.DataTextField = "Name"
dropDownList.DataBind()
End if
End Sub

Function ToGetDataTable() As System.Data.IDataReader
Dim connectionString As String = "server='(local)';..................."
Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)

Dim queryString As String = "SELECT Title, Name + ' ( ' + Id1 + ')' AS MyNewColumn FROM MyTableName"
Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection

dbConnection.Open
Dim dataReader As System.Data.IDataReader = dbCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection)

Return dataReader
End Function

Do you have sufficient rights on your SQL Server box to create auser-defined function? That seems to be the best way to go aboutthis; you would simply select Title, Name (which is a terrible name fora database field), and MyFunction(Name, Id1, Id2) where MyFunctionwould do the work.
If you don't have this option, you could pull all necessary columns,put them into a DataTable, and then run through it, appending theappropriate values in one particular column.

Populate dropdownlist using table data + text

I have a drop down list and the first option need to be 'ALL', then the remaining options are retrieved from a column of a table. How can this be achieved?
Thanks...I'm not sure it's the best way of doing it, but this is what I do (I'm using a DataSet, it is only mildly different with a SQLDataReader):
Dim myDS as DataSet
' Fill myDS here
Dim myDR as DataRow
myDropDownList.Items.Add(new ListItem("All"))
For Each myDR in myDS.Tables(0).Rows
myDropDownList.Items.Add(new ListItem(myDR.Items("ColumnNameOrIndexHere")))
Next
I do this because the obvious issue with using DataBind is that it flushes whatever data you already had in there. However, looking at the documentation for ListItems, it appears you can do this:
'Get data and bind it to myDropDownList (assuming you are already doing this)
myDropDownList.Items.Insert(0, new ListItem("All"))
The documentation for ListItemCollection in DropDownList (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemwebuiwebcontrolslistitemcollectionmemberstopic.asp) might help if neither of these are what you are looking for.

// Bind the DDL to the datasource first...
DDL.DataValueField = "value-column-name-here";
DDL.DataTextField = "text-column-name-here";
DDL.DataSource = dataTable;
DDL.DataBind();

// Add the "ALL" selection at the 1st (#0) position...
DDL.Items.Insert(0, new ListItem("ALL", "ALL"));
NC...

I think the most quicker way is to populatethe ArrayList from the Database using the SqlDataReader (In case youare using SQL Server Database).
The first element of the ArrayList will be "All" which you can simply add like this:
ArrayList a = new ArrayList();
a.Add("All");
after that you use SqlDataReader to populate the ArrayList and finally bind the list to the DropDownList.
Let me know if you need to know more about this technique!

And just how could you possibly think that going through the process of creating an ArrayList out of the data and then binding the ArrayList to the DDL be faster and better than just binding the DDL directly to the data?
NC...

It all depends on how many rows are in the Database Table and how many you are inserting in the DataSet.

In what way? Creating the ArrayList would take more time regardless, even not taking into account the GC having to be called to discard it afterwards.
NC...

DataSet is a collection of DataTables, DataRows and DataColumns so does;nt that make DataSet more heavier than an ArrayList.

Why wouldn't you just use the DataReader as the data source?
DDL.DataValueField = "value-column-name-here";
DDL.DataTextField = "text-column-name-here";
DDL.DataSource = dataReader;
DDL.DataBind();

// Add the "ALL" selection at the 1st (#0) position...
DDL.Items.Insert(0, new ListItem("ALL", "ALL"));
dataReader.Close();
Then you don't have the overhead of creating the ArrayList, or a collection of any kind. After all, you'll never need the ArrayList again after binding the DDL.
NC...

Ohh no no no!
Don't ever send the DataReader on the presentation layer. DataReaderworks while the connection is open so this means you are sending theopen connection on the presentation layer.
NEVER NEVER NEVER send the DataReader on the presentation layer. Thisis also like mixing the DataBase layer with presentation layer with noBLL in between.

azamsharp wrote:

Ohh no no no!
Don't ever send the DataReader on the presentation layer. DataReader works while the connection is open so this means you are sending the open connection on the presentation layer.
NEVER NEVER NEVER send the DataReader on the presentation layer. This is also like mixing the DataBase layer with presentation layer with no BLL in between.

Weren't you the one transferring the DataReader into an ArrayList? My data layer does nothing but produce DataTables and DataSets, so there are no Connection objects, Command objects, DataReader objects, etc in my presentation layer (nor in the entire application layer for that matter).
Here is the part that I disagree with you on:

azamsharp wrote:

I think the most quicker way is to populate the ArrayList from the Database using the SqlDataReader (In case you are using SQL Server Database).

It's no quicker to create an ArrayList than a DataTable from a DataReader (if you need the code, let me know and I'll post it). In fact, unless you create a custom class and a custom collection, you'll be very restricted with an ArrayList as you have no access to column names to attach the DataValueField and DataTextField properties of the DDL, so you'll have to use the same value for both.
NC...

>>Weren't you the one transferring the DataReader into an ArrayList?
Thats done in the business logic layer and not the presentation layer which you have demonstrated.


I would agree with azamsharp if it was an application with a static connection. However, in the case of a web page, your web server is maintaining the DB Connection either while you are populating the ArrayList from the DataReader or while performing the DataBind. No connection is maintained by the client computer, only the web server.

Well then we're talking the same thing then. My way uses a DataTable and your way uses an ArrayList. both constructed in the data layer.

The main problem, as I see it, is that using the ArrayList, you'll be restricted to one column (unless you create a custom class, stuffing that into the ArrayList, which will also take more time and code), since you have no access to column names in an ArrayList to attach to the DataValueField and DataTextField properties of the DDL, therefore, you'll have to use the same value for both.

NC...

Populate fields

Hi:

I am looking for lookup table ability so the user will not have re-enter student information data. I have a table called for example "StudentInfo" - lookup table and another table called StudentActivity. My environment is VS.net/SQL/C#

I would like to have a form that would allow user to to select specific student (eg. from the drop down or listbox from StudentInfo table) and populate StudentActivity table based fields with the values from StudentInfo table, based on user selection.

ENTRY FORM (bound to StudentActivityTable)

Select Student: drop down menu (from StudentInfo)

Student name (populated from StudentInfo)

Student age (populated from StudentInfo)

Student address (populated from StudentInfo_

Location (user input)

Date: (user input) etc.

I would appreciate your assistance.

Danka

You need to get to grips with the basics of data access. http://quickstarts.asp.net/QuickStartv20/aspnet/doc/data/default.aspx

Here is a tutorial that is doing something like what you describe. It pairs a DropDownList with a DetailsView: http://msdn2.microsoft.com/en-us/library/aa581790.aspx

The DetailsView displays individual records, and can be used to edit or delete them. If the DetailsView control doesn't give you what you want, you could try the FormView control. I think the FormView offers greater flexibility in formatting the appearance.

Hope this helps

Yes, this is a good introduction for me. Thanks.

Hi:

I implemented it as a find compnay feature that provides user with a view of detailed company information based on the company selection in the drop down list.

Regarding my previous question related to drop down selection I tried the following code on someones advise. When user selects a value from the pull down menu this code updates templated field which is bound to database field. In the insert template I made this control not visible. I probably could implement the same on the edit template. It should be a better way than this. I am all new to it so I would appreaciate your thoughts.

} protectedvoid DropDownList1_SelectedIndexChanged(object sender,EventArgs e) { TextBox txtName = (TextBox)DetailsView1.FindControl("txtInsertLegalCompanyName"); DropDownList ddlName = (DropDownList)DetailsView1.FindControl("DropDownList1"); txtName.Text = ddlName.SelectedValue;

Now, I also have radio buttons list with two values Yes and No (true and false). I need some piece of code that would allow to show the selected value on the edit template (yes or no). Check box list allow to select both values True and False so I probably need some piece of code here so if one is selected the other one is deselected. I would also like to dispay Yes instead true, in vb I would use IIF... statment...

Your help would be appreaciated.

Populate GridView with DataSet

I was using a DataView to bind records from a DB table to a DataGrid
using the following code:
Dim sqlDapter As SqlDataAdapter
Dim dSet As DataSet
Dim dView As DataView
sqlDapter = New SqlDataAdapter(strSQL, sqlConn)
dSet = New DataSet()
dView = New DataView
sqlDapter.Fill(dSet, "Users")
dView = dSet.Tables("Users").DefaultView
dgUsers.DataSource = dView
dgUsers.DataBind()
The above works fine but I did like to bind the DataGrid to a GridView
instead of a DataView as the above code shows. What I did is deleted
the DataView from the above code & added a GridView i.e. all the
instances of the DataView were replaced with GridView i.e. changed the
variable name 'dView' to 'gView' but I get this error:
Value of type 'System.Data.DataView' cannot be converted to
'System.Web.UI.WebControls.GridView'
pointing to this line
gView = dSet.Tables("Users").DefaultView
How do I populate the GridView with the DataSet?Your DataSet and DataView variables stay the same. You will simply set
the DataSource of the GridView to your existing dView variable.
Using what you started with...

> sqlDapter.Fill(dSet, "Users")
> dView = dSet.Tables("Users").DefaultView

> dgUsers.DataSource = dView
> dgUsers.DataBind()
Assuming the GridView is named gvUsers, make the last two lines...
gvUsers.DataSource = dView
gvUsers.DataBind()
The DataGrid and GridView both take a DataView as the DataSource.
Brennan Stehling
http://brennan.offwhite.net/blog/
rn5a@.rediffmail.com wrote:
> I was using a DataView to bind records from a DB table to a DataGrid
> using the following code:
> Dim sqlDapter As SqlDataAdapter
> Dim dSet As DataSet
> Dim dView As DataView
> sqlDapter = New SqlDataAdapter(strSQL, sqlConn)
> dSet = New DataSet()
> dView = New DataView
> sqlDapter.Fill(dSet, "Users")
> dView = dSet.Tables("Users").DefaultView
> dgUsers.DataSource = dView
> dgUsers.DataBind()
> The above works fine but I did like to bind the DataGrid to a GridView
> instead of a DataView as the above code shows. What I did is deleted
> the DataView from the above code & added a GridView i.e. all the
> instances of the DataView were replaced with GridView i.e. changed the
> variable name 'dView' to 'gView' but I get this error:
> Value of type 'System.Data.DataView' cannot be converted to
> 'System.Web.UI.WebControls.GridView'
> pointing to this line
> gView = dSet.Tables("Users").DefaultView
> How do I populate the GridView with the DataSet?

Populate GridView with DataSet

I was using a DataView to bind records from a DB table to a DataGrid
using the following code:

Dim sqlDapter As SqlDataAdapter
Dim dSet As DataSet
Dim dView As DataView

sqlDapter = New SqlDataAdapter(strSQL, sqlConn)

dSet = New DataSet()
dView = New DataView

sqlDapter.Fill(dSet, "Users")
dView = dSet.Tables("Users").DefaultView

dgUsers.DataSource = dView
dgUsers.DataBind()

The above works fine but I did like to bind the DataGrid to a GridView
instead of a DataView as the above code shows. What I did is deleted
the DataView from the above code & added a GridView i.e. all the
instances of the DataView were replaced with GridView i.e. changed the
variable name 'dView' to 'gView' but I get this error:

Value of type 'System.Data.DataView' cannot be converted to
'System.Web.UI.WebControls.GridView'

pointing to this line

gView = dSet.Tables("Users").DefaultView

How do I populate the GridView with the DataSet?Your DataSet and DataView variables stay the same. You will simply set
the DataSource of the GridView to your existing dView variable.

Using what you started with...

Quote:

Originally Posted by

sqlDapter.Fill(dSet, "Users")
dView = dSet.Tables("Users").DefaultView

Quote:

Originally Posted by

dgUsers.DataSource = dView
dgUsers.DataBind()

Assuming the GridView is named gvUsers, make the last two lines...

gvUsers.DataSource = dView
gvUsers.DataBind()

The DataGrid and GridView both take a DataView as the DataSource.

Brennan Stehling
http://brennan.offwhite.net/blog/
rn5a@.rediffmail.com wrote:

Quote:

Originally Posted by

I was using a DataView to bind records from a DB table to a DataGrid
using the following code:
>
Dim sqlDapter As SqlDataAdapter
Dim dSet As DataSet
Dim dView As DataView
>
sqlDapter = New SqlDataAdapter(strSQL, sqlConn)
>
dSet = New DataSet()
dView = New DataView
>
sqlDapter.Fill(dSet, "Users")
dView = dSet.Tables("Users").DefaultView
>
dgUsers.DataSource = dView
dgUsers.DataBind()
>
The above works fine but I did like to bind the DataGrid to a GridView
instead of a DataView as the above code shows. What I did is deleted
the DataView from the above code & added a GridView i.e. all the
instances of the DataView were replaced with GridView i.e. changed the
variable name 'dView' to 'gView' but I get this error:
>
Value of type 'System.Data.DataView' cannot be converted to
'System.Web.UI.WebControls.GridView'
>
pointing to this line
>
gView = dSet.Tables("Users").DefaultView
>
How do I populate the GridView with the DataSet?

Populating 2 dim array.

Hello, I have a 7 column data table in ADO.net with 200 rows. I want to learn how to copy the data in my data table to myarray (6,199) as Integar.

Dim myarray (6, 199) As Integar??

For Each myarray In myDataSet.Tables??

??

??

Next??

Hello my friend,

Use the following: -

Dim myarray(6, 199) As Integer

For i As Integer = 0 To dt.Rows.Count - 1

For j As Integer = 0 To dt.Columns.Count - 1
myarray(i, j) = Convert.ToInt32(dt.Rows(i).Item(j))
Next
Next

Kind regards

Scotty

Populating a dropdownlist

Hi Everyone,

I am trying to figure out how to populate my dropdown list with data from my table. What happens is I call a method that expects an id by using this method I want to keep looping through the recordset and populate my dropdown list with teh values I require.

Here is teh code I have attempted to write this code appears in my page load

 ddlVersions.Items.Clear()
 Dim _CVersion As Content = ContentManager.GetContentVersionList(Page.Request.Params("PID"))
 Dim Version As Content
 For Each Version In _CVersion
 ddlVersions.DataTextField = "CoPgVersion"
 ddlVersions.DataValueField = "CoPgID"
 ddlVersions.DataBind()
 Next

Here is my method

 Public Shared Function GetContentVersionList(ByVal CoPgID As Integer) As Content
 ' Get the list of Content form the datastore
 Dim _Data As SqlDataReader
 Dim _Content As New Content
 _Data = SqlHelper.ExecuteReader(ConfigurationSettings.AppSettings("DataStoreConnection"), CommandType.StoredProcedure, "Content_get", New SqlParameter("@dotnet.itags.org.CoPgID", CoPgID))
 While _Data.Read
 _Content = PopulateObjectFromSqlDataReader(_Data)
 End While
 _Data.Close()
 Return _Content
 End Function

My stored procedure is basically the following SQL Statement

Select * from content where CoPgID = @dotnet.itags.org.CoPgID

I am simply getting teh syntax wrong but I am unsure on how to correctly code the dropdownlist so it populates.

While I am here once I get the drop down list to work is there a way I can make the drop down list highlight as if selected the most current record.

Basically the dropdownlist will display all teh version of my content if I am at version 4 I want version for to appear along with the rest of teh versions but for it to be select rather than "Please Select" is this doable?

I look forward to any help one may be able to give

Kind Regards

BradHi I will try to help a bit but take it with reservation as I am just beginner using Matrix which generates code for me, but just to say that when I was populating drop down list following instructions from the matrix book I did not loop through the recordset.
Steps were:
-to create function with sql statement in it and reterning the recordset
-to assign this function to data source of drop down list so:
dropdownlist.DataSource=above function
dropdownlist.DataBind()

these 2 lines are putting your recordset in the list already but they are always within some event
for example page load event
i hope this helps
anqa
Anga is quite correct. This is the way to bind data to controls in ASP.NET.

Seethis example on LearnASP.com for more details.

Populating a DropDownList

Hi all,
I have a MS SQLServer database which has two tables, 'images' and
'photographers'. The photographer table contains a field for
PhotographerID and a one for PhotographerName. The image table also
contains a field for photographerID which is used to join the two
tables.
I'm having problems with populating a dropDownList using the complete
set of photographerNames and IDs from the 'photographer' table but
binding the selected value to the Image table.
I've set up a DataSet and added both tables to it and now have the list
populated but can't bind the list to the photographerID field in the
'image' table.
Anyone done anything like this before? if so any help would be much
appreciated,
thanks in advance,
PaulSo if i understand, you have a Dataset with 2 DataTables.
The DataMember property of the Dropdownlist object will allow you to specify
which dataTable to use from the datasource which is in this case the DataSet
So for example
DropDownList dll = new DropDownList();
ddl.DataSource = MyDataSetWith2Tables;
ddl.DataMember = "Photographers"
...
ddl.DataBind()
HTH,
Tony
"p.mc" <paul.mcmanus.uk@.googlemail.com> wrote in message
news:1142616298.580083.168470@.j33g2000cwa.googlegroups.com...
> Hi all,
> I have a MS SQLServer database which has two tables, 'images' and
> 'photographers'. The photographer table contains a field for
> PhotographerID and a one for PhotographerName. The image table also
> contains a field for photographerID which is used to join the two
> tables.
> I'm having problems with populating a dropDownList using the complete
> set of photographerNames and IDs from the 'photographer' table but
> binding the selected value to the Image table.
> I've set up a DataSet and added both tables to it and now have the list
> populated but can't bind the list to the photographerID field in the
> 'image' table.
> Anyone done anything like this before? if so any help would be much
> appreciated,
> thanks in advance,
> Paul
>
Hi Tony,
thanks for your help, however i'm still unable to get it working as i
would like. Using the method you suggested i've got the ddl displaying
the complete list of photographers from the photographers table. After
i select this i would like to store the result (photog ID) in the
photographerID field in the 'imageTable'. So i guess i need to bind the
DataTextField and DataValueField to the photographer Table but bind the
result to the image table.
I'm sure i must be missing something simple here, but can't think what
it is. Any further help would be appreciated.
thanks, paul
Anthony Merante wrote:
> So if i understand, you have a Dataset with 2 DataTables.
> The DataMember property of the Dropdownlist object will allow you to speci
fy
> which dataTable to use from the datasource which is in this case the DataS
et
> So for example
> DropDownList dll = new DropDownList();
> ddl.DataSource = MyDataSetWith2Tables;
> ddl.DataMember = "Photographers"
> ...
> ddl.DataBind()
> HTH,
> Tony
> "p.mc" <paul.mcmanus.uk@.googlemail.com> wrote in message
> news:1142616298.580083.168470@.j33g2000cwa.googlegroups.com...
Assuming that the image field that you want to place in selected value
is of type System.String, you need to create a SQL statement or
(preferably) a stored procedure that will do the join. Then bind the
dropdown list to that...
using (SqlConnection conPhotographerAndImage = new
SqlConnection([PUT CONNECTION STRING HERE])
{
SqlCommand cmdGet = new
SqlCommand("GetPhotographersAndImages", conPhotographerAndImage);
cmdGet.CommandType = CommandType.StoredProcedure;
//You could also omit the above line and set command with
text instead...
//SELECT p.PhotographerName,i.ImageName FROM Photographer
p INNER JOIN Image i ON p.PhotographerId=i.PhotographerId;
conPhotographerAndImage.Open();
SqlDataReader drdPhotographerAndImage =
cmdGet.ExecuteReader();
this.ddlMyDropDownList.DataSource =
drdPhotographerAndImage;
this.ddlMyDropDownList.DataValueField = "ImageName";
this.ddlMyDropDownList.DataTextField =
"PhotographerName";
this.ddlMyDropDownList.DataBind();
drdPhotographerAndImage.Close();
conPhotographerAndImage.Close();
ListItem lstAll = new ListItem("[all]", "999999");
this.ddlEventType.Items.Insert(this.ddlEventType.Items.Count, lstAll);
this.ddlEventType.SelectedIndex =
this.ddlEventType.Items.Count - 1;
}
HTH,
JP
JP
thanks for your help, just to clarify - i've included a table in the
dataSet which is a SQL view that contains the INNER JOIN you suggest.
Is it necessary to explicitly create this join for the ddl?
The webForm i'm working on will contain 15 to 20 of these foreign-key
joins bound to ddls.
I'm sure that what i'm trying to do must be a very common technique (is
it not one of the basic principles of relational databases?), surely
using foreign keys to link to other tables can be handled more
efficiently than this?
thanks again
The point is to set the datasource up as *one* entity (not two tables).
This where the join comes in. You configure the query with a join to
return a result set that has all of your data. You then simply set
DataTextField to the name of the field that you want to show in the
dropdown, set DataValue field to the name of the field that you want to
be in the corresponding values, and then bind the DDL to the one
entity. At least that's the way I have always done it.
JP
Thanks for your help, much appreciated.
I'll have a go and see where i get,
thanks again

Populating a DropDownList

Hi all,

I have a MS SQLServer database which has two tables, 'images' and
'photographers'. The photographer table contains a field for
PhotographerID and a one for PhotographerName. The image table also
contains a field for photographerID which is used to join the two
tables.

I'm having problems with populating a dropDownList using the complete
set of photographerNames and IDs from the 'photographer' table but
binding the selected value to the Image table.

I've set up a DataSet and added both tables to it and now have the list
populated but can't bind the list to the photographerID field in the
'image' table.

Anyone done anything like this before? if so any help would be much
appreciated,

thanks in advance,

PaulSo if i understand, you have a Dataset with 2 DataTables.

The DataMember property of the Dropdownlist object will allow you to specify
which dataTable to use from the datasource which is in this case the DataSet

So for example

DropDownList dll = new DropDownList();
ddl.DataSource = MyDataSetWith2Tables;
ddl.DataMember = "Photographers"
...
ddl.DataBind()

HTH,
Tony

"p.mc" <paul.mcmanus.uk@.googlemail.com> wrote in message
news:1142616298.580083.168470@.j33g2000cwa.googlegr oups.com...
> Hi all,
> I have a MS SQLServer database which has two tables, 'images' and
> 'photographers'. The photographer table contains a field for
> PhotographerID and a one for PhotographerName. The image table also
> contains a field for photographerID which is used to join the two
> tables.
> I'm having problems with populating a dropDownList using the complete
> set of photographerNames and IDs from the 'photographer' table but
> binding the selected value to the Image table.
> I've set up a DataSet and added both tables to it and now have the list
> populated but can't bind the list to the photographerID field in the
> 'image' table.
> Anyone done anything like this before? if so any help would be much
> appreciated,
> thanks in advance,
> Paul
Hi Tony,

thanks for your help, however i'm still unable to get it working as i
would like. Using the method you suggested i've got the ddl displaying
the complete list of photographers from the photographers table. After
i select this i would like to store the result (photog ID) in the
photographerID field in the 'imageTable'. So i guess i need to bind the
DataTextField and DataValueField to the photographer Table but bind the
result to the image table.

I'm sure i must be missing something simple here, but can't think what
it is. Any further help would be appreciated.

thanks, paul

Anthony Merante wrote:
> So if i understand, you have a Dataset with 2 DataTables.
> The DataMember property of the Dropdownlist object will allow you to specify
> which dataTable to use from the datasource which is in this case the DataSet
> So for example
> DropDownList dll = new DropDownList();
> ddl.DataSource = MyDataSetWith2Tables;
> ddl.DataMember = "Photographers"
> ...
> ddl.DataBind()
> HTH,
> Tony
> "p.mc" <paul.mcmanus.uk@.googlemail.com> wrote in message
> news:1142616298.580083.168470@.j33g2000cwa.googlegr oups.com...
> > Hi all,
> > I have a MS SQLServer database which has two tables, 'images' and
> > 'photographers'. The photographer table contains a field for
> > PhotographerID and a one for PhotographerName. The image table also
> > contains a field for photographerID which is used to join the two
> > tables.
> > I'm having problems with populating a dropDownList using the complete
> > set of photographerNames and IDs from the 'photographer' table but
> > binding the selected value to the Image table.
> > I've set up a DataSet and added both tables to it and now have the list
> > populated but can't bind the list to the photographerID field in the
> > 'image' table.
> > Anyone done anything like this before? if so any help would be much
> > appreciated,
> > thanks in advance,
> > Paul
Assuming that the image field that you want to place in selected value
is of type System.String, you need to create a SQL statement or
(preferably) a stored procedure that will do the join. Then bind the
dropdown list to that...

using (SqlConnection conPhotographerAndImage = new
SqlConnection([PUT CONNECTION STRING HERE])
{
SqlCommand cmdGet = new
SqlCommand("GetPhotographersAndImages", conPhotographerAndImage);
cmdGet.CommandType = CommandType.StoredProcedure;

//You could also omit the above line and set command with
text instead...
//SELECT p.PhotographerName,i.ImageName FROM Photographer
p INNER JOIN Image i ON p.PhotographerId=i.PhotographerId;

conPhotographerAndImage.Open();

SqlDataReader drdPhotographerAndImage =
cmdGet.ExecuteReader();

this.ddlMyDropDownList.DataSource =
drdPhotographerAndImage;
this.ddlMyDropDownList.DataValueField = "ImageName";
this.ddlMyDropDownList.DataTextField =
"PhotographerName";
this.ddlMyDropDownList.DataBind();

drdPhotographerAndImage.Close();
conPhotographerAndImage.Close();

ListItem lstAll = new ListItem("[all]", "999999");

this.ddlEventType.Items.Insert(this.ddlEventType.I tems.Count, lstAll);
this.ddlEventType.SelectedIndex =
this.ddlEventType.Items.Count - 1;
}

HTH,
JP
JP

thanks for your help, just to clarify - i've included a table in the
dataSet which is a SQL view that contains the INNER JOIN you suggest.
Is it necessary to explicitly create this join for the ddl?

The webForm i'm working on will contain 15 to 20 of these foreign-key
joins bound to ddls.

I'm sure that what i'm trying to do must be a very common technique (is
it not one of the basic principles of relational databases?), surely
using foreign keys to link to other tables can be handled more
efficiently than this?

thanks again
The point is to set the datasource up as *one* entity (not two tables).
This where the join comes in. You configure the query with a join to
return a result set that has all of your data. You then simply set
DataTextField to the name of the field that you want to show in the
dropdown, set DataValue field to the name of the field that you want to
be in the corresponding values, and then bind the DDL to the one
entity. At least that's the way I have always done it.

JP
Thanks for your help, much appreciated.
I'll have a go and see where i get,
thanks again

Populating a Dropdownlist from a SQL table

Hello guys, I am trying to populate a ddlist on my form from a sql table, but am having trouble coming up with the code.
Here is what i got so far. Im not sure what goes after the read? Thanks you very much.

Sub GetCauseofLoss()

Dim connAs SqlClient.SqlConnection
Dim cmdAs SqlClient.SqlCommand

Dim drAs SqlClient.SqlDataReader

Dim intFieldAsInteger

conn =New SqlClient.SqlConnection

conn.ConnectionString = Replace(Application("ConnectStage"), "Driver={SQL Server}; ", "")

cmd = conn.CreateCommand

conn.Open()

cmd.CommandText = "SELECT * FROM CauseOfLoss"

dr = cmd.ExecuteReader

While dr.Read

dgddLossCode.SelectedValue &= vbNewLine

For intField = 0To dr.FieldCount - 1

?????

Next

EndWhile

dr.Close()

conn.Close()

EndSub

' Assume myDDL is an established web control
Dim myDS as DataSet
' Populate DataSet however you want.
Dim currRecord as DataRow
myDDL.Items.Add(new ListItem("", "blank")) ' Just addin
for each currRecord in myDS.Tables(0).Rows
myDDL.Items.add(new ListItem(currRecord(0)))
next
The index in parenthesis after Tables can be replaced with a string of a table name you proved.
The index in parenthesis after currRecord can be replaced with a string of a column name you proved or the name of the column from the database.
I haven't used DataReader before, thus my example above with a DataSet. Looking at the documentation for it, you would do something similar (and in this case, probably easier).

While dr.Read()
dgddLossCode.Items.Add(new ListItem(dr.Item(COLUMN_INDEX_OR_NAME_HERE)) ' assuming that is you DropDownList
End While
The above example is assuming you only want 1 column. If you want multiple columns from the same row, you can name each column individually, or you can iterate through them or use a method of the class. You can find those athttp://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatasqlclientsqldatareadermemberstopic.asp
If you do that, use a String variable and then add the String to the DropDownList

check this

The table Cause of Loss has a Loss _Code, and a Loss_Description. I would need to populate the ddlist with both columns. I also didnt mention that this ddlist is part of a datagrid. Thank you very much guys for your help. Does this look OK?

Sub GetCauseofLoss()

Dim conn As SqlClient.SqlConnection
Dim cmd As SqlClient.SqlCommand
Dim myDS as DataSet
Dim currRecord as DataRow

Dim dr As SqlClient.SqlDataReader
Dim intField As Integer
conn = New SqlClient.SqlConnection
conn.ConnectionString = Replace(Application("ConnectStage"), "Driver={SQL Server}; ", "")
cmd = conn.CreateCommand
conn.Open()

cmd.CommandText = "SELECT * FROM CauseOfLoss"

dr = cmd.ExecuteReader
While dr.Read

for each currRecord in myDS.Tables(0).Rows
dgddLossCode.Items.Add(new ListItem(dr.Item(Loss_Code))
dgddLossCode.Items.Add(new ListItem(dr.Item(Loss_Description))
next

End While
dr.Close()
conn.Close()

End Sub

Each add call on the dropdownlist.item will add a new list item. What you need to do is similar, but more like
Dim strToAdd as String
While dr.Read
strToAdd = ""
for each currRecord in myDS.Tables(0).Rows
strToAdd &= dr.Item(Loss_Code)
strToAdd &= dr.Item(Loss_Description)
dgddLossCode.Items.Add(strToAdd)
next
End While
You may want to space them somehow, but I'm not sure the best method on that. As for the ddl being in a DataGrid, I'm not sure that would change this part of the code. It's still DropDownList, just encapsulated within a DataGrid.

vin1127 wrote:

Sub GetCauseofLoss()

Dim conn As SqlClient.SqlConnection
Dim cmd As SqlClient.SqlCommand
Dim myDS as DataSet
Dim currRecord as DataRow

Dim dr As SqlClient.SqlDataReader
Dim intField As Integer
conn = New SqlClient.SqlConnection
conn.ConnectionString = Replace(Application("ConnectStage"), "Driver={SQL Server}; ", "")
cmd = conn.CreateCommand
conn.Open()

cmd.CommandText = "SELECT * FROM CauseOfLoss"

dr = cmd.ExecuteReader
While dr.Read

for each currRecord in myDS.Tables(0).Rows
dgddLossCode.Items.Add(new ListItem(dr.Item(Loss_Code))
dgddLossCode.Items.Add(new ListItem(dr.Item(Loss_Description))
next

Hi, your DataSet was not filled and it is empty and uninstantiated, your foreach loop would not run. Try this
protected void dgrd_ItemDataBound(Object sender, DataGridItemEventArgs e)
{
// if in <EditItemTemplate>, use ListItemType.EditItem
if(e.Item.ItemType == ListItemType.Item ||
e.Item.ItemType == ListItemType.AlternatingItem)
{

// open connection here
// DataSet declaration

// retrieve the fields needed only
string strSQL = "SELECT Loss_Code, Loss_Description FROM CauseOfLoss";
SqlDataAdapter daCOL = new SqlDataAdapter(strSQL,conn);

daCOL.Fill(myDS,"COL");
DataColumn dcol = new DataColumn();
dcol.ColumnName = "CompositeCol";
dcol.ColumnType = System.Type.GetType("System.String");
dcol.Expression = "Loss_Code + ' - ' + Loss_Description";
myDS.Tables["COL"].Columns.Add(dcol);
// locate DDL in DataGrid
DropDownList ddl = (DropDownList)e.Item.FindControl("ddlIDInGrid");
ddl.DataSource = myDS.Tables["COL"];
ddl.DataTextField = "CompositeCol";
ddl.DataValueField = "Loss_Code";
ddl.DataBind();
}
}


Hope this helps...

vin1127 wrote:

The table Cause of Loss has a Loss _Code, and a Loss_Description. I would need to populate the ddlist with both columns. I also didnt mention that this ddlist is part of a datagrid. Thank you very much guys for your help. Does this look OK?

Sub GetCauseofLoss()

Dim conn As SqlClient.SqlConnection
Dim cmd As SqlClient.SqlCommand
Dim myDS as DataSet
Dim currRecord as DataRow

Dim dr As SqlClient.SqlDataReader
Dim intField As Integer
conn = New SqlClient.SqlConnection
conn.ConnectionString = Replace(Application("ConnectStage"), "Driver={SQL Server}; ", "")
cmd = conn.CreateCommand
conn.Open()

cmd.CommandText = "SELECT * FROM CauseOfLoss"

dr = cmd.ExecuteReader
While dr.Read

for each currRecord in myDS.Tables(0).Rows
dgddLossCode.Items.Add(new ListItem(dr.Item(Loss_Code))
dgddLossCode.Items.Add(new ListItem(dr.Item(Loss_Description))
next

End While
dr.Close()
conn.Close()

End Sub

Your code should be this.

Sub GetCauseofLoss()

Dim conn As SqlClient.SqlConnection
Dim cmd As SqlClient.SqlCommand
Dim myDS as DataSet
Dim currRecord as DataRow

Dim dr As SqlClient.SqlDataReader
Dim intField As Integer
conn = New SqlClient.SqlConnection
conn.ConnectionString = Replace(Application("ConnectStage"), "Driver={SQL Server}; ", "")
cmd = conn.CreateCommand
conn.Open()

cmd.CommandText = "SELECT * FROM CauseOfLoss"

dr = cmd.ExecuteReader
While dr.Read

for each currRecord in myDS.Tables(0).Rows
dgddLossCode.Items.Add(new ListItem(dr.Item("Loss_Code"),dr.Item("Loss_Description"))
next

End While
dr.Close()
conn.Close()

End Sub

for each currRecord in myDS.Tables(0).Rows
dgddLossCode.Items.Add(new ListItem(dr.Item("Loss_Code"),dr.Item("Loss_Description"))
next
The above will actually add a new ListItem to your DDL with a Text of the value in Loss_Code and a Value of "Loss_Description". If this is what you want, thent that is correct. If you want them to both be added, you'll need to make them 1 string, then add that string. On a note, if you only supply on parameter to ListItem, it is made both the Text and Value property.

Populating a html table

How to populate a html table using data from sql database..

The simplest answer is the use a gridview to display a dataset selected from the database.

Populating ddl using hashtables

I have a hash table declared in a class. how do i bind it to a
dropdownlist in a webform?The same way you bind any other source to a dropdownlist. Use "Key" and
"Value" as your dataTest/ValueField

ddl.DataSource = urHashtable;
ddl.DataTextField = "Value";
ddl.DataValueField = "Key";
ddl.DataBind();

--
MY ASP.Net tutorials
http://www.openmymind.net/
<shamila.thakur@.gmail.com> wrote in message
news:1129568158.495358.66850@.g14g2000cwa.googlegro ups.com...
>I have a hash table declared in a class. how do i bind it to a
> dropdownlist in a webform?