topic Date/Time Field in Access 2000 - INSERT INTO ... syntax error in LabVIEW

Date/Time Field in Access 2000 - INSERT INTO ... syntax error

G.G.K. — Thu, 05 Jun 2003 21:07:33 GMT

I am using Labview 6.1 Professional with the Database Connectivity Toolkit. When I execute this SQL Command "INSERT INTO PRODUCTION (Part, Passed, Date) VALUES ('Part#1', 2, '10/10/2003 10:10:10 AM')" I get a Run-Time Syntax Error. If I remove the Date Label and Date Value it works. What is the proper Format of the Date/Time Type Field in ACCESS 2000 for Labview 6.1 SQL? I have seen and tried all of the solutions already posted like MSG 1ZNAJHJ6.

Re: Date/Time Field in Access 2000 - INSERT INTO ... syntax error

mikeporter — Fri, 06 Jun 2003 00:09:39 GMT

Well, if it's any consolation handling dates is a pain regardless of which database you use. In any case, I created a table with the following definition:

CREATE TABLE testing
(item1 INTEGER,
item2 DATE)
;

I them did the following insert:

INSERT INTO testing
VALUES (1, '18/11/1953 04:20:00');

And everything worked fine. Note that in the date (my Bday, BTW) the format is DD/MM/YYYY followed by the time.

What exactly is the error you're getting? Can you post the exact text of the error message. Also, I ran my test using the examples that come with LabSQL--not the toolkit.

Mike...

Oops, just noticed something. You have a column name that is probibly a reserved word "Date". Try your insert as:

INSERT INTO PRODUCTION
VALUES
('Part#1', 2, '10/10/2003 10:10:10 AM')

If there is only those three columns and they the order the data appears is the same as the column order, you don't need the column list. If this works (and it should--I just tried it) I would only view it as a temporary patch. The column name should be changed.

This is also a good reason to not use the Access GUI to create tables. If you tried creating a table like that in SQL you would have gotten an error message. Learning to build tables in SQL code isn't hard and it adds an extra layer of error checking that the GUI apparently doesn't think is important.

If you're interested let me know and I can send you the info on a really good book on SQL...

Re: Date/Time Field in Access 2000 - INSERT INTO ... syntax error

G.G.K. — Fri, 06 Jun 2003 00:59:32 GMT

Mike,
Removing the Column Names from the SQL CMD solved the Syntax Error. So, I changed the Column name from "Date" to "DateTime". Now the "INSERT INTO PRODUCTION ( PartNumber, Passed, DateTime ) VALUES ( 'Part#1', 101, '12/31/2003' ) WORKS. And without having to enter a TIME Value, either. Thanks Dude! The Database Tables were actually created in Access 2000 not SQL nor Labview. Yes, I agree, Date & Time fields are weird.

What is that name of the SQL Book?

Greg Klocek

Re: Date/Time Field in Access 2000 - INSERT INTO ... syntax error

Ben — Fri, 06 Jun 2003 12:34:48 GMT

Hi Greg!

Remember me? I spent two weeks working for you one week.

Ben

Re: Date/Time Field in Access 2000 - INSERT INTO ... syntax error

G.G.K. — Fri, 06 Jun 2003 13:47:15 GMT

Yes, I do. HI! How you doing? I work for Innovar Systems Limited in North Jackson Industrial Park. I do Labview almost exclusively, now.

The DATE/TIME field in ACCESS 2000 is weird when accessing thru Labview Connectivity Toolkit. In SQL UPDATE or SELECT CMD I have to use "WHERE DateTimeField = #YYYY-MM-DD#". Yes, POUND SIGNS! But for the INSERT INTO TABLE CMD I use a Cluster for the Fields with the Labview SLASH Codes "\01date/time\0112/31/2003" String.

In Access 2000 you can Name a Feild "DATE" but you get a Runtime Syntax error in Labview when trying to access the Table wit a Field named "DATE".

Weird indeed. Very Strange format. But it works!

Gregory Klocek

Re: Date/Time Field in Access 2000 - INSERT INTO ... syntax error

Ben — Fri, 06 Jun 2003 14:47:39 GMT

DB's sometimes have reserved names. Date is one of them.

Some DB's also do not like spaces in the names. That is why you see names that are "AllRunTogether". Some DB do not allow underscores either.

The last time I looked connectivity toll kit came with a "browser" type thing that let you browse through the DB after you point at it. It also will let you interactively build SQL statements that are correct for your DB. (NO, not all SQL syntaxes are not all the same). I use that utility any time I run into trouble trying to get my SQL statement correct.

Ben

Re: Date/Time Field in Access 2000 - INSERT INTO ... syntax error

mikeporter — Sat, 07 Jun 2003 03:04:28 GMT

Look for:

"The Practical SQL Handbook: Using Structured Query Language", by Judith S. Bowman, Sandra L. Emerson and Marcy Darnovsky.

Also be aware that for some databases "datetime" is also a reserved word--it's in fact a synonym for "date". Safest is to use something a little more specific like "prod_date" or something of that nature. Also be aware that SQL in the language syntax is NOT case-sensitive.

Mike...

Re: Date/Time Field in Access 2000 - INSERT INTO ... syntax error

lmd2 — Thu, 12 Feb 2004 19:53:47 GMT

you solved my problem, those pound signs were a killer to debug.
Thanx,
keep drawing code
lmd2

Re: Date/Time Field in Access 2000 - INSERT INTO ... syntax error

romulus — Fri, 06 Oct 2006 17:30:26 GMT

This is an excellent post. I had the exact same problem today. Trying to create a new table with DB Table Create.vi, and when the table format array would get passed, LabVIEW would return a -20xxxx error, don't remember the exact number. It's because I was trying to pass 2 fields named Date and Time into the table....changed it to Date_ and Time_ and everything works now. Thanks.