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

[Laurie Nason]

I don't know as I haven't used access seriously for a long time, but isn't
Date a reserved word? Won't that screw things up? Also - is the SQL
statement expecting a ; at the end of it? Again I seem to remember access
throwing fits when I tried to type in my own SQL statements and forgot.

hth,
Laurie

-----Original Message-----
From: zope-admin@zope.org [mailto:zope-admin@zope.org]On Behalf Of Micah
Martin
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=189154113112099132172218072026249012177230057026
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 )