[Zope] can't store a date in Access database

[Capesius, Alan]

As a point of reference, you can use reserved words if you enclosed them in
brackets [Date].
The one I run into most often is [Desc]

Looks like everyone was shortcuting their posts. I was testing with a
different field name and substituted "Date" back into my replies for
clarity.

Live and learn...


>>>-----Original Message-----
>>>From: Micah Martin [mailto:micah@objectmentor.com]
>>>Sent: Thursday, October 25, 2001 1:59 PM 
>>>To: 'zope@zope.org'
>>>Subject: Re: [Zope] can't store a date in Access database
>>>
>>>
>>>I appologize for my careless typos.  I should have copied 
>>>and pasted the
>>>code.  Here is the copied text:
>>>insert into TableName(Date) values ('10/25/2001')
>>>The quotes were in fact single quotes.  
>>>Yes the name of the table is really 'TableName', however 
>>>this was just
>>>created to help tackle my problem.  The real SQL statement 
>>>has 20 fields to
>>>fill.  I wanted to isolate the problem so I made a silly table named
>>>'TableName'.  
>>>You're right that I should not need a key for this test to work.  
>>>I am sure the SQL is correct because it works fine in Java.  
>>>Just not in
>>>Zope, it yeild the error:
>>>Error, sql.error: ('37000', -3502, '[Microsoft][ODBC Microsoft Access
>>>Driver] Syntax error in INSERT INTO statement.') 
>>>	
>>>	Micah
>>>	  
>>>From: "Thomas B. Passin" <tpassin@m...
>>></group/zope/post?protectID=189154113112099132172218072026249
>>>012177230057026
>>>209121182190>>
>>>Date: Thu Oct 25, 2001 4:29 pm
>>>Subject: Re: [Zope] can't store a date in Access database
>>>
>>>[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
>>>
>>>
>>>
>>>_______________________________________________
>>>Zope maillist  -  Zope@zope.org
>>>http://lists.zope.org/mailman/listinfo/zope
>>>**   No cross posts or HTML encoding!  **
>>>(Related lists - 
>>> http://lists.zope.org/mailman/listinfo/zope-announce
>>> http://lists.zope.org/mailman/listinfo/zope-dev )
>>>