Updating a Database Record - Syntax Error

[jamesw2207]

Hi,

I'm getting "Syntax Error in UPDATE statement" when trying to update a record. I'm presuming my UPDATE statement is wrong but am not sure how to go about it as I presume it's a little different from simply adding records. If anyone knows how I should be creating the UPDATE statement I'd be grateful for the help.

        private void button1_Click(object sender, EventArgs e)
        {
            int sa;
            string compID = null;
            string addone = null;
            string addtwo = null;
            string town = null;
            string county = null;
            string pcode = null;
            string telno = null;
            OleDbConnection myConnection = new OleDbConnection(connectionString);
            try
            {
                    compID = textBox1.Text;
                    addone = textBox3.Text;
                    addtwo = textBox4.Text;
                    town = textBox5.Text;
                    county = textBox6.Text;
                    pcode = textBox7.Text;
                    telno = textBox8.Text;

                    OleDbCommand myCommand = new OleDbCommand("UPDATE Company SET (CompanyAdd = '" + addone + "', CompanyAdd2 = '" + addtwo + "', CompanyTown = '" + town + "', CompanyCounty = '" + county + "', CompanyPCode = '" + pcode + "', CompanyTel = '" + telno + "') WHERE CompanyID = '" + compID + "')", myConnection);
                    OleDbDataAdapter myDataAdapter = new OleDbDataAdapter(myCommand);
                    myConnection.Open();
                    sa = myCommand.ExecuteNonQuery();
                    myConnection.Close();
                    MessageBox.Show("Company Updated", "Company details updated", MessageBoxButtons.OK, MessageBoxIcon.Information);
                
            }
            catch (Exception ex)
            {
                MessageBox.Show("Data Load Error: " + ex.Message.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                myConnection.Close();
            }
        }

[TheCPUWizard]

Use the debugger to look at the CommandText property. Probably a mismatch on your quotes.

More importantly, do NOT use string concatenation to build your update. With the code you have, I could easily wipe your entire database just by passing in the right values via the text boxes (Lookup "SQL Injection Attack"). ALWAYS use proper SqlParameter objects to build your commands.

[jamesw2207]

Thanks, I hadn't heard of SQL Injection Attacks before, was interesting reading. Researching online I think I've managed to change my code to using SQLParameter Objects but I'm just getting one runtime error that I can't get rid of.
I'm getting an ArgumentException was unhandled error. "An OLE DB Provider was not specified in the ConnectionString. An example would be, 'Provider=SQLOLEDB;'." I presumed that the form would inherit the connectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source =" + "C:\...etc."; from my main form but it hasn't.

        private void button1_Click(object sender, EventArgs e)
        {
            string strEditCommand = "UPDATE Company SET ";
            strEditCommand += "CompanyAdd = ?, CompanyAdd2 = ?, CompanyTown = ?,";
            strEditCommand += "CompanyCounty = ?, CompanyPCode = ?, CompanyTel = ?";
            strEditCommand += "WHERE CompanyID = ?";
            using (OleDbConnection myConnection = new OleDbConnection(strEditCommand))
            {
                using (OleDbCommand myCommand = new OleDbCommand(strEditCommand, myConnection))
                {
                    myCommand.Parameters.Add(new OleDbParameter("CompanyAdd", this.textBox3.Text));
                    myCommand.Parameters.Add(new OleDbParameter("CompanyAdd2", this.textBox4.Text));
                    myCommand.Parameters.Add(new OleDbParameter("CompanyTown", this.textBox5.Text));
                    myCommand.Parameters.Add(new OleDbParameter("CompanyCounty", this.textBox6.Text));
                    myCommand.Parameters.Add(new OleDbParameter("CompanyPCode", this.textBox7.Text));
                    myCommand.Parameters.Add(new OleDbParameter("CompanyTel", this.textBox8.Text));
                    myCommand.Parameters.Add(new OleDbParameter("CompanyID", this.textBox1.Text));
                    myCommand.Connection.Open();
                    myCommand.ExecuteNonQuery();
                }
            }
        }

[TheCPUWizard]

Just a silly typo in your re-write....

In your original, you were properly initializing the connection object with connectionString, however in your re-write, you are initializing the conntection with strEditCommand

[jamesw2207]

Hmmm...I've changed the strEditcommands but am still getting the same error. Have I possibly not declared something higher up?

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.Drawing;
using System.Text;
using System.Windows.Forms;

namespace STLSystem
{
    public partial class UpdateCompany : Form
    {
        private DataSet myDataSet;
        private string connectionString;
        private DataViewManager dviewmanager;

        public UpdateCompany(DataSet myDataSet, string connectionString)
        {
            InitializeComponent();
            this.myDataSet = myDataSet;
            this.connectionString = connectionString;
            myDataSet = new DataSet();
            this.BindComboBox(myDataSet, connectionString);
            dviewmanager = new DataViewManager();
        }

        private void BindComboBox(DataSet myDataSet, string connectionString)
        {
            ...
        }

        private void button1_Click(object sender, EventArgs e)
        {
            string connectionString = "UPDATE Company SET ";
            connectionString += "CompanyAdd = ?, CompanyAdd2 = ?, CompanyTown = ?,";
            connectionString += "CompanyCounty = ?, CompanyPCode = ?, CompanyTel = ?";
            connectionString += "WHERE CompanyID = ?";
            using (OleDbConnection myConnection = new OleDbConnection(connectionString))
            {
                using (OleDbCommand myCommand = new OleDbCommand(connectionString, myConnection))
                {
                    myCommand.Parameters.Add(new OleDbParameter("CompanyAdd", this.textBox3.Text));
                    myCommand.Parameters.Add(new OleDbParameter("CompanyAdd2", this.textBox4.Text));
                    myCommand.Parameters.Add(new OleDbParameter("CompanyTown", this.textBox5.Text));
                    myCommand.Parameters.Add(new OleDbParameter("CompanyCounty", this.textBox6.Text));
                    myCommand.Parameters.Add(new OleDbParameter("CompanyPCode", this.textBox7.Text));
                    myCommand.Parameters.Add(new OleDbParameter("CompanyTel", this.textBox8.Text));
                    myCommand.Parameters.Add(new OleDbParameter("CompanyID", this.textBox1.Text));
                    myCommand.Connection.Open();
                    myCommand.ExecuteNonQuery();
                }
            }
        }
    }
}

[eclipsed4utoo]

can you post the connection string in it's entirety?