date format when insert

[mickeyg]

I am getting an "Error Diagnostic Information
ODBC Error Code = 37000 (Syntax error or access violation)
" error when I attempt to assign a value to a date field.
Everything works correctly if I take the date field out.

See code:
<cfset MyDate = #Form.Date#>
<!--- insert a new record into the Recreation table --->
<cfquery name=&quot;RecreationAdd&quot; datasource=&quot;sportsmen&quot; dbtype=&quot;ODBC&quot;>
INSERT INTO tblRecreationResults (Angler,SurveyType,Location,County,Date,LakeID,WaterClarity,Hours,VegetationGrowth,BassHealth,SuccessLevel,SkyCondition,WeatherCondition,Comments,LMFishLength1,LMFishLength2,LMFishLength3,LMFishLength4,LMFishLength5,SMFishLength1,SMFishLength2,SMFishLength3,SMFishLength4,SMFishLength5)
VALUES ('#Form.Angler#','#Form.Type#','#Form.Bodyofwater#','#Form.County#','#DateFormat(MyDate,&quot;dd/mm/yyyy&quot#','#Form.LakeID#','#Form.Waterclarity#','#Form.Hours#','#Form.Vegetation#','#Form.Basshealth#','#Form.Success#','#Form.Sky#','#Form.Weather#','#Form.Comments#','#Form.Lm1#','#Form.Lm2#','#Form.Lm3#','#Form.Lm4#','#Form.Lm5#','#Form.Sm1#','#Form.Sm2#','#Form.Sm3#','#Form.Sm4#','#Form.Sm5#')
</cfquery>

Thanks,
Mickey

 

[r937]

what database?

try ansi standard format, it never fails: yyyy-mm-dd


rudy

 

[mickeyg]

Access database

ODBC Error Code = 37000 (Syntax error or access violation)
[Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement.

 

[ecobb]

Try using this for the date value in your insert statement:

'#CreateODBCDateTime('#MyDate#')#'

instead of:

#DateFormat(MyDate,&quot;dd/mm/yyyy&quot#'
--although shouldn't that be #DateFormat('#MyDate#', 'dd/mm/yyy')# ?

 

[mickeyg]

UGH...I figured it out. I had to change &quot;both&quot; my form and field names to something other than &quot;date&quot;. I did not think ColdFusion would conflict with a database field name of date, but I guess it does!