-4

How can I get this query to work properly? I can not save any of the data to the database, keep getting error: "Syntax error in INSERT INTO statement"

this helped a lot too

Data Types was my issue, thank you guys!

Cœur
  • 37,241
  • 25
  • 195
  • 267
HGtez
  • 89
  • 1
  • 8
  • Does table *SetUp_Track* include only those 6 fields mentioned in the question? – HansUp Nov 23 '14 at 17:42
  • those are the only fields plus the ID set to AutoNumber on the database,I was fearing it was the Last_Good_Time & First_Good_Time I am not sure if I am passing the data type correctly to the DB. – HGtez Nov 23 '14 at 19:13

3 Answers3

1

The VALUES clause should be a list of items enclosed in parentheses, (). Yours omitted the closing ).

HansUp
  • 95,961
  • 11
  • 77
  • 135
1

Your real problem is to forget to use ) at the end of VALUES part.

INSERT INTO SetUp_Track
values(@Operat, @Maching, @Last_Good_Time,
@First_Good_Time, @Post_Number, @Created
                                        ^here

Always try your command in your database manager first. But more important, OleDbCommand does not accept named parameters.

From OleDbCommand.Parameters property

The OLE DB .NET Provider does not support named parameters for passing parameters to an SQL statement or a stored procedure called by an OleDbCommand when CommandType is set to Text. In this case, the question mark (?) placeholder must should be used. For example:

SELECT * FROM Customers WHERE CustomerID = ?

Therefore, the order in which OleDbParameter objects are added to the OleDbParameterCollection must directly correspond to the position of the question mark placeholder for the parameter in the command text.

Also use using statement to dispose your OleDbConnection and OleDbCommand.

Soner Gönül
  • 97,193
  • 102
  • 206
  • 364
  • 2
    That page gets quoted a lot. Unfortunately it is misleading when it says that "the question mark (?) placeholder **must** be used" (emphasis mine). It really should say that "the question mark (?) placeholder *should* be used". The `?` placeholder is not an absolute requirement. Named parameters *do* work, but the names are irrelevant; it's still the *position* of the parameters in the CommandText and the *order* in which they are added that matters. (And that's why I maintain that `?` *should* be used, to prevent people from getting the wrong idea.) – Gord Thompson Nov 23 '14 at 13:39
  • @GordThompson Yeah, you are totally right. Using `?` is just a comman approach for parameterize of an `OleDbCommand`. Names are totally ignored. The only matter is their orders. – Soner Gönül Nov 23 '14 at 13:41
0

Try not using named parameter:

ad.InsertCommand = new OleDbCommand("INSERT INTO SetUp_Track (Operat, Maching, Last_Good_Time, First_Good_Time, Post_Number, Created) values (?, ?, ?, ?, ?, ?)", con);

Also,

All parameters in .Parameters.AddWithValue are sent with type of String,

check if all of these parameters are defined as String type in db too,

For example if Post_Number is Integer in db then you should cast the String to Integer and then use .Parameters.AddWithValue

ad.InsertCommand.Parameters.AddWithValue("@Post_Number", Convert.ToInt32(Post_NumberTxtbx.Text));
Hana Bzh
  • 2,212
  • 3
  • 18
  • 38
  • Thank you guys I had found I had a couple of parenthesis I forgot to close; however, I am still getting the above mentioned error. – HGtez Nov 23 '14 at 17:41
  • @HGtez Did you try our solutions?! – Hana Bzh Nov 23 '14 at 17:53
  • I'm honestly not sure if I am passing the data correctly to the DB from my form, I've attached the DB I am using. tHank you – HGtez Nov 23 '14 at 19:19
  • Check this link for DateTime parameters: (http://stackoverflow.com/questions/6552262/passing-a-datetime-value-as-a-parameter-to-an-oledbcommand) – Hana Bzh Nov 23 '14 at 19:34
  • Thank you that helped a lot GREAT WORK! – HGtez Nov 23 '14 at 22:44