Data from database to VB Drop Down list / list box

I have a form load, I want to have a list box in this form that pulls data from my database table.
I know how to connect to Database and pull the data in a recordset but I dont know how to put those data in my List box.

 sSQL = "Select ProjectId, ProjectDescription from projects"
 Set rs = Conn.Execute(sSQL)
       
Whats next?
AutomaticSlimAsked:
Who is Participating?
 
Carl TawnSystems and Integration DeveloperCommented:
You need to set the ItemData for the corresponding item:

    While Not rs.EOF
        listBox.AddItem rs("ProjectID")
        listBox.ItemData(listBox.ListCount - 1) = rs("ProjectID")
        rs.MoveNext
    Wend


You would then retrieve the ID for the selected item like:

    ID = listBox.ItemData(listBox.ListIndex)
0
 
Carl TawnSystems and Integration DeveloperCommented:
You have to manually loop through the dataset and populate the listbox.

Something like:

    While Not rs.EOF
        listBox.AddItem rs("ProjectID")
        rs.MoveNext
    Wend


Hope this helps.
0
 
AutomaticSlimAuthor Commented:
That worked, but do you know how to show the 'ProjectDescription' in the list but when we select and pass the value to send  the 'ProjectId'.

Just like in HTML when we have drop down menu... can we do that here?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
AutomaticSlimAuthor Commented:
I was playing around and I think what I need is a ComboBox.
Does combobox work the same way?


        Combo1.AddItem rs("ProjectID")
        Combo1.ItemData(listBox.ListCount - 1) = rs("ProjectID")
        rs.MoveNext
0
 
AutomaticSlimAuthor Commented:
Ok, That did with the combo box. The only problem was that I got an error on this line:
Combo1.ItemData(listBox.ListCount - 1) = rs("ProjectID")

and that was because my 'ProjectID' its not numeric. Temperary I changed it to Int. But is there any way to make it work with AlphaNumeric?
0
 
Carl TawnSystems and Integration DeveloperCommented:
Unfortunatley not, it actually works with long under the hood but can do the implicit cast to int.

What you would have to do is maintain an array withyour project ID's in an sync it with the index positions of the combo.

So item 1 in the combo would map to item 1 in the array.
0
 
g_johnsonCommented:
you can also add a "padded" item to a list box such that what you want visible stays visible and any "key" you want to maintain is off the screen, but available, e.g.,

lst1.additem strVisible & space(100) & strKey

if you format such that your strVisible is always X characters and strKey is always Y characters, then your values are:

main value    left(lst1.list(i),X)
key value      right(lst1.list(i),Y)

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.