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 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

Share the Post:
Share on facebook
Share on twitter
Share on linkedin

Overview

The Latest

iOS app development

The Future of iOS App Development: Trends to Watch

When it launched in 2008, the Apple App Store only had 500 apps available. By the first quarter of 2022, the store had about 2.18 million iOS-exclusive apps. Average monthly app releases for the platform reached 34,000 in the first half of 2022, indicating rapid growth in iOS app development.

microsoft careers

Top Careers at Microsoft

Microsoft has gained its position as one of the top companies in the world, and Microsoft careers are flourishing. This multinational company is efficiently developing popular software and computers with other consumer electronics. It is a dream come true for so many people to acquire a high paid, high-prestige job

your company's audio

4 Areas of Your Company Where Your Audio Really Matters

Your company probably relies on audio more than you realize. Whether you’re creating a spoken text message to a colleague or giving a speech, you want your audio to shine. Otherwise, you could cause avoidable friction points and potentially hurt your brand reputation. For example, let’s say you create a