-
May 4th, 2011, 03:00 PM
#1
Using an object property in an SQL query
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:
Code:
SELECT Model FROM Models WHERE Make = makeComboBox.Text
I added these lines to the makeComboBox_SelectedItemChanged event code:
Code:
modelComboBox.Enabled = true;
models.TableAdapter.FillByMake(carDataSet.Models);
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?
-
May 4th, 2011, 03:04 PM
#2
Re: Using an object property in an SQL query
Double-click the COMBO box to create the required code with the properties
-
May 4th, 2011, 04:15 PM
#3
Re: Using an object property in an SQL query
Originally Posted by dglienna
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.
-
May 5th, 2011, 09:52 AM
#4
Re: Using an object property in an SQL query
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.
Code:
private void LoadComboBox(OleDbConnection con, string makeOfCar)
{
// open connection - you will want to add a try/catch/finally block
con.Open();
// 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();
dt.Load(dr);
// bind the datatable to the Model combobox
modelComboBox.DataSource = dt;
modelComboBox.DisplayMember = "Model";
modelComboBox.ValueMember = "Model";
// close the connection
con.Close();
}
}
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|