CODEWITHSUNDEEP
c#comboboxoledb
ArtR45
ArtR45

Reputation: 39

Chain query results to multiple combo boxes

I have several dependent combo boxes (CB) that pull data from different tables. It can be the selected value or a lookup value. For example I will display text (Name) in the box, when selected, I need to lookup the corresponding int (ID) and use that value in the query to populate the next combo box.

I'm having trouble referencing the command/query results and getting a data type error. The error is:

SQL passthru expression ... using equals (=) has components that are of different data types.

Note: My oledb interface doesn't support ICommandWithParameters

// Get the ID (int) value - This works as I can convert the return value to a string and echo back to the screen
OleDbCommand tblRow2 = new OleDbCommand("select ID from Table1 where NM=  '"+ CB1.Text +"' ;" , conn3);
try
{
    conn3.Open();
    string r2 = Convert.ToString(tblRow2.ExecuteScalar());
    MessageBox.Show(r2);
    labelTableID.Text = "ID Code= " + r2;
    conn3.Close();
}
catch (Exception ex2)
{
    MessageBox.Show("Error " + ex2);
}

// Pass the ID (int) value and populate the next combo box CB2
// This doesn't work and throws the data type mismatch error
OleDbCommand tblRow3 = new OleDbCommand("select STATUS from Table2 where TABLE_ID = '"+ tblRow2 +"'; ",conn3);
OleDbDataReader rdRow3;
try
{
    conn3.Open();
    rdRow3 = tblRow3.ExecuteReader();
    while (rdRow3.Read())
    {
        CB2.Items.Add(rdRow3.GetString(0));
    }
    conn3.Close();
}

Upvotes: 1

Views: 80

Answers (3)

ArtR45
ArtR45

Reputation: 39

I found the problem and it was literally nothing, i.e. blank spaces.  Bad: ‘ “ + var + “ ‘ Good: ‘”+var+”’ All queries are now working properly.

Upvotes: 0

ArtR45
ArtR45

Reputation: 39

I've answered most of my questions and I'm left with a logic error, i.e. my sql query doesn't return any results when it should. Again I'm pretty new to C# so I may not have the correct nomenclature, but this is my understanding of the problem/solution. The OleDbCommand returns an 'object', a query can return many rows & columns, but I need a singular value for comparison in the subsequent queries ... consequently ExecuteScalar() does the trick. Once I had a good value, I just needed to cast it in a variable (string or int) and embed properly in the next query. So everything is 'working' (no errors), I've verified the results of each query, so I know they're correct ... but I'm not getting any results from the last command/query( tblRow3a). Any Ideas? Thanks @

                        MessageBox.Show(CB1.Text);

        string conn3str = <connection string>;
        OleDbConnection conn3 = new OleDbConnection(conn3str);

        // Get the ID (int) value
        OleDbCommand tblRow2 = new OleDbCommand();
        tblRow2.Connection = conn3;
        tblRow2.CommandText = "select ID from Table1 where NM=  ' " + CB1.Text +" ' ;" ;

        conn3.Open();

        // Convert & Pass the command result (tblRow2) to string (info only) & int (for next query) 

        string r2 = Convert.ToString(tblRow2.ExecuteScalar());
        Label_1.Text = "ID Code= " + r2;

        int r2i = Convert.ToInt32(tblRow2.ExecuteScalar());

        // Select STATUS (string) where TABLE_ID (int) = ID (int)
        OleDbCommand tblRow3 = new OleDbCommand();
        tblRow3.Connection = conn3;
        tblRow3.CommandText = "select STATUS from Table2 where TABLE_ID = " + r2i;

        // Convert and Pass command result (tblRow3) to string for next query
        // Where CODE (string) = r3 (string) 
        string r3 = Convert.ToString(tblRow3.ExecuteScalar());
        Label_2.Text = "Current Status = " + r3;

        OleDbCommand tblRow3a = new OleDbCommand();
        tblRow3a.Connection = conn3;
        tblRow3a.CommandText = "select ORDER from Table3 where CODE =  ' " + r3 + " ' ;" ;

        string r3a = Convert.ToString(tblRow3a.ExecuteScalar());
        lblInvCd.Text = "Order = " + r3a;
        MessageBox.Show(r3a);
        // Result is empty even through this same query returns results in the database

Upvotes: 0

Grant Winney
Grant Winney

Reputation: 66449

You're surrounding your number in apostrophes, so it's being treated like a string literal.

Remove the apostrophes.

var tblRow3 =
    new OleDbCommand("select STATUS from Table2 where TABLE_ID = " + tblRow2 + ";", conn3);

Better yet, parameterize your query so you don't run into mistakes like this.

This is untested, but something like this should work. You can specify the name of the parameter too, but I don't think the name matters as much as the order, with OLEDB.

using (var cmd = new OleDbCommand("select STATUS from Table2 where TABLE_ID = ?", conn3))
{
  cmd.Parameters.Add(new OleDbParameter {OleDbType = OleDbType.Integer, Value = tblRow2});
}

Upvotes: 1

Related Questions