15 Replies Latest reply on Jul 29, 2007 8:47 AM by dempster

    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!!

        • 1. Re: ColdFusion & Image Uploading
          mama_karlo
          Can you attach the error message?
          • 2. ColdFusion & Image Uploading
            Rin- Level 1
            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  

            • 3. Re: ColdFusion & Image Uploading
              dempster Level 1
              I think "image" is a reserved word in the MS Jet SQL engine. Try changing the field name and see what happens.
              • 4. Re: ColdFusion & Image Uploading
                jdeline Level 1
                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.
                • 5. Re: ColdFusion & Image Uploading
                  dempster Level 1
                  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.
                  • 6. Re: ColdFusion & Image Uploading
                    JR "Bob" Dobbs-qSBHQ2 Level 3
                    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.
                    • 7. Re: ColdFusion & Image Uploading
                      jdeline Level 1
                      So "image" is not a reserved word in Access 2002, but is a reserved word in Access 2003. Which version are you using?
                      • 8. Re: ColdFusion & Image Uploading
                        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
                        • 9. Re: ColdFusion &amp; Image Uploading
                          cf_dev2 Level 1
                          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.

                          • 10. Re: ColdFusion &amp; Image Uploading
                            Amit 9808 Level 1
                            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
                            • 11. ColdFusion &amp; Image Uploading
                              cf_dev2 Level 1
                              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.




                              • 12. Re: ColdFusion &amp; Image Uploading
                                Dinghus Level 1
                                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.
                                • 13. Re: ColdFusion &amp; Image Uploading
                                  cf_dev2 Level 1
                                  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.
                                  • 14. Re: ColdFusion &amp; Image Uploading
                                    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.
                                    • 15. Re: ColdFusion &amp; Image Uploading
                                      dempster Level 1
                                      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.