CODEWITHSUNDEEP
c#sqldatabasems-accessoledb
Dor Meiri
Dor Meiri

Reputation: 477

C# Insert Into Access Throwing Exception

I have code that insert into Access Database.

This code usually works, but i have one table that doesn't work and i can't understand why.

This is the exception i get:

Syntax error in INSERT INTO statement.

this is the CODE:

if (connection == null) Connect();
command = new OleDbCommand(SQL);
command.Connection = connection;

try
{
    connection.Open();
    command.ExecuteNonQuery();
}
catch (Exception ex) { }
finally { connection.Close(); }

this is the SQL string:

Insert INTO TrackingsDateTimes (trackingDateTimeID, trackingID, dateTime, info) VALUES(1, 0, #02/05/2017 21:37:00#, '')

this is the table TrackingsDateTimes:

trackingDateTimeID Number
trackingID Number
dateTime Date/Time
info Text

What am i missing?

Thanks,

Upvotes: 0

Views: 531

Answers (3)

Mad Myche
Mad Myche

Reputation: 1075

First thing is you should use parameterized queries instead of inserting variables inline. The other thing is that dateTime is a reserved word in MS Access and may need to be escaped with backticks [ReservedWord].

This should give you a good start on rewriting your queries

// string qry = "Insert INTO TrackingsDateTimes ( trackingDateTimeID, trackingID, `dateTime`, info) VALUES(@trackingDateTimeID, #trackingID, @dateTime, @info)"
string qry = "Insert INTO TrackingsDateTimes ( trackingDateTimeID, trackingID, [dateTime], info) VALUES(@trackingDateTimeID, #trackingID, @dateTime, @info)"

if (connection == null) Connect();
command = new OleDbCommand(SQL);
command.Connection = connection;
command.Parameters.AddWithValue("@trackingDateTimeID", (int)1);
command.Parameters.AddWithValue("@trackingID", (int)0);
command.Parameters.AddWithValue("@dateTime", DateTime.Parse("2/05/2017 21:37:00");
command.Parameters.AddWithValue("@info", string.Empty);

Upvotes: 0

RH6
RH6

Reputation: 154

You're trying to insert dates that are surrounded by # instead of single quotes.

Replace with this

Insert INTO TrackingsDateTimes (trackingDateTimeID, trackingID, dateTime, info) VALUES(1, 0, '#02/05/2017 21:37:00#', '')

EDIT: I'm wrong, Steve is right.

Upvotes: -1

Steve
Steve

Reputation: 216293

Not really a good idea to have a column named as a reserved keyword.

DATETIME is reserved

If you really want to use that name (I suggest to change it) then you need square brackets around that name 

Insert INTO TrackingsDateTimes (trackingDateTimeID, trackingID, [dateTime], info) VALUES (.....)

Upvotes: 5

Related Questions