I am getting an error when I try to update records in my access database

Question

I have a small program that connects to an access database, and what I am trying to do is update(edit) a selected record via an edit form. When I execute my code, I get this error:

System.Data.OleDb.OleDbException was unhandled
  Message=Syntax error (missing operator) in query expression '5346 S. Eubank blvd'.
  Source=Microsoft Access Database Engine
  ErrorCode=-2147217900

Needless to say, it is for the Address field..

Here is my code block:

private void saveChangeBtn_Click(object sender, EventArgs e)
{
    Customer.SetCustID(Convert.ToInt32(editIdTB.Text));
    Customer.SetFirstName(editFirstNameTB.Text);
    Customer.SetLastName(editFirstNameTB.Text);
    Customer.SetAddress(editAddressTB.Text);
    Customer.SetPhoneNum(editPhoneTB.Text);
    Customer.SetEmail(editEmailTB.Text);

    using (OleDbConnection connect = new OleDbConnection(connectionString))
    {
        OleDbCommand cmd = new OleDbCommand();
        connect.Open();

        cmd.Connection = connect;
        cmd.CommandText = "UPDATE Customers SET [Customer ID]=" + Customer.GetCustId() +
            ", [First Name]=" + Customer.GetFirstName() +
            ", [Last Name]=" + Customer.GetLastName() +
            ", [Address]=" + Customer.GetAddress() +
            ", [Phone Number]=" + Customer.GetPhoneNum() +
            ", [Email Address]=" + Customer.GetEmailAddress() + 
            ", WHERE [Customer ID]=" + editIdTB.Text + "";
        cmd.ExecuteNonQuery();
        connect.Close();
        MessageBox.Show("Changes made successfully!", "Success!", MessageBoxButtons.OK);
    }
    this.Close();
}

Should there be a comma before the WHERE? Do you allow the user to change the Customer ID? Seems like you wouldn't want to do so, or use a different primary key if so. If you simplify the query to just update address and skip the rest do you still get the same error? — Tad, Dec 16 '12 at 04:19
Echo your cmd.CommandText to the page or a log file so you can see what you are trying to execute. — Tad, Dec 16 '12 at 04:22
I did just the address and go this error: System.Data.OleDb.OleDbException was unhandled Message=Syntax error in string in query expression ''5346 S. Eubank blvd WHERE [Customer ID]=3'. Source=Microsoft Access Database Engine ErrorCode=-2147217900 StackTrace: at — Carson, Dec 16 '12 at 04:27
StackTrace: at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr) at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) — Carson, Dec 16 '12 at 04:30
It's way too long for me to even try and post in these stupid boxes — Carson, Dec 16 '12 at 04:31

score 0 · Answer 1 · answered Dec 16 '12 at 04:20

0

I think the problem you have is the comma before WHERE. Try remove that and give it a try.

It would be easier to diagnostics if you can capture the exact sql your executing, and try to run it in a query browser.

Also, I recommend you to use string.format when you are constructing the sql. For a better solution, try LINQ to SQL or Entity Framework.

answered Dec 16 '12 at 04:20

Jason Ching

1,991
1
19
23

Well, I changed it to no avail... even added single quotes around all the strings and put all of my values inside parentheses, which didn't help... I will try running it in a browser though, that is a good idea. – Carson Dec 16 '12 at 04:24

score 0 · Accepted Answer · answered Dec 16 '12 at 04:35

0

Try this

cmd.CommandText = "UPDATE Customers SET [First Name]='" + Customer.GetFirstName() +
            "', [Last Name]='" + Customer.GetLastName() +
            "', [Address]='" + Customer.GetAddress() +
            "', [Phone Number]='" + Customer.GetPhoneNum() +
            "', [Email Address]='" + Customer.GetEmailAddress() + 
            "' WHERE [Customer ID]=" + editIdTB.Text;

answered Dec 16 '12 at 04:35

Tad

934
6
10

That worked! I see you got rid of the excess quotes at the end, and dropped the Customer ID! Excellent! Thank you! – Carson Dec 16 '12 at 04:38
And quotes around the values... didn't realize that was an issue as well! – Carson Dec 16 '12 at 04:38
You'll probably want to search and replace single quotes with 2 single quotes in any of these values or your SQL statement will break again. – Tad Dec 16 '12 at 04:41

score 0 · Answer 3 · answered Dec 16 '12 at 04:37

0

you need to put quotes around the values. that should solve the main problem here.

however, you have a pretty enormous security flaw here. google "sql injection" and you'll see that a bad guy can seriously ruin your week by putting malicious text into the editIfTB textbox

answered Dec 16 '12 at 04:37

Robert Levy

28,747
6
62
94

This is just a personal project, and won't contain any sensitive data. But when I create more substantial programs, I certainly will take up your advice. Never the less, I'll look into it, thanks! – Carson Dec 16 '12 at 04:44

score 0 · Answer 4 · answered Jun 18 '13 at 15:03

Apart from security vulnernability, constructing queries this way will still have a stability problem. As soon as one of your data fields includes an apostrophe, the SQL will break again (e.g. surname O'Neill). Best practice is to supply all data values via parameters; it avoids the need to concatenate in all those single-quotes/apostrophes, won't be sensitive to data values, and won't have a security vulnerability.

I am getting an error when I try to update records in my access database

4 Answers4

Linked