ColdFusion & Image Uploading

[Rin-]

Hi everyone:

I'm trying to determine why Im getting an INSERT error when I run this ColdFusion page on my server. If anyone would be kind enough as to take a look at my code and show me what I've done wrong (if anything?) I would appreciate it. I am running this from a Microsoft Access Database named cfwtf, and the test table is defined. The test table consists of 2 columns, an autoIncrement Id, and a text field named 'image'. When ColdFusion tries to execute the attached code, I continually receive an INSERT error.

When looking at the error I see that it does have the value for image defined (e.g. when I upload an image named "sometestimage.jpg", the ColdFusion code spits it back to me in the error statement, and the file gets uploaded in the correct location). All I'm trying to understand is why it won't copy that file name into the database when everything else seems to be working correctly.

Thanks in advance!!

[Guest]

Can you attach the error message?

[Rin-]

The error message is as follows:

Error Executing Database Query.
Syntax error in INSERT INTO statement.


42 :
43 : VALUES (
44 : '#cffile.SERVERFILE#'
45 : )
46 :



--------------------------------------------------------------------------------

SQL INSERT INTO test ( image ) VALUES ( 'test.jpg' )
DATASOURCE cfwtf
VENDORERRORCODE 3092
SQLSTATE  

[dempster]

I think "image" is a reserved word in the MS Jet SQL engine. Try changing the field name and see what happens.

[Guest]

Image is not a reserved word in Access 2002 - see http://support.microsoft.com/kb/q286335/. Try pulling the CFIF statement out of the CFQUERY and making the assignment to a temporary variable. Use that variable in the VALUES clause.

[dempster]

Image is a reserved word according this documentation:
http://office.microsoft.com/en-us/access/HP010322491033.aspx

The CFIF isn't the problem since the error message that is returned shows the SQL statement:
INSERT INTO test ( image ) VALUES ( 'test.jpg' )

That 3092 error often indicates a SQL problem usually related to a bad or invalid field name.

[JR__Bob__Dobbs-qSBHQ2]

Try escaping the field name image with brackets and putting the filename value in a parameter in case the characters in the filename need to be escaped as well.

[Guest]

So "image" is not a reserved word in Access 2002, but is a reserved word in Access 2003. Which version are you using?

[Amit_9808]

Hi,

If the problem still exists then try this code

<cfset thisFile = Evaluate("cffile.SERVERFILE") />
<cfif Len(Trim(thisFile)) >
<cfset filename = #cffile.SERVERFILE#/>
<cfelse>
<cfset filename = " "/>
</cfif>

<cfquery datasource="cfwtf">
INSERT
INTO test
(image)
VALUES
(#filename#)
</cfquery>


Hope this will help you.

Cheers

Amit Rana

[cf_dev2]

quote:

---

Originally posted by: Amit 9808
<cfset thisFile = Evaluate("cffile.SERVERFILE") />
<cfif Len(Trim(thisFile)) >
<cfset filename = #cffile.SERVERFILE#/>
<cfelse>
<cfset filename = " "/>
</cfif>

---

I don't think Evaulate() is needed here. The error is most likely what dempster mentioned: image is a reserved word. They just need to check the form field and if its not empty perform the upload and insert.

[Amit_9808]

I agree with u that Evaluate() is not required here, the value of the cffile.serverfile can be assigned to a variable without using it.
But the thing which needs to concentrate is that i am using
<cfif Len(Trim(thisFile)) >
for checking the length of the value.
as i think its better to use this rather than using <cfif isDefined()>
because if you just click upload button without selecting any file ( In this case he is selecting the file but suppose) then sometimes you will get error. Just try it once.
Please correct me if i am wrong
thanks

[cf_dev2]

Agreed. IsDefined() won't work because the file field will always be defined.

I should have been more specific. When I said "check the form field and if its not empty perform the upload and insert" I meant use your len(trim(...)) check on the form field.

<!--- If a file was uploaded --->
<cfif len(trim(form.theFileField)) gt 0>
.... do the upload
</cfif>

Though its interesting to note that if you type anything in the file field (like 5 space characters) the #len(trim(form.theFileField))# value will be greater than 0. So it will still pass the CFIF test, but CF will throw an error when you try and upload with cffile.

[Dinghus]

Hey. How about the simple fact that "image" is not defined as a variable prior to that. stForm.image is what you want in there.

Even if image is not a reserved word, it is best NOT to use it as a variable name because it MIGHT cause problems. But I bet if you use the entire variable name here (stForm.image) it will work.

[cf_dev2]

quote:

---

Originally posted by: Dinghus
Hey. How about the simple fact that "image" is not defined as a variable prior to that. stForm.image is what you want in there.

---

No.

Look at the error message and query they posted. Its a SQL error. Since their code produced a valid sql statement the error has nothing to do with the stForm variable. The problem is that "Image" is a reserved word. They must either rename the column or use square brackets around it in their query:

INSERT INTO test ( [Image] ) VALUES ( 'test.jpg' )

I don't know what the purpose of stForm.Image is (or if its even needed). But any other issues with that variable are secondary. The primary problem is they've used a reserved word as a table column name.

[kkchan]

If you are using the MS Access driver with unicode support, try the MS Access driver without unicode support. I get the same error code in an insert statement in CFMX 6.1. Switching to the MS Access driver without unicode support resolved the issue for me.

Good luck.

[dempster]

It gets back to the issue of "image" being a reserved word. Switching drivers would work (the other Access driver isn't as strict with things like field lengths and reserved words) but it would be better practice to change the field name.