Updating a Database Record - Syntax Error

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.

Code:

---


        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();
            }
        }

---

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.

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.

Code:

---


        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();
                }
            }
        }

---

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 :eek: :eek: :wave:

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

Code:

---


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();
                }
            }
        }
    }
}

---

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