-1

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

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
kdem
  • 24
  • 1
  • 6
  • Try your query directly in Access and see what errors you get. I would guess maybe having quotes around the verifiedUser might cause a problem, if that's actually a numeric field. –  Jul 29 '17 at 06:03
  • @marc_s please don't do it.. I realise that many users come to SO for the quick fix their crap solution needs, but sometimes they really need to be told to start it over properly. You never know who might be writing an ecommerce solution that one day will store YOUR credit card number ;) – Caius Jard Jul 29 '17 at 06:10

4 Answers4

2

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

Caius Jard
  • 72,509
  • 5
  • 49
  • 80
0

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

sosspyker
  • 83
  • 7
0

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.

Jonathan Willcock
  • 5,012
  • 3
  • 20
  • 31
0

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.

omoor
  • 73
  • 8