How to use one combo box to filter results for another in C# with a local database

Question

I've spent a good bit of time searching the internet for an answer and can't come up with anything. Basically I have a very small database that consists of 5 tables total. My problem right now deals with just two of the them though. I have one table named Model (yes I know I did a bad job at naming this table. Going to try to rename it soon). Here is what Model looks like. Model table

Make ID refers to the unique ID in the table Makes. Here is what the table Make looks like. Make table

I have a windows form app that I created in C# using Visual Studios 2012. This database was created in that project. I have a form that has among other things, two combo boxes. The first one lists the info from the table Makes. It shows the 3 different car brands. The 2nd combo box shows the different models from the other table. I can get the first combo box to show all the Makes. I can get the 2nd combo box to show all the Models. But what I want is that if they select Ford in the first box, that it only shows the Fords in the 2nd box. When they select Ford in the first box, I need to somehow store the unique ID associated with Ford and then use it to filter the 2nd box by referencing the column Make ID in the Model table. I've done this in Access, but can't get it to work here. Here is my code I'm using to populate the first box.

 private void enterNewVehcileForm_Load(object sender, EventArgs e)
    {
        vinAutoPopulateTextBox.Text = mainMenu.VIN;

        mainMenu.connection.Open();

        SqlCeCommand cs = new SqlCeCommand("SELECT * FROM Makes", mainMenu.connection);

        SqlCeDataReader dr = cs.ExecuteReader();
        while (dr.Read())
        {
            vehicleMakeComboBox.Items.Add(dr["Car Brand"]);
        }

        dr.Close();
        dr.Dispose();



        mainMenu.connection.Close();
    }

This populates the first box just fine. I see Ford, Chevy, Dodge in the drop down box. But if I try this for the 2nd box, it doesn't work.

private void vehicleMakeComboBox_SelectedIndexChanged(object sender, EventArgs e)
    {

        int num = vehicleMakeComboBox.SelectedIndex;

        mainMenu.connection.Open();

        SqlCeCommand modelSearch = new SqlCeCommand("SELECT * FROM Model WHERE [Make ID] = @num", mainMenu.connection);

        SqlCeDataReader modelRead = modelSearch.ExecuteReader();
        while (modelRead.Read())
        {
            vehicleModelComboBox.Items.Add(modelRead["Model"]);
        }

        modelRead.Close();
        modelRead.Dispose();

        mainMenu.connection.Close();

I get an error at the line for while (modelRead.read()) It says a parameter is missing.

Can anyone help me or point me in the right direction. This is my first time messing with this, so it possible I did everything wrong.

You need to add a parameter as follows: modelSearch.Parameters.Add("num", SqlDbType.SmallInt).Value = num; — Kami, Jan 18 '16 at 13:13

score 2 · Answer 1 · answered Jan 18 '16 at 13:10

2

You aren't currently providing a value for the parameter @num in you SqlCeCommand. You can add a value for the parameter like this:

cmd.Parameters.AddWithValue("@num", num)

Here, you are saying that the parameter that you named @num in your SQL is going to have the value of the variable num.

answered Jan 18 '16 at 13:10

TomDoesCode

3,580
2
18
34

Ah, ok. I'll try that. Thank you. Is there a way I can actually give the variable num the value of the ID that goes with the Make they chose in the first drop box? For example, in the table Model that I posted above, the ID for Ford is 1, Chevy is 2, Dodge is 3. They way I have it now is storing the index of the selected car company, which isn't exactly what I want. I need to store the value that is in the ID column of the same row as the car brand they choose. This is how I am referencing it in the Model table. – B_Martin Jan 18 '16 at 16:44
Would I select the value of ID that is in the same index as the brand they selected and then pass that value to num? – B_Martin Jan 18 '16 at 16:50
@B_Martin I'm not quite sure that I understand, can you raise another question and put in some details and code? – TomDoesCode Jan 18 '16 at 16:50
Sorry, I now see part of my problem. I'm currently assigning the variable num with the index of the combo box. In my first table Model (link in original question), each car brand has a unique ID number in the column next to it. I need to show the car brand in the first drop box, but store the value of its ID. I then need to use that ID value to filter the 2nd combo box. In the table Make (see link in original question) there is a column called Make ID. This is the foreign key to the Make table. If they pick Ford in the first box, I need only the Fords to show up in the 2nd box. – B_Martin Jan 18 '16 at 17:21

score 2 · Answer 2 · answered Jan 18 '16 at 13:36

2

Before you fire up ExecuteReader add the following line to your code.

modelSearch.Parameters.Add("num", SqlDbType.SmallInt).Value = num;

answered Jan 18 '16 at 13:36

Kami

365
1
10

score 0 · Accepted Answer · answered Jan 19 '16 at 00:44

Alright, I got it all working. I realized earlier that my logic was all messed up and I might not have explained myself very well. I did get it working, so here it is for anyone else that might find this and have the same issue.

This part populates the first combo box.

private void enterNewVehcileForm_Load(object sender, EventArgs e)
    {
        vinAutoPopulateTextBox.Text = mainMenu.VIN;

        mainMenu.connection.Open();

        SqlCeCommand cs = new SqlCeCommand("SELECT * FROM Makes", mainMenu.connection);

        SqlCeDataReader dr = cs.ExecuteReader();
        while (dr.Read())
        {
            vehicleMakeComboBox.Items.Add(dr["Car Brand"]);
        }

        dr.Close();
        dr.Dispose();           

        mainMenu.connection.Close();
    }

This part then filters the 2nd box based on what they picked in the first box.

        private void vehicleMakeComboBox_SelectedIndexChanged(object sender, EventArgs e)
    {
        string ID = null;
        string command = "SELECT * FROM Makes WHERE [Car Brand] = '" + vehicleMakeComboBox.Text + "'";
        string command2 = null;

        mainMenu.connection.Open();

        SqlCeCommand makeSearch = new SqlCeCommand(command, mainMenu.connection);

//  This part gets the ID of the car brand they picked in the first combo box.  Ford is 1, Chevy is 2, Dodge is 3  
        SqlCeDataReader makeRead = makeSearch.ExecuteReader();
        while (makeRead.Read())
        {
            ID = (makeRead["ID"].ToString());
        }

        makeRead.Close();
        makeRead.Dispose();

        vehicleModelComboBox.Items.Clear(); // Clears the combo box incase they picked a brand and then picked another

//  This part now selects all rows in the Model table that have the same value in the Make ID column as the car brand they chose in the first combo box  
        command2 = "SELECT * FROM Model WHERE [Make ID] = " + ID;

        SqlCeCommand modelSearch = new SqlCeCommand(command2, mainMenu.connection);

        SqlCeDataReader modelRead = modelSearch.ExecuteReader();
        while (modelRead.Read())
        {
            vehicleModelComboBox.Items.Add(modelRead["Model"]);
        }

        modelRead.Close();
        modelSearch.Dispose();

        mainMenu.connection.Close();
    }

Thank you to @TomDoesCode and @Kami. You guys got me thinking in the right direction which made me see where my code was lacking.

How to use one combo box to filter results for another in C# with a local database

3 Answers3