Syntax error in INSERT INTO statement.

Andrew

Hi,

I have a program to communicate with database. One of the functions is to INSERT data into the database, which is giving me a syntax error!

I can't figure out why...

"INSERT INTO Report ('Date', 'Reporter', 'Details') VALUES ('27/1/2010', 'Mark Smith, 11260(S)', 'gfdsgsdfgsdf')"

I also tried it like this:

"INSERT INTO Report (Date, Reporter, Details) VALUES ('27/1/2010', 'Mark Smith, 11260(S)', 'gfdsgsdfgsdf')"

Can someone please help?

Andrew

13 Replies

Know the answer? Post it — somebody with the same question will find it here.

theLizard16y ago
Accepted answer
then, what you have here should work.

string command = "INSERT INTO Report (Date, Reporter, Details) VALUES ('" + DateText.Text + "', '" + ReporterText.Text + "', '" + DetailsText.Text + "')";

Questions? what database engine are you using.

What are the values being parsed

What is the value of command on a break point.

If there are no problems with the values and you are not reserved words, I am not sure if Date is, if it is you may need to bracket like [Date] otherwise there is no reason why you would get an error from what I see.

0
Andrew16y ago
Hi theLizard,

I didn't use integers as ID, since the ID includes (s) or an other letter.
I didn't even use auto number since users use their real identification, that is, state ID, which includes (s).

(s) stands for which country you come from...

Hope I didn't confuse you ;)
0
theLizard16y ago
Hi Andrew,

I do not use Access so I don't know for sure how access works with ID but I would have expected that id's are integers what does (S) represent!

Is ID your identifier or an auto increment, just curious. I may also learn something new today... :)
0
Andrew16y ago
Hi theLizard,

Actually I read it much more than one time lol
No, the ID is of type TEXT, which explains the single quotes.
It is not integer for the reason that our ID is not just '224536' BUT '224536(S)' for example...

Andrew
0
theLizard16y ago
Hi Andrew,

Sometimes you need to read things more than once, this is one of those times, generally ID's are integers so I think where you say WHERE ID = '" + impId + "';" is your problem

can you see anything wrong? you have single quotes either side of your impId, these indicate a string data type, remove the single quotes and your statement should work.
0
Andrew16y ago
Hi theLizard,

I had already tried [Time] but didn't make difference.
I also tried changing A/D to A_D, and changing Time to time but for nothing.

I totally can't figure out the problem!

Exception Details: System.Data.OleDb.OleDbException: Syntax error in UPDATE statement.

Still, thanks for your help theLizard!
0
theLizard16y ago
Hi Andrew,

at first glance it looks ok but I think Time may also be one of those words, I tend to begin my vars in lower case this normally overcomes these types of issues, date is not the same as Date so sql server does not complain.

string MyString = "UPDATE Md SET Name='" + NText.Text + "',A/D (not sure what / would do) ='" + DText.Text + "',[Time]='" + TText.Text +"' WHERE ID='" + ImpId + "';";

other examples of how I name vars is Customer Name -> customerName, Supplier Name -> supplierName
0
Andrew16y ago
theLizard, first of all thanks for your last comment, regarding reserved words, as when I did as you told me, ie [Date] instead of Date, it worked fine :)
Thanks for that.

Another problem I am facing is a syntax error in an UPDATE statement... I am suspecting it is something similar, but I can't figure out what :|

string MyString = "UPDATE Md SET Name='" + NText.Text + "',A/D='" + DText.Text + "',Time='" + TText.Text +"' WHERE ID='" + ImpId + "';";

The strange thing is that another similar function below works perfectly... I can't see the difference

string MyString = "UPDATE Progress SET DN ='" + Dct.Replace("'", "''") + "'WHERE ID ='" + ImpId + "';";

What database engine are you using. : Ms Access

What are the values being parsed . : All values are of type TEXT

Thanks in advance!
0
Andrew16y ago
Hi theLizard,

I tried it both ways you told me,

string command = "INSERT INTO Report (Date, Reporter, Details) VALUES ('" + DateText.Text + "', '" + ReporterText.Text + "', '" + DetailsText.Text + "')";

and it gave me the usual:

"OLEDBEXception was unhandled by user code:
Syntax error in INSERT INTO statement." :(
0
theLizard16y ago
why are you doing this?

string command = "INSERT INTO Report " + "(Date, Reporter, Details) " + "VALUES ('" + DateText.Text + "', '" + ReporterText.Text + "', '" DetailsText.Text.Replace("'", "''") + "')";

do this

string command = "INSERT INTO Report (Date, Reporter, Details) VALUES ('" + DateText.Text + "', '" + ReporterText.Text + "', '" you missed the + here (DetailsText.Text = DetailsText.Text.Replace("'", "''")) + "')";

this should be (DetailsText.Text = DetailsText.Text.Replace("'", "''")) //if this does not work in line, try doing it before you build the command string and just do + DetailsText.Text..
0
Andrew16y ago
Hi,

thanks for the reply.
You are right about asking these questions, because what I pasted was the output given when debugging.
The real command is :

string command = "INSERT INTO Report " + "(Date, Reporter, Details) " + "VALUES ('" + DateText.Text + "', '" + ReporterText.Text + "', '" DetailsText.Text.Replace("'", "''") + "')";

Date is just Text, like everything else.

The strange thing is that in another form, the same format for an Insert statement was used.

Thanks
Andrew
0
Vasanth16y ago
Andrew , is [date] column datatype is datetime or varchar.

If varchar means your second query is right.

But if [date] datatype is datetime means , please cast datetime as like sqlserver format. Please run the below query.

INSERT INTO Report (Date, Reporter, Details) VALUES ('1/27/2010', 'Mark Smith, 11260(S)', 'gfdsgsdfgsdf')

Just i changed the date column value. If its not helps you please revert table schema also and error text.

If it helps you , please mark as answer.

0
Amit Choudhary16y ago
hi friend,

your second one is right with syntax but i have dobut i your query

"INSERT INTO Report (Date, Reporter, Details) VALUES ('27/1/2010', 'Mark Smith,1260(S)', 'gfdsgsdfgsdf')"

use escape sequence before inserting special characters in your data using insert statement.

use
"INSERT INTO Report (Date, Reporter, Details) VALUES ('27/1/2010', 'Mark Smith\\,1260\\(S\\)', 'gfdsgsdfgsdf'){escape '\\'}"

Please mark as answer if it helps.
0