Login | Register   
RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX

Tip of the Day
Language: VB7
Expertise: Intermediate
Nov 25, 2002



Full Text Search: The Key to Better Natural Language Queries for NoSQL in Node.js

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

CboPhase.DataSource = drPhase
CboPhase.DataTextField = "phaseid"
CboPhase.DataValueField = "KeyVal"
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 If
End Function
Audrey Peters
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date