UPDATE SQL statement pulling in GDV value?

[sundog1]

Hi guys,

I've been cracking on with this project for a few days now and had lots of useful pointers to help along the way.

I've now hit a little snag where I think I have written the correct update Statement but It seems to error at the last point.

The Code is the following:

           if (radioButton1.Checked == true)
            {
                //Creates new versions of the Connection string and Data Set//
                con = new System.Data.OleDb.OleDbConnection();
                ds = new DataSet();

                //The actual connection to the database//
                con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = E:/Orders1.mdb";

                //Open the Connection to the Database//
                con.Open();

                //The SQL UPDATE String you need to pass into the Data Adapter to collect the information//
                string UpdateCashOrderSQL = "UPDATE cashOrders SET cashQTY = '" + txtBoxQTY.Text + "', cashDescription = '" + txtBoxDesc.Text + "', cashSupplier = '" + txtBoxSupplier.Text + "' , cashDate = '" + dateTimePicker1.Text + "', cashCost = '" + txtBoxCost.Text + "', cashSell = '" + txtBoxSell.Text + "' WHERE CashAccRef_FKID = '" + txtAccRef.Text + "' AND cashID = '" + dataGridView2.SelectedCells[0].Value.ToString() +"' ";

                //Creates a new version of the Data Adapater which passed the SQL and Connection information//
                da = new System.Data.OleDb.OleDbDataAdapter(UpdateCashOrderSQL, con);

                //Data Adapater (da) uses the Insert Command along with the SQL Querey & Connection Details//
                da.UpdateCommand = con.CreateCommand();
                da.UpdateCommand.CommandText = UpdateCashOrderSQL;
                da.UpdateCommand.ExecuteNonQuery();

                //Closes the connection//
                con.Dispose();

                //Shows End User the Account has been added//
                MessageBox.Show("Customer Account updated");
            }

The part I think is causing the trouble is...

AND cashID = '" + dataGridView2.SelectedCells[0].Value.ToString() +"' ";

even though the form loads and when debugging seems to select the correct information it still errors at the last hurdle stating...

Data type mismatch in criteria expression. (See Screenshot)

Look forward to some responses and thanks in advance.

Regards
Mark.

[sundog1]

Update...

I've attached a screenshot showing the Syntax seems to be correct?
(Used an OR instead of AND but made no differnce) Still get the same error.

Regards
Mark.

[ChrisHunter]

Is cashID an int value?

If cashID is an int do cashID = " + (int)dataGridView2.SelectedCells[0].Value + "";

In the long run the best thing to do is to creats a stored procedures on the database side, it will help prevent the SQL statements from being changed while you're editing you app.

[sundog1]

Hi Chris,

Thanks for your advice on this. Unfortunatley I still seem to get the same error. You are correct though the 'cashID' column is a 'Long Integer'(Its' Access DB).

Would it be Int64 or something along those lines?

regards
Mark.

[sundog1]

I've attached a screenshot to this reply to show the Layout of the table and the SQL that is created via the Code.

[ChrisHunter]

try Convet.ToInt64(dataGridView2.SelectedCells[0].Value) and see what happens.

Let me know how you get on.

[sundog1]

Hi Chris,

Tried That and no worky :(

Thanks for your support on this though... so close to finishing my first little project and just this hurdle to get over.

I have uploaded another screenshot in a previous post showing Column DT and the Query being helg in C# if that helps.

Regards
Mark

[ChrisHunter]

So where is it giving you the error and what does the error say?

I'm an Sql Serever developer so my knowlage of Access is limited I'm affraid but I'll still try to help if I can.

[sundog1]

Hi Chris.

I'm prettu sure its down to the CashID (Long Integer) field as when this part of the statement is revmoed then it works fine (bar populating all Order Lines with the Same Info)

One person on another forum mentioned the following:

*" I can already see the problem in your rendered SQL statement, and it's due to this problem. All your numbers are quoted, meaning the DB engine is treating them like a string when it's expecting an integer/decimal/long/whatever.

You can manually fix that by removing the single quotes from the numbers, but the best fix is to use parameters so you don't have to do that."*

How do I tell the SQL Query to not use quotes around the cashID value?

[sundog1]

Ok.. CRACKED IT!

It was down to the...

cashID = '" + (int)dataGridView2.SelectedCells[0].Value + "'"

I just has to remove ' ' and it worked fine.

It was down to the wrong data being passed through.

Also, I am aware I should be using Parameters within this type of code which I shall do in the future.

THanks for all your advice guys.

Mark.

[ChrisHunter]

I did mention that a few posts ago, if you put' ' around something it is taken as a string value, I've been caught out by that problem a few times in the past.

You're welcome, I'm glad you managed to solve the problem.