Being an avid Access Programmer I have been frustrated with the limitation of VB DropDownLists to using only 1 field as the value. I found a way to simulate/work around this behavior with concatenation of several database fields at the query level to be my columns(i.e. Select KeyId1 + ‘~’ + KeyId2 + ‘~’ + KeyId3 as Keyval from…)
I use the combined keyval as the DropDown.DataValueField and then call my parse function after the user selects their choice on the DropDownList.SelectedItem.Value. I can now retrieve multiple column data from the DropDownList as I used to in Access without having to run an additional query. This may help other users that are frustrated with the limitations of the .NET combo/dropdownlist controls. In this particular application I have a series of nested dropdowns that depend on the selection of the previous dropdown.
This code is in the SelectedIndexChange event of the DropDownList1:
'(My Connection 'Cn' is already open)Dim mysql As Stringmysql = "select phaseid, description, PhaseID + '~' + RateHdrID + '~' + " _ & "BillStatus AS KeyVal from tblphase where tblphase.clientid = '" & _ Me.ClientCode.Text & "' and tblphase.jobid = '" & _ Me.DropDownList1.SelectedItem.Value & "' and tblphase.active = 1"Dim cmd2 As New SqlCommand(mysql, Cn)Dim drPhase As SqlDataReader = cmd2.ExecuteReader _ (CommandBehavior.CloseConnection)CboPhase.DataSource = drPhaseCboPhase.DataTextField = "phaseid"CboPhase.DataValueField = "KeyVal"CboPhase.DataBind()CboPhase.Items.Insert(0, "<---Select Phase-->")
After the postback on the CboPhase, in the SelectedIndexChange event I use this code:
If Me.CboPhase.SelectedIndex <> 0 Then Me.RateHdrID.Text = ParseVal(Me.CboPhase.SelectedItem.Value, 2) Me.BillStatus.Text = ParseVal(Me.CboPhase.SelectedItem.Value, 3)End If
Where the ParseVal function returns the n-th piece of the value string:
Public Function ParseVal(ByVal MyString, ByVal KeyPos) As String ParseVal = "" If MyString <> "" Then Dim PosStart Dim PosEnd PosStart = InStr(1, MyString, "~") If PosStart > 0 Then Select Case KeyPos Case 1 ParseVal = Left(MyString, PosStart - 1) Case 2 PosEnd = InStr(PosStart + 1, MyString, "~") ParseVal = Mid(MyString, PosStart + 1, _ PosEnd - PosStart - 1) Case 3 PosStart = InStr(PosStart + 1, MyString, "~") ParseVal = Mid(MyString, PosStart + 1) End Select End If End IfEnd Function