C# syntax error in INSERT INTO statement

Question

I'm having the error at the line: ins.ExecuteNonQuery().ToString(); OledDbException was unhandled Syntax error in INSERT INTO statement.

How do I fix this?

string strOleDbConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\Project.mdb";

OleDbConnection objConnection = new OleDbConnection(strOleDbConnectionString);
string newTagID = textBox1.Text;
string newUser = textBox2.Text;
string newAge = textBox3.Text;
string newPhoneNumber = textBox4.Text;

string insertString = "INSERT INTO jiahe ([Tag ID], User, Age, [Phone Number]) VALUES ('" + newTagID + "', '" + newUser + "', '" + newAge + "', '" + newPhoneNumber + "')";

OleDbCommand ins = new OleDbCommand(insertString, objConnection);
ins.Connection.Open();
ins.ExecuteNonQuery().ToString();
ins.Connection.Close();

Look into use parameterized queries for an OleDb provider. — Anthony Pegram
– Anthony Pegram, Commented Oct 4, 2011 at 15:56
This doesn't answer your question, and it sort of depends on your userbase, but can you afford not to worry about SQL injection in your application? — Richard Ev
– Richard Ev, Commented Oct 4, 2011 at 15:58
have your code spit out the generated insertString. There's probably a stray ' in one of the inserted values causing the syntax error. — Marc B
– Marc B, Commented Oct 4, 2011 at 15:58
Because it's been mentioned but not linked to. Read this first, then do many more searches until you really understand the problem: en.wikipedia.org/wiki/SQL_injection — Jamiec
– Jamiec, Commented Oct 4, 2011 at 16:05
Sorry, I'm actually very new to C#. What's SQL injection? And yes, I see an error in newAge but that doesn't cause the the syntax error right? A stray ' in the inserted values? — Esther EatFries
– Esther EatFries, Commented Oct 4, 2011 at 16:05

InBetween · Accepted Answer · 2011-10-04 16:16:58Z

1

Your problem is probably one these three:

Outright syntax error not clearly visible with the hideous unparametrized SQL statement :p
newUser or some other field has a ' somewhere and is screwing up the syntax.
You are trying to insert a numeric value (Age?) as a string.

You should easily solve the first two creating a breakpoint after the insertString statement construction and checking out what the string really contains. The third one is even easier to check, just review the data types of the table's fields in your data base.

answered Oct 4, 2011 at 16:16

InBetween

32.9k3 gold badges52 silver badges98 bronze badges

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

Comments

Bahri Gungor · Accepted Answer · 2011-10-04 16:30:52Z

Notwithstanding, you should change the use of your command to use parameters and not build the query string with string concatenation (which is susceptible to sql injection attacks).

The issue is most likely because [Tag ID], User, Age, [Phone Number] are not all strings. In your SQL, any non-string column data should not be wrapped by quotes (').

To fix the immediate problem (assuming [Tag ID] is an integer):

    string insertString = "INSERT INTO jiahe ([Tag ID], User, Age, [Phone Number]) VALUES (" + newTagID + ", '" + newUser + "', '" + newAge + "', '" + newPhoneNumber + "')";

However, you should structure your code to avoid sql injection, have cleaner code, and also not worry about the quotes:

    string insertString = "INSERT INTO jiahe ([Tag ID], User, Age, [Phone Number]) VALUES (@TagID, @User, @Age, @PhoneNumber)";

    OleDbCommand ins = new OleDbCommand(insertString, objConnection);
    ins.Parameters.Add(new OleDbParameter("@TagID",newTagID);
    ins.Parameters.Add(new OleDbParameter("@User",newUser);
    ins.Parameters.Add(new OleDbParameter("@Age",newAge);
    ins.Parameters.Add(new OleDbParameter("@PhoneNumber",newPhoneNumber);
    ins.Connection.Open();
    ins.ExecuteNonQuery();
    ins.Connection.Close();

Collectives™ on Stack Overflow

C# syntax error in INSERT INTO statement

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related