CODEWITHSUNDEEP
sqlms-accesssql-insertinsert-into
kdem
kdem

Reputation: 24

Syntax error in INSERT INTO statement

I am trying to insert some information in an MS Access database. In my database I have the following columns and types:

log_order - Autonumber (I need this to keep the order where inserted in the db),
userID - Text,
time - Text,
date_ - text,
message - Text.

My query:

command.CommandText = "INSERT INTO logs(userID, time, date_, message) VALUES ('"+verifiedUser+"', '"+msg_time+"', '"+msg_date+"', '"+msg+"')";        

OleDbDataReader reader = command.ExecuteReader();

The error that I get:

System.Data.OleDb.OleDbException: 'Syntax error in INSERT INTO statement.'

I tried several posts but no post helped me. I believe there might be a problem with the autonumber column (log_order). Because of what I remember I don't have to include it in the query.

PS: I know I have to pass the values as parameters.

Thank you in advance

Upvotes: -1

Views: 5813

Answers (4)

omoor
omoor

Reputation: 73

Is your date_ variable a string or a DateTime? check in your sql database if your msg_date cell is for dates or for strings, if it is for dates, simply write:

command.CommandText = "INSERT INTO logs(userID, time, date_, message) VALUES ('"+verifiedUser+"', '"+msg_time+"', #"+msg_date+"#, '"+msg+"')";        

OleDbDataReader reader = command.ExecuteReader();

the # signifies to the database that you are inserting a date and not a string and therefore it matches it's date type cell.

Upvotes: 0

Jonathan Willcock
Jonathan Willcock

Reputation: 5255

I totally agree with all that has been said, but to answer your question directly, I am pretty sure you will need to put square brackets around your field names. OleDb tends not to like special characters and could well be having a problem for example with date_ ; sending [date_] instead should get round the issue. It will not like time either. Same solution

Addendum on SQL Injection

As an aside, in fact calling Access through OleDb is relatively protected from SQL Injection. This is because any attempt to execute multiple instructions in one command fails. (You get an incorrect formatted string error). So whilst you could argue that what you are doing is safe, it is not for other db providers. The sooner you get into good habits, the less likely you will be to introduce a vulnerability in a case where it could be dangerous. If it seems like you are getting a stream of abuse, it is just because everyone here wants to keep the net safe.

Upvotes: 0

sosspyker
sosspyker

Reputation: 93

It seems your data in some of the variables passed in INSERT may be causing this error. Try debugging the value in command.CommandText before executing it. If any of the variables have a single quote they must be escaped... Ref: How do I escape a single quote in SQL Server?

Also brush up on SQL Injection Ref: SQL Injection

Upvotes: 0

Caius Jard
Caius Jard

Reputation: 74710

Probably one of your variables (msg?) contains an apostrophe

The way you've written your SQL is a massive security risk. Please immediately look up "parameterized queries" and never, ever, ever write an sql like this again (where you use string concatenation to tack the values into the query). Your code has a proliferation of issues and using parameterized queries will solve all of them; they aren't difficult to write

enter image description here

Upvotes: 2

Related Questions