LabVIEW

cancel
Showing results for 
Search instead for 
Did you mean: 

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

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.
0 Kudos
Message 1 of 9
(8,366 Views)
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...

Certified Professional Instructor
Certified LabVIEW Architect
LabVIEW Champion

"... after all, He's not a tame lion..."

For help with grief and grieving.
Message 2 of 9
(8,366 Views)
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
0 Kudos
Message 3 of 9
(8,366 Views)
Hi Greg!

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

Ben
Retired Senior Automation Systems Architect with Data Science Automation LabVIEW Champion Knight of NI and Prepper LinkedIn Profile YouTube Channel
0 Kudos
Message 4 of 9
(8,366 Views)
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
0 Kudos
Message 5 of 9
(8,367 Views)
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
Retired Senior Automation Systems Architect with Data Science Automation LabVIEW Champion Knight of NI and Prepper LinkedIn Profile YouTube Channel
0 Kudos
Message 6 of 9
(8,366 Views)
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...

Certified Professional Instructor
Certified LabVIEW Architect
LabVIEW Champion

"... after all, He's not a tame lion..."

For help with grief and grieving.
0 Kudos
Message 7 of 9
(8,366 Views)
you solved my problem, those pound signs were a killer to debug.
Thanx,
keep drawing code
lmd2
Lawrence M. David Jr.
Certified LabVIEW Architect
cell: 516.819.9711
http://www.aleconsultants.com
larry@aleconsultants.com
0 Kudos
Message 8 of 9
(8,366 Views)
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.
0 Kudos
Message 9 of 9
(8,029 Views)