Question Combo Box

[DoJa]

Greetings all,

First things first, i'm new here so Hi! I have dabbled in a few programming/scripting languages but generally considermyself to be a beginner.

I am trying to make a vb.net application that will form the front end of a mysql database.

I have three combo boxes which I wish to use to narrow down the choices in the adjacent combo box. For example combobox1 will list all the geographical locations in the database, and combo box 2 should then list all the venues that are in the chosen location. combo box 3 will narrow down the selection further.

My question is two fold:

Firstly here is the code I have managed to cobble together so far:

Imports MySql.Data.MySqlClient 'Import MySQL Connection Object
Public Class Form1
    Dim conn As MySqlConnection = New MySqlConnection()

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        '******* Connection String *****************

        conn.ConnectionString = "server=localhost;" _
          & "user id=root;" _
          & "password='';" _
          & "database=db"
        Try
            conn.Open()
            Label1.Text = "Connection Opened Successfully"
            conn.Close()
        Catch myerror As MySqlException
            Label1.Text = "Connection Failed:" & myerror.Message
        Finally
            conn.Dispose()
        End Try
        '*****************************************************************

        'Populate 1st combo box

        Dim strSQL As String = "SELECT DISTINCT Location FROM Academy ORDER BY Location ASC"
        Dim da As New MySqlDataAdapter(strSQL, conn)
        Dim ds As New DataSet
        da.Fill(ds, "Academy")

        With ComboBox1
            .DataSource = ds.Tables("Academy")
            .DisplayMember = "Location"
            .ValueMember = "Academy_Key"
            .SelectedIndex = 0
        End With

        'Populate 2nd combo box

        Dim strSQL2 As String = "SELECT DISTINCT Venue FROM Academy ORDER BY Venue ASC"
        Dim da2 As New MySqlDataAdapter(strSQL2, conn)
        Dim ds2 As New DataSet
        da2.Fill(ds2, "Venue")

        With ComboBox2
            .DataSource = ds2.Tables("Venue")
            .DisplayMember = "Venue"
            .ValueMember = "Venue_Key"
            .SelectedIndex = 0
        End With


    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click


        
    End Sub
End Class

The code above connects to the database and populates combo box 1, but when i duplicated the code and changed the variable names for combo box 2 it doesnt work. Where am I going wrong?


The second part of my question ... how do I go about using the selected value from combo box 1 as part of the select statement in combobox 2? I want to be able to add an 'AND' clause to my sql statement for combobox 2 which is equal to the selected value for combo box 1 . I hope this makes sense.


Finally if I am doing anything stupid, or simply just writing with bad habbits or inefficiently I would always welcome any tips/suggestions to improve. Even if it seems like overstating the ovbious I really am a beginner with all this so I wont be offended!

I'm still trying to get my head around all this dataadapter stuff and generally the concept of classes and I dont yet fully grasp what stuff i have to duplicate and what stuff I can reuse.


Thanks in Advance,


DJ

 

[jmcilhinney]

You should create a single DataSet with three DataTables and two DataRelations between them. You can then bind the data to the ComboBoxes and all the filtering will take care of itself. Check out this thread of mine that demonstrates how to do what you want with two tables. You just need to extend it with an extra table.

Master/Detail (Parent/Child) Data-binding

 

[DoJa]

Thanks for the link. I followed the instructions, copied the code and got the example working however I dont understand what i'd need to change to get it working with my database instead of the predefined values.

Out of interest why isnt the code to populate the second combo box working? It is almost an exact copy and vb displays no errors but it doesnt do anthing either.

 

[jmcilhinney]

Thanks for the link. I followed the instructions, copied the code and got the example working however I dont understand what i'd need to change to get it working with my database instead of the predefined values.

I suggest that you read the last paragraph of that post. Basically, my example shows you how to bind two DataTables with a parent/child relationship. How those DataTables get populated is completely irrelevant to the binding. You already know how to populate DataTables from a database so so you already know what to do to make my example work in your application.

Out of interest why isnt the code to populate the second combo box working? It is almost an exact copy and vb displays no errors but it doesnt do anthing either.

I don't know. It looks OK on the surface. Have you actually checked that youre query is returning records? What value does your Fill call return?

One other thing I would say, although I doubt that it's your issue, is that you are apparently setting the ValueMember to the name of a column that you aren't retrieving.

 

[DoJa]

The code itself does work because if i delete the code for combobox1 then the code for combobox 2 does what it's supposed to. However if I run the code for combobox1 first and then combobox 2 after nothing happens with combobox 2. Do I have to create a new connection every time I want to send an sql statement, could that be where i'm going wrong?

 

[jmcilhinney]

You need to debug the code. Code doesn't just "not work". First thing: does it actually run? If an exception is thrown earlier then the code won't even be running, which would explain why the expected results aren't seen.

I think I know what the issue is now, and it's already in my previous post. I said earlier that there was an anomoly in your code in that you were setting the ValueMember to the name of a column that didn't exist. That should be throwing an exception. I just noticed that your code is in the Load event handler, which means that any exceptions will be swallowed rather than crash the app. You are setting the ValueMember after the DataSource and DisplayMember, so the data-binding is appearing to work. The second query is never even executed though, because the first data-binding throws an exception. If you had read that in my previous post and fixed it then your issue would already have gone away.