INSERT INTO with CF and Access 1

[brian1313]

I'm getting a Syntax Error on the following statement...

<cfquery name=&quot;add_photo&quot; datasource=&quot;domicron&quot;>
INSERT INTO photos (id,photo_name,album_id,photo_date,caption,descrip,random,include) VALUES (#form.id#,'#form.photo_name#',#form.album_id#,#form.photo_date#,'#form.caption#','#form.descrip#',#form.random#,#form.include#)
</cfquery>

ID = AutoNumber
photo_name = Text
album_id = Number
photo_date = Date/Time
caption = Text
descrip = Memo
random = Yes/No
include = Yes/No

I'm using this variable to generate the date/time value:
<cfset form.photo_date = CreateDate(form.year, form.month, form.day)>

The form values all return fine when I call them in a <cfoutput> tag. Still getting the error though.

Any help would be sweet. Let me know if you guys need anymore info.

Thanks in advance.

-b-

 

[jalbao]

What exactly does the error say?

 

[brian1313]

Here's the error:

Error Diagnostic Information

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

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

The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (1:2928) to (1:2975).

Date/Time: 12/28/03 09:00:23
Browser: Mozilla/5.0 (Macintosh; U; PPC Mac OS X; en) AppleWebKit/103u (KHTML, like Gecko) Safari/100.1
Remote Address: 192.168.123.200
HTTP Referrer:

http://192.168.123.201/domicron/admin/gallery.cfm?action=add

Query String: action=addsave



Thanks again...

-b-

 

[jalbao]

Directly above your query, cfoutput the dynamic sql you are running - This will help you see what your sql looks like.

Example:

<cfoutput>
INSERT INTO photos (id,photo_name,album_id,photo_date,caption,descrip,random,include) VALUES (#form.id#,'#form.photo_name#',#form.album_id#,#form.photo_date#,'#form.caption#','#form.descrip#',#form.random#,#form.include#)
</cfoutput>

<cfabort>

<cfquery name=&quot;add_photo&quot; datasource=&quot;domicron&quot;>
INSERT INTO photos (id,photo_name,album_id,photo_date,caption,descrip,random,include) VALUES (#form.id#,'#form.photo_name#',#form.album_id#,#form.photo_date#,'#form.caption#','#form.descrip#',#form.random#,#form.include#)
</cfquery>


If the above tip doesn't help, show us the output of the dynamic sql that you generated.

On a side note - are you using any radio/select form types that may not be defined when you run your sql statement?

 

[brian1313]

Here's the generated SQL:

INSERT INTO photos (id,photo_name,album_id,photo_date,caption,descrip,random,include) VALUES (,'01.jpg',2,{ts '2000-01-01 00:00:00'},'none','none desc',1,1)

This has actually sovled my problem. Here are some questions I have.

The ID field is an autonumber, so I should NOT specify it upon inserting the new record, correct? I had received an error when I tried it without before but I think it was the same syntax error that I've been receiving.

I know that the radio buttons I was using had caused problems for me in the past. I actually converted my <form> to <cfform> and used the <cfinput> tag to specify radio buttons that way. No problems since.

Anyways, thanks for the help tooled. You get a gold star from me.

-b-

 

[brian1313]

Oh, I also had to pull the quotes from the Date/Time field but that was a really easy fix. I'm used to changing quotes around on those as I can never remember if they're sent as a string or not.

-b-

 

[jalbao]

&quot;The ID field is an autonumber, so I should NOT specify it upon inserting the new record, correct?&quot;

That's correct - I apologize for not catching that.

Regarding radio button issues -
Although the cfform/cfinput appears to be working for you, it's important to understand that the error handling is done on the client side - which is cool, but it aint 100% fail safe. Ideally, you will want to trap errors on the cfas too.

For example, before you run your insert you can check if a radio button is defined.

Something like:

<cfif ISDEFINED(&quot;form.myRadio&quot>
<cfset locMyRadio=1>
<cfelse>
<cfset locMyRadio=0>
</cfif>

Then you run your insert with the local variable:
<cfquery name=&quot;add_photo&quot; datasource=&quot;domicron&quot;>
INSERT INTO photos (photo_name,album_id,photo_date,caption,descrip,random,include) VALUES ('#form.photo_name#',#form.album_id#,#form.photo_date#,'#form.caption#','#form.descrip#',#form.random#,#locMyRadio#)
</cfquery>