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

[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?