RE: [Zope] can't store a date in Access database
Whoops, this was a typo. The code is actually: insert into TableName (Date) values ("10/24/2001") I'm still looking for a solution to the problem. Micah From: "Capesius, Alan" <CapesiusA@S...> Date: Wed Oct 24, 2001 10:13 pm Subject: RE: [Zope] can't store a date in Access database "Insert into" or just "Insert" rather than "Insert in"
-----Original Message----- From: Micah Martin [mailto:micah@o...] Sent: Wednesday, October 24, 2001 4:47 PM To: 'zope@z...' Subject: [Zope] can't store a date in Access database
Hello,
This is the error the is troubling me:
Error Type: sql.error Error Value: ('37000', -3502, '[Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement.')
My SQL statement: insert in TableName (Date) values ("10/24/2001")
I have tried at least a dozen different date formats including the wierd '#' delimiters. Anyone solve this problem?
Micah
------------------------------------------------------------- --------------- -- Micah Martin Software Engineer Object Mentor, Inc. micah@o... www.objectmentor.com, www.xprogramming.com www.pairprogramming.com, www.junit.org
[Micah Martin]
Whoops, this was a typo. The code is actually:
insert into TableName (Date) values ("10/24/2001")
I'm still looking for a solution to the problem.
Is the name of your table really "TableName"? This seems unusual. You need the actual name of the table. You also probably need to supply the primary key or index column value as well as the date column - definitely if there is a primary key or an index (there ought to be one, but in Access you can get away without a primary key). Finally, you need to use single quotes instead of double quotes around the date value. Double and single quotes are not interchangable in SQL. So if your table is called "fact_table", and its primary key or index column is called "the_key" whose value should be 102 for this row, and if the date field were called "Date", then you would write insert into fact_table (the_key,Date) values (102,'10/24/2001') Access will accept this syntax for a dattime field, as I just verified by checking. If you mean to be changing an existing date value, you need to use an update statement instead. Cheers, Tom P
Is the name of your table really "TableName"? This seems unusual. You need the actual name of the table.
My guess is that TableName was a generic string that represents the table's name
You also probably need to supply the primary key or index column value as well as the date column - definitely if there is a primary key or an index (there ought to be one, but in Access you can get away without a primary key).
If he has it set up that the primary key is an AutoNumber, then you don't need to provde a value for the primary key.
Finally, you need to use single quotes instead of double quotes around the date value. Double and single quotes are not interchangable in SQL. So if your table is called "fact_table", and its primary key or index column is called "the_key" whose value should be 102 for this row, and if the date field were called "Date", then you would write
insert into fact_table (the_key,Date) values (102,'10/24/2001')
Access will accept this syntax for a dattime field, as I just verified by checking.
True.
If you mean to be changing an existing date value, you need to use an update statement instead.
True here too, but here is where you'll need a primary key value for your where clause. (Actually, you don't NEED it, but you will need to provide some field value for a where clause - unless you are updating every record in the table to have the same date. *Not likely*) *Whoa* Proud of myself that I didn't get on the *What are you using Access for* bandwagon! LOL. Tommy Innovation: The Best Way To Predict The Future ... Is To Create It.
participants (3)
-
Micah Martin -
Thomas B. Passin -
Tommy Johnson