Those nasty date problems 1

[twcman]

I have an insert that works just fine without the date &%$@* in it. I have scowered (sp?) this forum and have found many threads... no soap on any working form me. So, I will show you my code, then a copy of the error. BTW... my field in the access database is named date and the data type is date/time.

code:

<CFSET dateEntered = #CreateODBCDate(DateFormat(now(), &quot;yyyy-mm-dd&quot)#>
<cfquery name=&quot;insertdata&quot; datasource=&quot;#datasource#&quot; dbtype=&quot;ODBC&quot;>
INSERT INTO tbldonors (id,address,cctype,ccmonth,ccnumber,ccyear,city,comments country,donoramount,email,firstname,phonenumb,secondname,state,zip,date)
VALUES(#findmax.newid#,'#address#','#cctype#',#ccmonth#,'#ccnumber#',#ccyear#,'#city#', '#comments#','#country#',#donoramount#,'#email#','#firstname#','#phonenumb#','#secondname#','#state#','#zip#,'#dateEntered#')
</cfquery>


error:

ODBC Error Code = 37000 (Syntax error or access violation)


[Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement.


SQL = &quot;INSERT INTO tbldonors (id,address,cctype,ccmonth,ccnumber,ccyear,city,comments country,donoramount,email,firstname,phonenumb,secondname,state,zip,date) VALUES(2,'18347 emerald oaks','visa',8,'443433434343',2004,'san antonio', 'test','usa',100,'cscott@satx.rr.com','christopher','2104039344','mathews','texas','222222,'{d '2001-11-29'}')&quot;

 

[tleish]

If the field is set to a Date/Time then remove quotes on #dateEntered# in the query

=== START CODE EXAMPLE ===
<CFSET dateEntered = CreateODBCDate(DateFormat(now(), &quot;yyyy-mm-dd&quot;))>
<cfquery name=&quot;insertdata&quot; datasource=&quot;#datasource#&quot; dbtype=&quot;ODBC&quot;>
INSERT INTO tbldonors (id, address, cctype, ccmonth, ccnumber, ccyear, city, comments country, donoramount, email, firstname, phonenumb, secondname, state, zip, date)

VALUES(#findmax.newid#, '#address#', '#cctype#', #ccmonth#, '#ccnumber#', #ccyear#, '#city#', '#comments#', '#country#', #donoramount#, '#email#', '#firstname#', '#phonenumb#', '#secondname#', '#state#', '#zip#, #dateEntered#)
</cfquery>
=== END CODE EXAMPLE === - tleish

 

[twcman]

tleish,
thanks for your response. I tried removing the single quotes and recieved the same error.

 

[tleish]

Check the 1 single quote mark just before #zip#.

(code...) '#secondname#', '#state#', '#zip#, #dateEntered# - tleish

 

[twcman]

good catch but still no difference..

 

[tleish]

If you remove the date like you said earlier, does it work? - tleish

 

[twcman]

yes

 

[iamkillyou]

I would remove the cfset for dateEntered and just create the date in the database update query like this:

...'#state#','#zip#,#CreateODBCDateTime(Now())# ...


Also if your using access (in my expeirience) as your database moving the date to the next before last update helps to clear up your problem.

for the insert into part:

.... date, zip)

for the valuse part:

.... #CreateODBCDateTime(Now())#, '#zip#')

 

[twcman]

good suggestion but still with the error. can i change the data type or something. all i am going to do with the date is compare it in BETWEEN sql statements in queries.

cnsisa

 

[iamkillyou]

Your datatype is proabably wrong.

 

[twcman]

What should it be...? The only dumb questions are the ones that are never asked

 

[tleish]

It should be set to Date/Time... what is it set to? - tleish

 

[twcman]

it is set to date/time. I have resorted to setting the default value in the database date field and not inserting it using sql. would prefer the way we have been working but am up against a wall. The only dumb questions are the ones that are never asked

 

[iamkillyou]

With all of the changes you made was the sql error the same in each?

 

[iamkillyou]

If you look at your code and error message above there is not a comma between comments and country causing everything to be off by one. Maybe thats your problem.

 

[pl12987]

Oi vey! You still may have a syntax error to eliminate first. Check the commas, check the quote marks, check everything.

 

[twcman]

thanks, i found the missing comma but still getting errors. I am using the above solution and might pick this up another day. The only dumb questions are the ones that are never asked

 

[calista]

Don't use &quot;date&quot; as a field name in Access. Call it &quot;CurrentDate&quot;, &quot;DateEntered&quot;, or anything else but &quot;date&quot;. It is a reserved word and Access chokes on it. I had the same problem, and it took me awhile to figure that out. Calista :-X
Jedi Knight,
Champion of the Force

 

[twcman]

calista,
You da one. Changing the date field name to another name worked like a charm.

thanks. The only dumb questions are the ones that are never asked