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

Thread: Using an object property in an SQL query

  1. #1
    Join Date
    Dec 2007
    Posts
    13

    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?

  2. #2
    Join Date
    Jan 2006
    Location
    Chicago, IL
    Posts
    14,961

    Re: Using an object property in an SQL query

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

    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
    Posts
    13

    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
    Posts
    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
  •  


Azure Activities Information Page

Windows Mobile Development Center


Click Here to Expand Forum to Full Width

This is a CodeGuru survey question.


Featured


HTML5 Development Center