Can I use an object property (such as the Text value from a comboBox) in an SQL query so that the query results will be dependent upon the user's choice of said comboBox?
I have a makeComboBox with 5 car manufacturers. When the user chooses one, it enables modelComboBox, which is data bound to a table from a database. The table is called Models, and consists of 30 random car models, with a text field called Make that specifies which of the 5 manufacturers it came from. I want to populate modelComboBox with models from the Models table, but only those whose Make is the same as the one chosen in makeComboBox. I added a query called FillByMake with this SQL SELECT statement using the TableAdapter Query Configuration Wizard:
SELECT Model FROM Models WHERE Make = makeComboBox.Text
I added these lines to the makeComboBox_SelectedItemChanged event code:
It compiles but before it loads the form it gives me an unhandled OleDbException: "No value given for one or more required parameters." Can anyone help me by telling what I'm doing wrong and how to do it correctly?
Double-click the COMBO box to create the required code with the properties
Forgive me, but when you say double-click the comboBox, do you mean the makeComboBox which the user chooses the make of the car, or the modelComboBox which will be popluated from the database? Either way, double-clicking creates ComboBox_SelectedIndexChanged() events which I already have for each. I guess the problem is what code to put inside of it.
You can use an object property, like .Text, in a query. One way to do this is by using a parameter object. The code below is a method tailored after your question, so you may be able to plug it right into your own code. Just pass the connection object and the make of the car as a string (from makeComboBox.Text). This code should be invoked from the selection item changed event of the makeComboBox.
The comments explain what the code does at each step.
private void LoadComboBox(OleDbConnection con, string makeOfCar)
// open connection - you will want to add a try/catch/finally block
// set the sql string – the ? will be filled in by the parameter
string sqlString = "SELECT Model FROM Models WHERE Make = ?";
// create the command object with the sql string and the passed OleDbConnection
OleDbCommand cmd = new OleDbCommand(sqlString, con);
// add a parameter for the make - will replace the ? in the sql string
cmd.Parameters.Add(new OleDbParameter("Make", makeOfCar));
// execute the query and convert the reader into a datatable
OleDbDataReader dr = cmd.ExecuteReader();
DataTable dt = new DataTable();
// bind the datatable to the Model combobox
modelComboBox.DataSource = dt;
modelComboBox.DisplayMember = "Model";
modelComboBox.ValueMember = "Model";
// close the connection