Binaryworld - Using multiple fields as value for the DropDownList control's items ... [ VB.net -> WinForms ]

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:

Click here to copy the following block

'(My Connection 'Cn' is already open)
Dim mysql As String

mysql = "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 = drPhase
CboPhase.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:

Click here to copy the following block

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 If
End Function