1

This should not be that difficult, but apparently this old dog doesn't understand (or can't figure out). I have a combobox that has a list of states (cb_States). The data for the states is being read from an Access database. I have no problem populating the cb. In fact, I use the method below and have even tried using a dictionary. Both worked. This is not my issue.

cmd.Connection = conn
        cmd.CommandText = "SELECT * FROM States WHERE States.ActiveEntry = true ORDER BY States.State"

        conn.Open()

        Using rdr As OleDbDataReader = cmd.ExecuteReader()
            While rdr.Read()
                cb_State.Items.Add(rdr("State"))
            End While
        End Using

        cb_State.SelectedIndex = -1

        conn.Close()

The problem is when I do a subsequent lookup from a different table and I get an ID value for state (based of the table):

StateID AutoNumber StateAbbr ShortText State ShortText

And in my customers table ...

CustomerID AutoNumber ... StateID Number

So when I load the customer data, I want the state to be selected based on the ID. I know in HTML I can set the value to the StateID, and if I remember correctly back in VB 6.0 you could add a number to the displayed text that is hidden and search on that.

Now how do I do that today? It seems that I would have to take my StateID and do a lookup on the State table to get the Name of the State and then use FindString, which IMHO is a very screwy way of doing things.

I tried to create a Dictionary and I could add the Key and Value items as advertised on here (Binding combobox using dictionary as the datasource (Binding Combobox Using Dictionary as the Datasource) which worked the same as above, but for the life of me I could not figure out how to search and set the right state when being populated.

I know I can't be the only one with this issue and I'm sure it's a simple solution, but it evades me.

Gary L Smith
  • 9
  • 5
  • 1
    Create a new `DataTable` object and pass the reader `rdr` to its `Load` method to fill it with the values from the database. Or use `OleDbDataAdapter` to do so. Bind the combo box: `cb_State.DataSource = dt`, set `cb_State.DisplayMember = "State"`, and `cb_State.ValueMember = "StateID"` and you are done. Now you can get the selected value (StateID) by converting the `cb_State.SelectedValue` to integer. – dr.null May 20 '23 at 22:15
  • Example https://stackoverflow.com/a/54240192/14171304 – dr.null May 20 '23 at 22:20
  • 1
    Perfect! The example is a bit dated but nevertheless, using the example's method of adding the data to the combobox and then applying what you said about the selected value worked. I figured there had to be a simple way of doing this. As I had said before, it's been over 20 years since I programmed in VB--a long way from VB for DOS. – Gary L Smith May 21 '23 at 00:31

0 Answers0