Getting data from Access into my Combobox

[andycharger]

I have a windows Form with a combo box on it.

 

I want to get the contents of my "makes" table in ny Access DB into my Combobox.

 

So far, I have added a combo box onto my form. I have then dragged the data connection onto the form from the SERVERS tab on the left.

I have then Generated a dataset from DATA/Generate Data Set on the menu at the top.

I have then gone into Combobox properties and set the following:

 

DataSource = DataSet11.Makes

DisplayMember = Vehicle_Make

ValueMember = ID

 

All looks ok until I preview it. I get no build errors.

 

Then when I access the form, the combobox is empty and when I click on the down arrow of the box, it does not drop or show me any entries and there are 20 or so entries in that table.

 

Any ideas?

 

Thanks

 

Andy

[Robby]

These samples may be in Sql Server however they can all be applied to Access...http://samples.gotdotnet.com/QuickStart/winforms/default.aspx?url=/quickstart/winforms/doc/WinFormsData.aspx

[andycharger]

Almost there

 

I have used some examples but im a little stuck here....

 

Here is my problem....

 

My combobox is now being filled. Thanks! The problem is, I want my selections to pass their ID to the next option. When im creating my list from the database, it is putting the ID in the actual combobox with a comma separating it from the text value and not assigning it to the value of the selection.

 

Here is my code. Any ideas?

 

       Dim strName As String
       Dim strOleDB As String ="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\carfinder.mdb"
       Dim cnOleDB As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\carfinder.mdb")
       
       Dim dr As OleDbDataReader
       Dim cmd As New OleDbCommand()
       With cmd
           .CommandText = "Select ID, vehicle_make from makes order by vehicle_make"
           .Connection = cnOleDB

       End With
       cnOleDB.Open()

       dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
       While dr.Read
           strName = dr("vehicle_make") & "," & dr("ID")
           Me.ComboBox1.Items.Add(strName)


       End While

       cnOleDB.Close()

[Robby]

Why the comma thing?

[andycharger]

Help!

 

Ignore the comma....

I just want to assign the id value to the text im putting in the combobox so when the person selects a value from the box, I can read the ID of the selection.

 

I.E Volvo = 20 so when they select Volvo in the combobox, I get the value 20.

So when im doing the ADD part of the combobox I guess I need to add the ID to the text value.

 

This is what I really want to understand

Ive tried not assigning it a ID and then just pulling out the Combobox1.SelectedIndex value but that just gives me the number where it appears in the list (so the 2nd one in the list has a selectedindex of 2 when its ID may be 34!)

 

Any ideas?

[Robby]

An easier way is to bind the data to the combo box, using the link I gave you above, you can assign DisplayMember (item shown to the user) and the ValueMember (item ID not shown to the user).

[andycharger]

Tried that!

 

I tried that Robbie but When I typed Public Structure in VS.NET it said Keyword Structure is not valid as an identifier.

[Robby]

Why do you need a structure?

[andycharger]

Help

 

Robby, I feel like im bashing my head against the wall with your solution.

You told me to go and look at databinding on your link. On the link you posted, the example for combobox binding mentions structures.

That is why I used that. I cannot see how I can resolve this.

 

Can you not post a typical ACCESS DB EXAMPLE combobox for me?

[andycharger]

Here is the code I have been trying to use now but this gives me an error.

it says:

An unhandled exception of type 'System.Exception' occurred in system.windows.forms.dll

 

Additional information: Complex DataBinding accepts as a data source either an IList or an IListSource

 

And it stops on the Me.ComboBox1.Datasource line

 

 

      Dim oConn As OleDbConnection
       Dim oComm As OleDbCommand
       Dim oReader As OleDbDataReader
       Dim sSQL As String
       Dim sConn As String

       sSQL = "Select ID, vehicle_make from makes order by vehicle_make"

       sConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\carfinder.mdb"

       oConn = New OleDbConnection(sConn)
       oConn.Open()

       oComm = New OleDbCommand(sSQL, oConn)
       oReader = oComm.ExecuteReader()

       Me.ComboBox1.DataSource = oReader
       Me.ComboBox1.DisplayMember = oReader("vehicle_make")
       Me.ComboBox1.ValueMember = oReader("ID")


       oConn.Close()

[andycharger]

I fixed it myself!!!

 

Here is my code!

 

       Dim strSQL As String = "Select ID, vehicle_make from makes order by vehicle_make"
       Dim Connection As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\carfinder.mdb")
       Dim DA As New OleDbDataAdapter(strSQL, Connection)
       Dim DS As New DataSet()

       DA.Fill(DS, "makes")



       Dim dt As New DataTable()
       dt.Columns.Add("vehicle_make", GetType(System.String))
       dt.Columns.Add("ID", GetType(System.String))
       '
       ' Populate the DataTable to bind to the Combobox.
       '
       Dim drDSRow As DataRow
       Dim drNewRow As DataRow

       For Each drDSRow In DS.Tables("makes").Rows()
           drNewRow = dt.NewRow()
           drNewRow("vehicle_make") = drDSRow("vehicle_make")
           drNewRow("ID") = drDSRow("ID")
           dt.Rows.Add(drNewRow)
       Next



       ComboBox1.DropDownStyle = ComboBoxStyle.DropDownList

       With ComboBox1
           .DataSource = dt
           .DisplayMember = "vehicle_make"
           .ValueMember = "ID"

       End With

[Robby]

Yeah they use a structure in the sample, but if you look at the Source Code they don't...http://samples.gotdotnet.com/quickstart/util/srcview.aspx?path=/quickstart/winforms/Samples/Data/ComboBoxBinding/ComboBoxBinding.src

 

Anyway here's a shorter version of your code...

 

Dim strSQL As String = "Select ID, vehicle_make from makes order by vehicle_make"

Dim Connection As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\carfinder.mdb")

Dim DA As New OleDbDataAdapter(strSQL, Connection)

Dim DS As New DataSet

DA.Fill(DS, "makes")

With ComboBox1

.DataSource = DS.Tables(0)

'or .DataSource = DS

'or .DataSource = DS.Tables(0).DefaultView

.DisplayMember = "vehicle_make"

.ValueMember = "ID"

End With

[andycharger]

Thanks Robby!

 

Im trying to get another combobox to populate after I have made a change to combobox1.

Im using the comboBox1_selectedIndexChanged event.

 

However, when my page loads up, it fires that even 3 or 4 times which is bad.

 

I saw somewhere you just delete the "Handles ComboBox1_selectedIndexChanged bit off the end of the event but that does not work now.

 

Any ideas?

[Robby]

That's what I used selectedIndexChanged...

I had a solution for not triggering it during form load but I can't think of it off-hand, it's been a while.