Insert DATETIME value into Access database using ODBC

0
votes

Fairly new to SQL. Our application targets an older Access (pre-2007) database. My table has a number of fields of various types such as SMALLINT, VARCHAR, DATETIME.

In the example below:

  • "Study" is of type SMALLINT
  • "Date" is of type DATETIME
  • "Patient" is of type VARCHAR

I'm trying to insert new records into a MyTable using the SQL statement:

OdbcConnection connection = new OdbcConnection(connectionString);
OdbcCommand command = connection.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = "INSERT INTO Studies (Study, Date, Patient) VALUES (1411,#2021-20-23#,'Fred');";
command.ExecuteNonQuery();

Every time the ExecuteNonQuery throws an exception: {"ERROR [42000] [Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement."}

Modify the INSERT and the record is inserted correctly:

"INSERT INTO Studies (Study, Patient) VALUES (1411,'Fred');"

I've tried formatting the date/time variable various ways:

  • #yyyy-mm-dd#
  • #yyyy-mm-ddThh:mm:ss#
  • #yyyy-mm-dd hh:mm:ss#
  • 'yyyy-mm-dd' (and with time)
  • yyyy-mm-dd (and with time)

...none of these seem to work.

How do you insert DATETIME values into an Access database using ODBC?

2
c#vb.netdatetimems-accessodbc
Use Command Parameters to pass values. - Jimi
Date is a reserved word in Access. What happens if you enclose in [ ] - [Date]? Or use / instead of - in date structure. - June7
I feel compelled to point out that 2021-20-23 is not a valid date, so even when you do quote Date as June7 said, it'll certainly complain about that eventually, if not next. - madreflection
@June7: Oh good spot...I did try [Date] which didn't seem to help, but will try the / now. - AlainD
Oops, I've made several mistakes here, reflecting my inexperience with SQL, ODBC and Access. June7, madreflection and Jimi are all right. My formatting should be "#yyyy-MM-dd HH:mm:ss#" (I was using "#yyyy-mm-ddThh:mm:ss#" which has two mistakes), the "Date" field needs to be delimited with [] brackets, and finally I should aim to use command parameters as Jimi points out. Thanks everyone! - AlainD

2 Answers

0
votes

Many thanks to the commentators, particularly @June7 and @madreflection, for catching some basic errors in the syntax:

  • Date is a reserved keyword in Access. If your field is (unfortunately) called "Date" it needs to be quoted. Use [Date], not Date.
  • DateTime formatting was incorrect. It should be #yyyy-MM-dd# or #yyyy-MM-dd HH:mm:ss#. Note the error with "mm" instead of "MM" for the month, and "hh" instead of "HH" for the hour.
  • As @Jimi points out, while you can build up the INSERT INTO statement manually, a better approach is to use IDbCommand.Parameters. This simplifies of the formatting issues while building the command.
0
votes

Well, often a lot of people jump in and say hey! - you should use parameters.

And they are right - but ONE big bonus feature? All the of the type conversions are done automatic. So in place of error prone strings? You wind up with this:

    Using cmdSQL As New OdbcCommand("INSERT INTO Studies (Study, [Date], Patient) VALUES (@Study, @Date, @Patient",
                                     New OdbcConnection(connectionString))

        cmdSQL.Parameters.Add("@Study", OdbcType.Int).Value = 1411
        cmdSQL.Parameters.Add("@Date", OdbcType.Date).Value = "2021-20-23"
        cmdSQL.Parameters.Add("@patient", OdbcType.NVarChar).Value = "Fred"
        cmdSQL.Connection.Open()
        cmdSQL.ExecuteNonQuery()

        cmdSQL.Dispose()

    End Using

Now, of course your example was just that - an example. But, if you have strong typed vars, the you can get to do this:

cmdSQL.Parameters.Add("@Study", OdbcType.Int).Value = intStudy
cmdSQL.Parameters.Add("@Date", OdbcType.Date).Value = dtInvoiceDate
cmdSQL.Parameters.Add("@DateT", OdbcType.DateTime).Value = dtInvoiceTime
cmdSQL.Parameters.Add("@Customer", OdbcType.NVarChar).Value = strCompanyName

So, as you create the above, you can hit ctrl-d to duplicate the first line - so you not typing much. You just edit + change the parameter values. And you NEVER have to mess with quotes, no quotes, or date delimiters (assuming you declared the vars as date, or datetime.

I find saying good bye to mess string concatenations? Well, not only is this sql injection safe - but it just plain less messy to deal with. You use your clean variables, get strong data type checking, and you don't have to think or remember rules about quotes, no quotes or some kind of date format. It just clean code, and you just assign values.

So, it is a wee bit more code, but you actually not having to type the code (with ctrl-d to duplicate the first param line, then you just cursor around in the code and replace over the @parm and the .value. And you can work against a form data input like this:

cmdSQL.Parameters.Add("@Study", OdbcType.Int).Value = cboDrop.dataValue
cmdSQL.Parameters.Add("@Date", OdbcType.Date).Value = InvoiceDate.text
cmdSQL.Parameters.Add("@DateT", OdbcType.DateTime).Value = InvoiceTime.Text
cmdSQL.Parameters.Add("@Customer", OdbcType.NVarChar).Value = CompanyName.Text

So, once you introduce controls, then you are REALL in trouble if you use a string - since you can't just string in the controls and thus the string sql approach gets even worse and more difficult to write.