8 Replies Latest reply on Jun 5, 2008 1:53 PM by Newsgroup_User

    Error executing insert to data source, please help

    CFmonger Level 1
      Hello;
      I am making a small security app for a log in feature. I have everything working, but when I try to post to the DB and leave a form field blank, it throws an error. I am placing my query code and then the 2 errors I am getting:

      Query:
      <cfquery datasource="#APPLICATION.dataSource#">
      INSERT INTO Bliplist
      (reject, HTTP_USER_AGENT)
      VALUES
      (<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.rejip#">,
      <cfqueryparam cfsqltype="cf_sql_longvarchar" value="#form.browser#">)
      </cfquery>
      <cflocation url="blist.cfm">

      Error:
      Error Executing Database Query.
      [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Field 'Bliplist.HTTP_USER_AGENT' cannot be a zero-length string.

      The error occurred in C:\Websites\4npp8b\admin\trac\bl-action.cfm: line 28

      26 : (<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.rejip#">,
      27 : <!--- <cfif HTTP_USER_AGENT is true> --->
      28 : <cfqueryparam cfsqltype="cf_sql_longvarchar" value="#form.browser#">)
      29 : <!--- </cfif> --->
      30 : </cfquery>



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

      SQLSTATE HY000
      SQL INSERT INTO Bliplist (reject, HTTP_USER_AGENT) VALUES ( (param 1) , (param 2) )
      VENDORERRORCODE -3702

      So I added the code to check if the field was true, and I get this error: (here is the cfif and error:

      <cfif HTTP_USER_AGENT is true>
      <cfqueryparam cfsqltype="cf_sql_longvarchar" value="#form.browser#">)
      </cfif>

      Error message:

      Error Executing Database Query.
      [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement.

      The error occurred in C:\Websites\4npp8b\admin\trac\bl-action.cfm: line 28

      26 : (<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.rejip#">,
      27 : <cfif HTTP_USER_AGENT is true>
      28 : <cfqueryparam cfsqltype="cf_sql_longvarchar" value="#form.browser#">)
      29 : </cfif>
      30 : </cfquery>



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

      SQLSTATE 42000
      SQL INSERT INTO Bliplist (reject, HTTP_USER_AGENT) VALUES ( (param 1) ,
      VENDORERRORCODE -3502

      What am I doing wrong, and how do I fix it? I also have an update query on this and that throws the same error is there is a blank form field, Kind of lost here. How do I allow 0 length?

      Thank you.

      CFmonger

        • 1. Re: Error executing insert to data source, please help
          jdeline Level 1
          Under some circumstances, HTTP_USER_AGENT will not exist. What you want to do is <CFIF IsDefined("HTTP_USER_AGENT")>.
          • 2. Re: Error executing insert to data source, please help
            Level 7
            jdeline wrote:
            > Under some circumstances, HTTP_USER_AGENT will not exist. What you want to do is <CFIF IsDefined("HTTP_USER_AGENT")>.

            This may not work. There is a little known gotcha with CGI variables.
            IIRC Do to their flighty nature, ColdFusion will always return true to
            a IsDefined() test for any value.

            • 3. Re: Error executing insert to data source, please help
              CFmonger Level 1
              I added the cfif isDefined on both the update record query and the add a new record.

              I get this error when adding a new record:
              Error Executing Database Query.
              [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression '. Pa_RaM001'.

              The error occurred in C:\Websites\4npp8b\admin\trac\bl-action.cfm: line 28

              26 : (<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.rejip#">,
              27 : <CFIF IsDefined("HTTP_USER_AGENT")>.
              28 : <cfqueryparam cfsqltype="cf_sql_longvarchar" value="#form.browser#">)
              29 : </cfif>
              30 : </cfquery>



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

              SQLSTATE 42000
              SQL INSERT INTO Bliplist (reject, HTTP_USER_AGENT) VALUES ( (param 1) , . (param 2) )
              VENDORERRORCODE -3100

              And this error when updating an existing record:

              Error Executing Database Query.
              [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement.

              The error occurred in C:\Websites\4npp8b\admin\trac\bl-action.cfm: line 39

              37 : <CFIF IsDefined("HTTP_USER_AGENT")>.
              38 : Bliplist.HTTP_USER_AGENT=<cfqueryparam cfsqltype="cf_sql_longvarchar" value="#form.browser#"></CFIF>
              39 : WHERE ID = <cfqueryparam value="#form.ID#" cfsqlType="CF_SQL_INTEGER">
              40 : </cfquery>
              41 : <cflocation url="blist.cfm">



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

              SQLSTATE 42000
              SQL UPDATE Bliplist SET Bliplist.reject= (param 1) , . Bliplist.HTTP_USER_AGENT= (param 2) WHERE ID = (param 3)
              VENDORERRORCODE -3503

              Here is the code I added:

              <cfif form.id eq 0>
              <cfquery datasource="#APPLICATION.dataSource#">
              INSERT INTO Bliplist
              (reject, HTTP_USER_AGENT)
              VALUES
              (<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.rejip#">,
              <CFIF IsDefined("HTTP_USER_AGENT")>.
              <cfqueryparam cfsqltype="cf_sql_longvarchar" value="#form.browser#">)
              </cfif>
              </cfquery>
              <cflocation url="blist.cfm">
              <cfelse>
              <cfquery datasource="#APPLICATION.dataSource#">
              UPDATE Bliplist
              SET
              Bliplist.reject=<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.rejip#">,
              <CFIF IsDefined("HTTP_USER_AGENT")>.
              Bliplist.HTTP_USER_AGENT=<cfqueryparam cfsqltype="cf_sql_longvarchar" value="#form.browser#"></CFIF>
              WHERE ID = <cfqueryparam value="#form.ID#" cfsqlType="CF_SQL_INTEGER">
              </cfquery>
              <cflocation url="blist.cfm">
              </cfif>

              I think the update cfif is being used wrong. Is the insert just missing pound signs? ##
              • 4. Re: Error executing insert to data source, please help
                Dan Bracuk Level 5
                It appears that you are trying to do this.

                insert into sometable
                (f1, f2)
                values
                (v1, <cfif something> v2 </cfif>)

                Do you see anything obviously wrong with this?
                • 5. Re: Error executing insert to data source, please help
                  CFmonger Level 1
                  Then how would I insert something into my tables, but allow it to have 0 lenth? So if a form field doesn't have anyhting in it, it won't error out.

                  Not to be rude, and I appreciate the help, BUT, why talk in riddles if your going to respond to a question? If there is something wrong, then state what it is. Again, not trying to be rude, but if your going to help, then help... not riddle about it.

                  I changed the way the cfif was written and it didn't like that either. I wasn't looking if the db table was defined, I was looking if the form field was defined to then add it to the DB, if it isn't in the form field, then not to try.

                  I wrote this in the insert into:

                  <CFIF IsDefined("#form.browser#")>.
                  <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.browser#">)
                  </cfif>

                  and then in the update record this:

                  <CFIF IsDefined("#form.browser#")>.
                  Bliplist.HTTP_USER_AGENT=<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.browser#">
                  </CFIF>

                  Now it throws this error:

                  Error Executing Database Query.
                  [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement.

                  The error occurred in C:\Websites\4npp8b\admin\trac\bl-action.cfm: line 39

                  37 : <CFIF IsDefined("#form.browser#")>.
                  38 : Bliplist.HTTP_USER_AGENT=<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.browser#"></CFIF>
                  39 : WHERE Bliplist.ID = <cfqueryparam value="#form.ID#" cfsqlType="CF_SQL_INTEGER">
                  40 : </cfquery>
                  41 : <cflocation url="blist.cfm">



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

                  SQLSTATE 42000
                  SQL UPDATE Bliplist SET Bliplist.reject= (param 1) , WHERE Bliplist.ID = (param 2)
                  VENDORERRORCODE -3503

                  So what is the proper way to allow 0 length??
                  • 6. Re: Error executing insert to data source, please help
                    Level 7
                    CFmonger wrote:
                    >
                    > So what is the proper way to allow 0 length??
                    >


                    Insert a zero length string ''. You are not allowing for a zero length,
                    you are truncating your SQL code in such a manner that it is illegal
                    under the conditions for which you are testing.

                    The fix that would require the least change to your code would to add
                    and else clause to your if statement so to allow for the the desired
                    value to be placed into the SQL code.

                    A better fix would to use features of CFML and/or your database that
                    allow for this. I.E. the NULL parameter of a <cfqueryparam...> tag or
                    default values in your database design.

                    Or you can modify your logic so that both the field name and field value
                    are truncated from your SQL code so that is is still legal under this case.
                    • 7. Re: Error executing insert to data source, please help
                      CFmonger Level 1
                      I thought I had this fixed, but it is back to errors again. Let me get this right.. if I set my db table, for allow zero length is should fix my problem? then I wouldn't need a cfif to tell if the form field wasn't filled, it wold still be ok to post to the db? Correct?
                      • 8. Re: Error executing insert to data source, please help
                        Level 7
                        CFmonger wrote:
                        > I thought I had this fixed, but it is back to errors again. Let me get this
                        > right.. if I set my db table, for allow zero length is should fix my problem?
                        > then I wouldn't need a cfif to tell if the form field wasn't filled, it wold
                        > still be ok to post to the db? Correct?
                        >

                        Not completely. If you set your database to use a default, apparently
                        an empty string in this case, you would not need to provide this in your
                        code.

                        You would still need to structure you CFML so that the SQL it builds is
                        legal for all cases.

                        I.E.
                        INSERT INTO aTable
                        (aField, bField)
                        VALUES
                        (aValue, <cfif ...>bValue></cfif>)

                        This will produce the following two SQL statements depending on the
                        evaluation of the if clause.

                        IF TRUE:
                        INSERT INTO aTable
                        (aField, bField)
                        VALUES
                        (aValue, bValue)

                        This is a normal and complete SQL statement and will properly execute.

                        IF FALSE:
                        INSERT INTO aTable
                        (aField, bField)
                        VALUES
                        (aValue, )

                        This is not proper. It says there will be two field requiring two
                        values, but there is only one value and a trailing comma with nothing
                        following. Databases are never going to accept this improper SQL. This
                        is what you need to work on. I gave you two ways to handle this in my
                        previous message.




                        The teacher in me now leaves you to give it your best shot to put this
                        all together.