3 Replies Latest reply on Aug 4, 2007 6:35 AM by Dan Bracuk

    Upload and Insert

      Hi all. So here's my issue. I'm uploading an image and inserting it's name into the database. I got that working. But when I try to insert another item (in this case projID) with it, I get an error. Her'es the code and the error follows:

      <cfset CurrentPage=GetFileFromPath(GetTemplatePath())>
      <cfif IsDefined("FORM.MM_InsertRecord") AND FORM.MM_InsertRecord EQ "uploadDoc">
      <cffile action="upload" filefield="rfpResponse" destination="C:\Inetpub\wwwroot\BDcomm\documents\" nameconflict="overwrite" accept="image/*">
      <cfif isDefined("FILE.FileWasSaved")>
      <cfset FORM.rfpResponse = FILE.ServerFile></cfif>
      <cfquery datasource="bdcomm">
      INSERT INTO bddocuments (rfpResponse, projID) VALUES (
      <cfif IsDefined("FORM.rfpResponse") AND #FORM.rfpResponse# NEQ "">
      <cfif IsDefined("FORM.projID") AND #FORM.projID# NEQ "">
      <cflocation url="project_view.cfm">

      and here's the error
      Error Executing Database Query.
      You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 9

      In my database projID is an INTEGER. If I take out all projID references from the insert it works fine.
      Any help would be greatly appreciated!!!
        • 1. Re: Upload and Insert
          WestSide Level 1

          Usually when I have problems like this, I remove the conditional logic. I try not to use dynamic SQL like that as well, it does work so I am not saying dont do it, but I try to take care of the logic outside the sql statement if you get what I mean. Sometimes a comma is in the wrong spot, I would just get the straight sql working even in sql query analyzer, take your query and run it there and make sure it works. I would then do a <cfdump> of the variables you plan on using before you even run your <cfquery>

          That should lead you to the answer.



          • 2. Re: Upload and Insert
            cf_dev2 Level 1
            First dump the form scope to see what values are being passed. Is FORM.projID numeric? Then turn on debugging so CF will display the sql query sent to the database. What is the actual sql that was generated?
            • 3. Re: Upload and Insert
              Dan Bracuk Level 5
              In addition to what everyone else said, you realize that your logic makes it possible for empty rows to be inserted into your table, right. And you know that's bad, right?