Very simple INSERT INTO with a DateTime parameter -> "Syntax Error in INSERT INTO statement"

30 Nov 2004 7:20 PM

loquak

Hello.
The following very simple code does not seem to accept the DateTime variable
as a parameter - why?
The table (MS Access db) here contains only a primary key and a column Date
of type date/time.

connection.Open();
transaction = connection.BeginTransaction();

commandString = "INSERT INTO [Events] (Date) Values(@Date)";

command = connection.CreateCommand();
command.CommandText = commandString;
command.Transaction = transaction;

OleDbParameter param_Date = new OleDbParameter("@Date",
OleDbType.DBTimeStamp); // Also tried DBTime and DBDate
param_Date.Value = date; // date == DateTime
command.Parameters.Add(param_Date);

// Execute command
command.ExecuteNonQuery(); // Syntax error in INSERT INTO statement???

30 Nov 2004 7:30 PM

Jeff Dillon

Is Date your fieldname? Not always a good choice to use reserved words.
Does the INSERT statement work in Access?

Shouldn't it be [Events].[Date] ?

Have you tried Text datatype?

Jeff
Show quoteHide quote

"loquak" <liam_p***@hotmail.com> wrote in message
news:u5b7ZGx1EHA.1296@TK2MSFTNGP10.phx.gbl...
> Hello.
> The following very simple code does not seem to accept the DateTime
variable
> as a parameter - why?
> The table (MS Access db) here contains only a primary key and a column
Date
> of type date/time.
>
> connection.Open();
> transaction = connection.BeginTransaction();
>
> commandString = "INSERT INTO [Events] (Date) Values(@Date)";
>
> command = connection.CreateCommand();
> command.CommandText = commandString;
> command.Transaction = transaction;
>
> OleDbParameter param_Date = new OleDbParameter("@Date",
> OleDbType.DBTimeStamp); // Also tried DBTime and DBDate
> param_Date.Value = date; // date == DateTime
> command.Parameters.Add(param_Date);
>
> // Execute command
> command.ExecuteNonQuery(); // Syntax error in INSERT INTO statement???
>
>
>

30 Nov 2004 7:40 PM

loquak

Thanks for the quick response
Damn I feel dumb... I'm not used to using those brackets, because the
statements usually work well without them, but not in this case - it worked
after adding those brackets :-)

Show quoteHide quote

"Jeff Dillon" <j***@removeemergencyreporting.com> wrote in message
news:uo9bSMx1EHA.2192@TK2MSFTNGP14.phx.gbl...
> Is Date your fieldname? Not always a good choice to use reserved words.
> Does the INSERT statement work in Access?
>
> Shouldn't it be [Events].[Date] ?
>
> Have you tried Text datatype?
>
> Jeff
> "loquak" <liam_p***@hotmail.com> wrote in message
> news:u5b7ZGx1EHA.1296@TK2MSFTNGP10.phx.gbl...
>> Hello.
>> The following very simple code does not seem to accept the DateTime
> variable
>> as a parameter - why?
>> The table (MS Access db) here contains only a primary key and a column
> Date
>> of type date/time.
>>
>> connection.Open();
>> transaction = connection.BeginTransaction();
>>
>> commandString = "INSERT INTO [Events] (Date) Values(@Date)";
>>
>> command = connection.CreateCommand();
>> command.CommandText = commandString;
>> command.Transaction = transaction;
>>
>> OleDbParameter param_Date = new OleDbParameter("@Date",
>> OleDbType.DBTimeStamp); // Also tried DBTime and DBDate
>> param_Date.Value = date; // date == DateTime
>> command.Parameters.Add(param_Date);
>>
>> // Execute command
>> command.ExecuteNonQuery(); // Syntax error in INSERT INTO statement???
>>
>>
>>
>
>

30 Nov 2004 7:41 PM

Miha Markic [MVP C#]

Change the @Date to ? in sql statement and possibly put Date in square
brackets.

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
SLODUG - Slovene Developer Users Group
www.rthand.com

Show quoteHide quote

"loquak" <liam_p***@hotmail.com> wrote in message
news:u5b7ZGx1EHA.1296@TK2MSFTNGP10.phx.gbl...
> Hello.
> The following very simple code does not seem to accept the DateTime
> variable as a parameter - why?
> The table (MS Access db) here contains only a primary key and a column
> Date of type date/time.
>
> connection.Open();
> transaction = connection.BeginTransaction();
>
> commandString = "INSERT INTO [Events] (Date) Values(@Date)";
>
> command = connection.CreateCommand();
> command.CommandText = commandString;
> command.Transaction = transaction;
>
> OleDbParameter param_Date = new OleDbParameter("@Date",
> OleDbType.DBTimeStamp); // Also tried DBTime and DBDate
> param_Date.Value = date; // date == DateTime
> command.Parameters.Add(param_Date);
>
> // Execute command
> command.ExecuteNonQuery(); // Syntax error in INSERT INTO statement???
>
>
>