Dropdown controls

[Jon]

I am using a dropdown control and populating it from an SQL select, like
below

SQL = "SELECT RegionNumber, Description FROM SalesRegion WHERE DateClosed IS
NULL"
cmd.Connection = dbCon
cmd.CommandType = CommandType.Text
cmd.CommandText = SQL
drTemp = cmd.ExecuteReader(CommandBehavior.SequentialAccess)
While drTemp.Read
Me.ddCust.Items.Add(drTemp.GetString(1))
End While
If Not drTemp.IsClosed Then drTemp.Close()

I really want to do something like this (from VB6)

ddCust.AddItem "BOB"
ddCust.ItemData(0) = 15

I want the Description from the select to be the text in the dropdown, yet I
still need to know what the RegionNumber is. What would be the best
solution to this?

Thanks!

 

[Jon]

Of course, you always find it 2 seconds later...for anyone else wondering,
here it is:

SQL = "SELECT Description, RegionNumber FROM SalesRegion WHERE DateClosed IS
NULL"

cmd.Connection = dbCon

cmd.CommandType = CommandType.Text

cmd.CommandText = SQL

drTemp = cmd.ExecuteReader(CommandBehavior.SequentialAccess)

While drTemp.Read

Dim l = New ListItem(drTemp.GetString(0), drTemp.GetInt32(1).ToString)

Me.ddCust.Items.Add(l)

End While

If Not drTemp.IsClosed Then drTemp.Close()

 

[William F. Robertson, Jr.]

Just throwing some thoughts out to you.

You can set the DataTextField and DataValueField for your DropDown control
and bind the datasource to your control.

ddlCust.DataTextField = "Description"
ddlCust.DataValueField = "RegionNumber"

(You probably will want to set this on the .aspx side as html style
properties of the control.)

Then you can bind directly to it.

ddlCust.DataSource = cmd.ExecuteReader( CommandBehavior.SequentialAccess OR
CommandBehavior.CloseConnection )
ddlCust.DataBind()

So you code would then look like:

SQL = "SELECT Description, RegionNumber FROM SalesRegion WHERE DateClosed IS
NULL"
cmd.Connection = dbCon
cmd.CommandType = CommandType.Text
cmd.CommandText = SQL


ddlCust.DataSource = cmd.ExecuteReader( CommandBehavior.SequentialAccess OR
CommandBehavior.CloseConnection )
ddlCust.DataBind()
'and then you are done.

I am not sure if I did the bitwise correct. I am a c# guy, but you should
know what I am talking about. You should always use the
CommandBehavior.CloseConnection (unless you have a good reason not to).
When the DataReader is closed (by the DataBind method) it will close out the
connection as well.

HTH,

bill

Of course, you always find it 2 seconds later...for anyone else wondering,
here it is:

SQL = "SELECT Description, RegionNumber FROM SalesRegion WHERE DateClosed IS
NULL"

cmd.Connection = dbCon

cmd.CommandType = CommandType.Text

cmd.CommandText = SQL

drTemp = cmd.ExecuteReader(CommandBehavior.SequentialAccess)

While drTemp.Read

Dim l = New ListItem(drTemp.GetString(0), drTemp.GetInt32(1).ToString)

Me.ddCust.Items.Add(l)

End While

If Not drTemp.IsClosed Then drTemp.Close()

 

[Jon]

Thanks for the tips.

Just throwing some thoughts out to you.

You can set the DataTextField and DataValueField for your DropDown control
and bind the datasource to your control.

ddlCust.DataTextField = "Description"
ddlCust.DataValueField = "RegionNumber"

(You probably will want to set this on the .aspx side as html style
properties of the control.)

Then you can bind directly to it.

ddlCust.DataSource = cmd.ExecuteReader( CommandBehavior.SequentialAccess
OR
CommandBehavior.CloseConnection )
ddlCust.DataBind()

So you code would then look like:

SQL = "SELECT Description, RegionNumber FROM SalesRegion WHERE DateClosed
IS
NULL"
cmd.Connection = dbCon
cmd.CommandType = CommandType.Text
cmd.CommandText = SQL


ddlCust.DataSource = cmd.ExecuteReader( CommandBehavior.SequentialAccess
OR
CommandBehavior.CloseConnection )
ddlCust.DataBind()
'and then you are done.

I am not sure if I did the bitwise correct. I am a c# guy, but you should
know what I am talking about. You should always use the
CommandBehavior.CloseConnection (unless you have a good reason not to).
When the DataReader is closed (by the DataBind method) it will close out
the
connection as well.

HTH,

bill