Using multiple fields as value for the DropDownList control’s items

Using multiple fields as value for the DropDownList control’s items

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 = 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, "")

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


Share the Post: