Using an object property in an SQL query
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums
Results 1 to 4 of 4

Thread: Using an object property in an SQL query

Hybrid View

  1. #1
    Join Date
    Dec 2007

    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:

    SELECT Model FROM Models WHERE Make = makeComboBox.Text
    I added these lines to the makeComboBox_SelectedItemChanged event code:

    modelComboBox.Enabled = true;
    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?

  2. #2
    Join Date
    Jan 2006
    Fox Lake, IL

    Re: Using an object property in an SQL query

    Double-click the COMBO box to create the required code with the properties

    CodeGuru Article: Bound Controls are Evil-VB6
    2013 Samples: MS CODE Samples

    CodeGuru Reviewer
    2006 Dell CSP
    2006, 2007 & 2008 MVP Visual Basic
    If your question has been answered satisfactorily, and it has been helpful, then, please, Rate this Post!

  3. #3
    Join Date
    Dec 2007

    Re: Using an object property in an SQL query

    Quote Originally Posted by dglienna View Post
    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.

  4. #4
    Join Date
    May 2011

    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.

            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

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

Windows Mobile Development Center

Click Here to Expand Forum to Full Width

This is a survey!

HTML5 Development Center