C# Syntax error when creating a query with OleDbParamater

Question

I am using OleDbParameter to build an OleDbCommand to INSERT a record into MS Access. But I get Syntax Error when I am executing the statement. If anyone can see what is preventing the query successfully executing it would be a huge help.

This is my code:

string query = "INSERT INTO Customer (customerID, date, time, telephone, fax,"
    + "name, sourceAddress, destAddress, via1, via2, via3,"
    + "priority, ourReference) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

OleDbCommand oleDbCommand = new OleDbCommand(query, oleDbConnection);

OleDbParameter customerID = new OleDbParameter();
OleDbParameter date = new OleDbParameter();
OleDbParameter time = new OleDbParameter();
OleDbParameter telephone = new OleDbParameter();
OleDbParameter fax = new OleDbParameter();
OleDbParameter name = new OleDbParameter();
OleDbParameter sourceAddress = new OleDbParameter();
OleDbParameter destAddress = new OleDbParameter();
OleDbParameter via1 = new OleDbParameter();
OleDbParameter via2 = new OleDbParameter();
OleDbParameter via3 = new OleDbParameter();
OleDbParameter priority = new OleDbParameter();
OleDbParameter ourReference = new OleDbParameter();

oleDbCommand.Parameters.Add(customerID);
oleDbCommand.Parameters.Add(date);
oleDbCommand.Parameters.Add(time);
oleDbCommand.Parameters.Add(telephone);
oleDbCommand.Parameters.Add(fax);
oleDbCommand.Parameters.Add(name);
oleDbCommand.Parameters.Add(sourceAddress);
oleDbCommand.Parameters.Add(destAddress);
oleDbCommand.Parameters.Add(via1);
oleDbCommand.Parameters.Add(via2);
oleDbCommand.Parameters.Add(via3);
oleDbCommand.Parameters.Add(priority);
oleDbCommand.Parameters.Add(ourReference);

customerID.Value = 4;
date.Value = "01/01/01";
time.Value = "01:01:01";
telephone.Value = 01010101;
fax.Value = 01010101;
name.Value = "test 4";
sourceAddress.Value = "test 4 source address";
destAddress.Value = "test 4 dest address";
via1.Value = "test 4 via 1";
via2.Value = "test 4 via 2";
via3.Value = "test 4 via 3";
priority.Value = 1;
ourReference.Value = "test 4 ref";

MessageBox.Show(oleDbCommand.CommandText.ToString());

OleDbDataAdapter oleDbDataAdapter = new OleDbDataAdapter(oleDbCommand);

oleDbCommand.CommandText = query;

oleDbConnection.Open();

oleDbCommand.ExecuteNonQuery();

Given that it's a syntax error, it would really help if you'd show us the query instead of just the parameters. — Jon Skeet
– Jon Skeet, Commented Sep 25, 2012 at 10:02
sorry i forgot about the most important part, I edited it into the original post. — user1696698
– user1696698, Commented Sep 25, 2012 at 10:05

Steve · Accepted Answer · 2012-09-25 10:17:08Z

3

The fields date and time are reserved words for JET/Access, so you should encapsulate them in square brackets. (this is the real origin of the Syntax Error message)

"INSERT INTO Customer (customerID, [date], [time], telephone, fax, name, " + 
"sourceAddress, destAddress, via1, via2, via3," + "priority, ourReference) VALUES " + 
"(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

Of course the number of parameters should match. (12 placeholder vs 13 fields/parameters, add another placeholder ?), but this will give another error message, not the syntax error stated

edited Sep 25, 2012 at 10:17

answered Sep 25, 2012 at 10:11

Steve

217k22 gold badges242 silver badges296 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user1696698 Over a year ago

Thanks Steve, hole in one! the query now inserts into the database :)

Kek · Accepted Answer · 2012-09-25 10:08:22Z

1

You have 13 parameters, but only 12 ?

answered Sep 25, 2012 at 10:08

Kek

3,2052 gold badges23 silver badges26 bronze badges

1 Comment

user1696698 Over a year ago

I added another ?, so now I have 13. But I am still getting the error Syntax error in INSERT INTO statement Is there a way of displaying the sql command that is being executed to get more clues? oleDbCommand.CommandText.ToString() displays:

INSERT INTO Customer (customerID, date, time, telephone, fax, name, sourceAddress, destAddress, via1, via2, via3,priority, ourReference) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

which isn't very helpful.

Rich Andrews · Accepted Answer · 2012-09-25 10:11:51Z

1

As others have mentioned your parameters are out of sync with your place holders, however, if your CustomerID is an autonumber then you can omit it from both the sql and the parameter list.

answered Sep 25, 2012 at 10:11

Rich Andrews

4,1883 gold badges37 silver badges48 bronze badges

Collectives™ on Stack Overflow

C# Syntax error when creating a query with OleDbParamater

3 Answers 3

1 Comment

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Related