Problem with SQL(Oledb) code

mark webb

Hey,
Im doing a project working within visual studio 2008 using oledb
the following code should actually work but it seems to be breaking out just after creating the dataset and I can't seem to find any reason for this.
What its trying to do is sort one drop down box (cb_CliOrdID) based on what is selected within the other (cb_ClientName).

    private void cb_ClientName_SelectedIndexChanged(object sender, EventArgs e)
        {
            OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.JET.OLEDB.4.0;" + @";Data Source=D:\Computing\Computing Project\Backup\RosieBookingandInvoiceBackup\Server\RosieBIDB.mdb;");

            DataSet ds = new DataSet("Orders"); 
            OleDbDataAdapter dsa = new OleDbDataAdapter();
            
            dsa.SelectCommand.CommandText = @"SELECT Date_of_Order FROM tbl_Order WHERE Client_Name = '" + cb_ClientName.DisplayMember + "'";
            dsa.TableMappings.Add("tbl_Order", "Orders");
            dsa.Fill(ds);
            cb_CliOrdID.DisplayMember = ds.Tables[0].Columns[0].ToString() ;
            cb_CliOrdID.ValueMember = ds.Tables[0].Columns[0].ToString();
            cb_ClientName.Refresh();
            
        }

Would appriciate any possible help,

and thanks in advance.

Mark

7 Replies

Know the answer? Post it — somebody with the same question will find it here.

mark webb16y ago
Cheers for your help guys just figured out I need to use WHERE Client_Name = '" + cb_ClientName.Text +"'". That seems to make it work...

Thanks again!
mark webb16y ago
the using statement is definatly there as I am calling other items from the same db within the form so i know all the connection strings are correct etc
I always use the try/catch/finally when doing items are dependant on a db / statement etc but for refreshing a drop down menu I didnt think it was that necessary, was that wrong?
Just tried putting it in the contructor and still no luck...

cheers
Sam Hobbs16y ago
Amit's code is good, except you need to specify the connection in the constructor. So specify the SelectCommand and the connection in the constructor of OleDbDataAdapter.

Also, you should use try/catch blocks to catch errors during execution. The error caused by a missing Using is not an execution error, but when you do get an execution error, try/catch blocks allow your program to issue a meaningful error message and then exit gracefully.
mark webb16y ago
Hey again,
Sorry to say that it doesnt seem to be working, the line of dsa.Connection= con; seemed to cause the following error.
" 'System.Data.OleDb.OleDbDataAdapter' does not contain a definition for 'Connection' and no extension method 'Connection' accepting a first argument of type 'System.Data.OleDb.OleDbDataAdapter' could be found (are you missing a using directive or an assembly reference?) "

I commented out that line and followed it through using breakpoint again and it got to the Select Command statement before breaking out completly.
I did try changing what you gave me after that to dsa.SelectCommand.Connection = con; but that didnt seem to have any effect either....

cheers

Amit Choudhary16y ago

hi friend,

Try following code i have modified it....

    private void cb_ClientName_SelectedIndexChanged(object sender, EventArgs e)
        {
            OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.JET.OLEDB.4.0;" + @";Data Source=D:\Computing\Computing Project\Backup\RosieBookingandInvoiceBackup\Server\RosieBIDB.mdb;");
            con.Open();
            DataSet ds = new DataSet("Orders");                 OleDbDataAdapter dsa = new OleDbDataAdapter();
            dsa.SelectCommand.CommandText = @"SELECT Date_of_Order FROM tbl_Order WHERE Client_Name = '" + cb_ClientName.DisplayMember + "'";
            dsa.TableMappings.Add("tbl_Order", "Orders");
            dsa.Connection=con;
            dsa.Fill(ds);
            cb_CliOrdID.DisplayMember = ds.Tables[0].Columns[0].ToString() ;
            cb_CliOrdID.ValueMember = ds.Tables[0].Columns[0].ToString();
            cb_ClientName.Refresh();
            
        }

Mark as answer if it helps you.

mark webb16y ago
Its not coming up with an error, its just not going through the entire set of code. When I go through and check it with a breakpoint and go through each line of code it only gets up to the DataSet ds = new DataSet("Orders"); before jumping out of the function entirely cheers
Amit Choudhary16y ago
hi friend,

Please post the error with stack trace... i can help you. Cause your code looks ok....