UPDATE SQL Statement Help!

[sundog1]

Hi Guys,

The reason for my message was to do with UPDATE and DELETE Statements from within C# when contacting a MDB file.

I currently have the INSERT INTO SQL Query working perfectly

The Code I used for this is below:

   else if (radioButton2.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 INSERT INTO String you need to pass into the Data Adapter to collect the information//
                string AddCustomerSQL = "INSERT INTO PrintedCustomers(PrintedAccRef, pCashName, pCashAddress1, pCashAddress2, pCashTown, pCashAddress3,pCashAddress4 ) " +
        " VALUES('" + txtAccRef.Text + "', '" + txtAccName.Text + "','" + txtAccAddr1.Text + "' ,'" + txtAccAddr2.Text + "' , '" + txtAccTown.Text + "' , '" + txtAccCounty.Text + "' , '" + txtAccPostCode.Text + "')";
                //Creates a new version of the Data Adapater which passed the SQL and Connection information//
                da = new System.Data.OleDb.OleDbDataAdapter(AddCustomerSQL, con);
                //Data Adapater (da) uses the Insert Command along with the SQL Querey & Connection Details//
                da.InsertCommand = new OleDbCommand(AddCustomerSQL, con);
                da.InsertCommand.ExecuteNonQuery();
                //Closes the connection//
                con.Dispose();
                //Shows End User the Account has been added//
                MessageBox.Show("Printed Account Added");

BUT... now its come to the UPDATE and DELETE Query i'm not too sure on how to handle it. Currently I have the following for my UPDATE Code:

  private void btnEditCashAcc_Click(object sender, EventArgs e)
    {
        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 INSERT INTO String you need to pass into the Data Adapter to collect the information//
            string UpdateCustomerSQL = "UPDATE cashCustomers (CashAccRef, CashName, CashAddress1, CashAddress2, CashTown, CashAddress3,CashAddress4 ) " +
        " VALUES('" + txtAccRef.Text + "', '" + txtAccName.Text + "','" + txtAccAddr1.Text + "' ,'" + txtAccAddr2.Text + "' , '" + txtAccTown.Text + "' , '" + txtAccCounty.Text + "' , '" + txtAccPostCode.Text + "')";
            //Creates a new version of the Data Adapater which passed the SQL and Connection information//
            da = new System.Data.OleDb.OleDbDataAdapter(UpdateCustomerSQL, con);
            //Data Adapater (da) uses the Insert Command along with the SQL Querey & Connection Details//
            da.UpdateCommand = con.CreateCommand();
            da.UpdateCommand.CommandText = UpdateCustomerSQL;
            da.UpdateCommand.ExecuteNonQuery();
            //Closes the connection//
            con.Dispose();
            //Shows End User the Account has been added//
            MessageBox.Show("Customer Account updated");
        }
    }

When Debugging it seems to follow through with the correct details within the Locals window but error's saying my SYNTAX is incorrect in the UPDATE Statment (Syntax error in UPDATE statement.).

I hope you can help me out once again as I would love to get this project finished and under my belt.

I look forward to hearing from you

Regards
Mark.

[ChrisHunter]

put the word set after the table name see if that works.

[sundog1]

Hi Chris,

Unfortunatley I thought this was the problem as well but then I get anothe error message which states...

"Syntax error in UPDATE statement" - Which I'm guessing there is a problem with my Querey somewhere. But in the Locals window it seems correct..

hmmm :\

[ChrisHunter]

Oh sorry, it looks like you've got your INSERT statements and UPDATE mixed together, for an INSERT you do something like:

INSERT INTO myTable (Name, Adddress, DOB) VALUES ('" + nameTextBox1.Text + "', '" + addressTextBox.Text + "', '" + dateOfBirthTextBox.Text + "')

But for an UPDATE you do something like:

UPDATE myTable Set Name = '" + nameTextBox1.Text + "', Address = '" + addressTextBox.Text + "', DOB = '" + dateOfBirthTextBox.Text + "'";

You need a where clause too if you want to update a specific record and not all of them.

Hope this helps.

[sundog1]

Hi Chris,

thanks for your reply. I'm struggling a little bit on where to / how to lay out the "Where" Clause in my SQL.

Any Pointers?

regards
Mark.

[ChrisHunter]

Same as the SELECT statement, the WHERE clause should be done on the unique identifier column (primary key/ ID column)

You should know the ID of the record as it should be returned as part of the SELECT statement something like: WHERE ID = myID.

[sundog1]

Hi Chris,

Many thanks for your Help. All sorted now.

[ChrisHunter]

Not a problem, glad I could help.