(Long)Showing Multiple DB Columns in a DropDownList

[Schultz]

I posted a message about this a few weeks ago, and my question is how
to show values from Multiple DB Columns in a DropDownList (VB.NET 03
and Access 2k). The format works, however it repeats the same first
value for the entire contents. How can I fix this? Thanks!

My code is as follows:

Dim strTID As String
Dim cmdT As OleDbCommand
Dim dtrT As OleDbDataReader
Dim dbCampus
Dim tsFix As String

'GET STUDENT INFO
strTID = "Select * From GlobalUsersWeb"
cmdT = New OleDbCommand(strTID, conPI)
conPI.Open()
dtrT = cmdT.ExecuteReader()
While dtrT.Read()

Dim sln
Dim dfn
Dim sid

sln = dtrT.Item("SLName")
dfn = dtrT.Item("SFName")
sid = dtrT.Item("UserID")

tsFix = String.Format("{0}, {1} ({2})", sln, dfn, sid)
dropGS.DataSource = dtrT
dropGS.DataTextField = "UserID"
dropGS.DataTextFormatString = tsFix

dropGS.DataValueField = "UserID"
dropGS.DataBind()
End While
dtrT.Close()
conPI.Close()

Within a sub run at Page_Load

The ouput HTML is the following:

<select name="dropGU" id="dropGU" class="cmdButton">
<option value="1003">Doe, John (1002)</option>
<option value="1004">Doe, John (1002)</option>
<option value="1005">Doe, John (1002)</option>
<option value="1006">Doe, John (1002)</option>
<option value="1007">Doe, John (1002)</option>
<option value="1008">Doe, John (1002)</option>
<option value="1009">Doe, John (1002)</option>
<option value="1010">Doe, John (1002)</option>
<option value="1011">Doe, John (1002)</option>
<option value="1012">Doe, John (1002)</option>
<option value="1013">Doe, John (1002)</option>
<option value="1014">Doe, John (1002)</option>
<option value="1015">Doe, John (1002)</option>
<option value="1016">Doe, John (1002)</option>
<option value="1017">Doe, John (1002)</option>
<option value="1018">Doe, John (1002)</option>
<option value="1019">Doe, John (1002)</option>
<option value="1020">Doe, John (1002)</option>
<option value="1021">Doe, John (1002)</option>
<option value="1022">Doe, John (1002)</option>
<option value="1023">Doe, John (1002)</option>
<option value="1024">Doe, John (1002)</option>
<option value="1025">Doe, John (1002)</option>
<option value="3001">Doe, John (1002)</option>
<option value="3002">Doe, John (1002)</option>
<option value="3003">Doe, John (1002)</option>
<option value="3004">Doe, John (1002)</option>

</select>

[Bill Borg]

Schultz, you're telling it to use a format string that is a literal (there's
no {0} left because you've already replaced it, so the UserId on each row has
nowhere to go), and each row is going to keep using that same string.

A few ways to go, but probably easiest: create a formatted column in your
datareader (either on the query or manually), e.g. FullName, with your Id,
First, and Last in a string the way you want it. Then use that name in your
DataTextField and skip the DataTextFormatString.

Not quite as easy (but I think way cleaner and more flexible): on
ItemDataBound of the dropdown get the values from your data source and format
and stuff them in directly.

Bill

"Schultz" wrote:

I posted a message about this a few weeks ago, and my question is how
to show values from Multiple DB Columns in a DropDownList (VB.NET 03
and Access 2k). The format works, however it repeats the same first
value for the entire contents. How can I fix this? Thanks!

My code is as follows:

Dim strTID As String
Dim cmdT As OleDbCommand
Dim dtrT As OleDbDataReader
Dim dbCampus
Dim tsFix As String

'GET STUDENT INFO
strTID = "Select * From GlobalUsersWeb"
cmdT = New OleDbCommand(strTID, conPI)
conPI.Open()
dtrT = cmdT.ExecuteReader()
While dtrT.Read()

Dim sln
Dim dfn
Dim sid

sln = dtrT.Item("SLName")
dfn = dtrT.Item("SFName")
sid = dtrT.Item("UserID")

tsFix = String.Format("{0}, {1} ({2})", sln, dfn, sid)
dropGS.DataSource = dtrT
dropGS.DataTextField = "UserID"
dropGS.DataTextFormatString = tsFix

dropGS.DataValueField = "UserID"
dropGS.DataBind()
End While
dtrT.Close()
conPI.Close()

Within a sub run at Page_Load

The ouput HTML is the following:

<select name="dropGU" id="dropGU" class="cmdButton">
<option value="1003">Doe, John (1002)</option>
<option value="1004">Doe, John (1002)</option>
<option value="1005">Doe, John (1002)</option>
<option value="1006">Doe, John (1002)</option>
<option value="1007">Doe, John (1002)</option>
<option value="1008">Doe, John (1002)</option>
<option value="1009">Doe, John (1002)</option>
<option value="1010">Doe, John (1002)</option>
<option value="1011">Doe, John (1002)</option>
<option value="1012">Doe, John (1002)</option>
<option value="1013">Doe, John (1002)</option>
<option value="1014">Doe, John (1002)</option>
<option value="1015">Doe, John (1002)</option>
<option value="1016">Doe, John (1002)</option>
<option value="1017">Doe, John (1002)</option>
<option value="1018">Doe, John (1002)</option>
<option value="1019">Doe, John (1002)</option>
<option value="1020">Doe, John (1002)</option>
<option value="1021">Doe, John (1002)</option>
<option value="1022">Doe, John (1002)</option>
<option value="1023">Doe, John (1002)</option>
<option value="1024">Doe, John (1002)</option>
<option value="1025">Doe, John (1002)</option>
<option value="3001">Doe, John (1002)</option>
<option value="3002">Doe, John (1002)</option>
<option value="3003">Doe, John (1002)</option>
<option value="3004">Doe, John (1002)</option>

</select>