How to populate a combobox in vb.net with filtered data from an msaccess table

Question

I would really appreciate any help I can get.

My problem is that I have Combobox1 bound to a BindingSource and the DataMember and ValueMember property linked and working. For the life of me I do not know how to use the value (selected valuemember) of Combobox1 to filter the results I show on Combobox2. I am desperate for a simple way to do this.

my failing code is below

Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged
    Dim conn As New SqlConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\Database1.mdb") 'This line fails

    Dim strSQL As String = "SELECT * FROM Questions WHERE Section='" & ComboBox1.ValueMember & "'"
    Dim da As New SqlDataAdapter(strSQL, conn)
    Dim ds As New DataSet
    da.Fill(ds, "Disk")

    With ComboBox2 'Here i try to populate the combobox2
        .DataSource = ds.Tables("Questions")
        .DisplayMember = "Question_String"
        .ValueMember = "Question_Code"
        .SelectedIndex = 0
    End With
End Sub

I keep getting a system level error as follows {"Keyword not supported: 'provider'."}

I have tried a few other options but the errors I get seem more cryptic can someone please help me on this. I will appreciate it a lot.

score 1 · Answer 1 · answered May 21 '12 at 18:02

Dim conn As New OleDBConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source='|DataDirectory|\Database1.mdb'")

Also your query has to use a string not an object so try...

Dim strSQL As String = "SELECT * FROM Questions WHERE Section='" & ComboBox1.ValueMember.toString & "'"

score 0 · Answer 2 · answered May 10 '12 at 15:38

Because you are using a Provider for a connection that doesn't need it because the provider should be an SQL driver.

I don't know what database you are using (well, the file is an access file), but you need to check you have the correct connection string

see http://www.connectionstrings.com/sql-server-2008#p2

score 0 · Answer 3 · edited May 23 '17 at 12:04

Here are a couple of observations about your code that I hope you find helpful:

First, you might want to look at this MSDN help on how to move your database connection string out of the code and into a configuration file. This is especially important for your code to work to work more seemlessly across different environment (dev box, staging, production, etc) - Connection Strings and Configuration Files (ADO.NET)

I also noticed that you never explicitly open or close the connection. Per this entry on stack overflow, you should be ok, but keep in mind that if you happen to change the code to explicitly open the connection you will also need to close it.

I also noticed that you aren't using a parameterized query. This makes your code vulnerable to a SQL Injection attack. Here is a link to a blog posting by Scott Guthrie 'Tip/Trick: Guard Against SQL Injection Attacks'. You never know who may copy and paste your block of code with this bad practice.

Finally, you do the following query (with appropriate mods from other answers:

Dim strSQL As String = "SELECT * FROM Questions WHERE Section='" & ComboBox1.ValueMember.toString & "'"

And subsequently only use Question_String, and Question_Code in your code. You might want to consider changing your query to only pull the columns you need. This is especially helpful when you have tables with many columns. Otherwise you will needlessly pull data your code never actually needs. So your query would become:

   Dim strSQL As String = "SELECT Question_String, Question_Code FROM Questions WHERE Section='" & ComboBox1.ValueMember.toString & "'"

How to populate a combobox in vb.net with filtered data from an msaccess table

3 Answers3