2 Replies Latest reply on Jul 6, 2010 12:35 PM by Irish-Phoenix

    cftransaction mismatch type error in query

    Irish-Phoenix

      Hello;

      I have a relational table (built in access) that I am trying to insert new records too. I can't figure out why I'm getting this error. I have tried a ton of different combimations of how to do this and it isn't working properly. Can anyone help me? This is my code:

       

      It's 2 query's in a ftransaction tag, in other areas of the site, it's an innerjoin query.


      This is the form field it comes from:
      <cfinput type="password" class="formSpamText" name="pass1" value="#form.pass1#" id="pass1">

       

      This is the cf-transaction. I am not puttin in all the error control stuff, but I did put in just the param for the code throwing the error:

       

      <cfparam name="FORM.fName" default="">
      <cfparam name="FORM.lName" default="">
      <cfparam name="FORM.clAdd" default="">
      <cfparam name="FORM.clCity" default="">
      <cfparam name="FORM.clState" default="">
      <cfparam name="FORM.clZip" default="">
      <cfparam name="FORM.clPhone" default="">
      <cfparam name="FORM.clEmail" default="">
      <cfparam name="FORM.pass1" default="">
      <cfparam name="FORM.f2" default="">

      <cfparam name="FORM.creditType" default="">
      <cfparam name="FORM.creditCard" default="">
      <cfparam name="FORM.securitCode" default="">
      <cfparam name="FORM.creditExpM" default="">
      <cfparam name="FORM.creditExpY" default="">

       

       

      <cftransaction action="begin">
      <cfquery datasource="#APPLICATION.dataSource#" dbtype="ODBC">
      INSERT INTO Lcustomers
      (c_fname, c_lname, c_street, c_city, c_state, c_zip, c_phone, c_email, c_password)
      VALUES (<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.fName#">,
              <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.lName#">,
              <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.clAdd#">,
              <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.clCity#">,
              <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.clState#">,
              <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.clZip#">,
              <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.clPhone#">,
              <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.clEmail#">,
              <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.pass1#">)
      </cfquery>

      <cfquery datasource="#APPLICATION.datasource#" name="getUpdate" dbtype="ODBC">
      SELECT MAX(c_ID) AS NewID
      FROM Lcustomers
      </cfquery>

      <cfquery datasource="#APPLICATION.dataSource#" dbtype="ODBC">
      INSERT INTO MerchandiseOrdersItems
      (c_ID, cc_type, cc_num, cc_verify, cc_expir_m, cc_expir_y)
      VALUES (<cfqueryparam value="#getUpdate.NewID#" cfsqltype="CF_SQL_VARCHAR">,
              <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.creditType#">,
              <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.creditCard#">,
              <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.securitCode#">,
              <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.creditExpM#">,
              <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.creditExpY#">)
      </cfquery>
      </cftransaction>

       

      then more code.. this is what the error is:

       

      Error Executing Database Query.

      [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.
      The error occurred in C:\Websites\187914kg3\accManage\signUp.cfm: line 202
      Called from C:\Websites\187914kg3\accManage\signUp.cfm: line 190
      Called from C:\Websites\187914kg3\accManage\signUp.cfm: line 189
      Called from C:\Websites\187914kg3\accManage\signUp.cfm: line 101
      Called from C:\Websites\187914kg3\accManage\signUp.cfm: line 1
      Called from C:\Websites\187914kg3\accManage\signUp.cfm: line 202
      Called from C:\Websites\187914kg3\accManage\signUp.cfm: line 190
      Called from C:\Websites\187914kg3\accManage\signUp.cfm: line 189
      Called from C:\Websites\187914kg3\accManage\signUp.cfm: line 101
      Called from C:\Websites\187914kg3\accManage\signUp.cfm: line 1
      200 :         <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.clPhone#">,
      201 :         <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.clEmail#">, 
      202 :         <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.pass1#">)
      203 : </cfquery>
      204 : 
      

      SQLSTATE  22018
      SQL   INSERT INTO Lcustomers (c_fname, c_lname, c_street, c_city, c_state, c_zip, c_phone, c_email, c_password) VALUES ( (param 1) , (param 2) , (param 3) , (param 4) , (param 5) , (param 6) , (param 7) , (param 8) , (param 9) )
      VENDORERRORCODE  -3030

       

       

      The table has the password section is set to TEXT - 250 char

       

      what is the problem here? am I missing something small?

       

      Thank you.

        • 1. Re: cftransaction mismatch type error in query
          Dan Bracuk Level 5

          First, re-arrange your insert queries so the commas are at the start of each line, not the end.  It makes the next step easier..

           

          Comment out the 2nd insert query for now.  Then, in the first insert query, comment out all the field names except the first one and all the values except the 1st one.  Run the page.  If it crashes, the first field is causing the problem.  It not, uncomment them one by one until it crashes.

           

          Once you have figured out the first query, fix the second one.  You are missing the 1st value.

          • 2. Re: cftransaction mismatch type error in query
            Irish-Phoenix Level 1

            I tried all that.. and all my variables are

            being passed properly, it's something in the query that's wrong. so I took JUST my query and it still throws the same error.

             

            so how about just looking at this:

             

            <cfquery datasource="#APPLICATION.dataSource#" dbtype="ODBC">
            INSERT INTO Lcustomers
            (c_fname, c_lname, c_street, c_city, c_state, c_zip, c_phone, c_email, c_password--->)
            VALUES (<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.fName#">
                    ,<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.lName#">
                    ,<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.c_street#">
                    ,<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.clCity#">
                    ,<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.clState#">
                    ,<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.clZip#">
                    ,<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.clPhone#">
                    ,<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.clEmail#">
                    ,<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.pass1#">)
            </cfquery>

             

            also, part of this has 2 fields in teh form that compair to each other to make sure you retyped your password properly. would that mess things up?

             

            <cfparam name="FORM.pass1" default="">
            <cfparam name="FORM.f2" default="">

             

             

            <cfset comparison = Compare(FORM.pass1, FORM.f2)>
            <cfif comparison eq 0>
            <cfparam name="FORM.pass1" default="">
            <cfelse>
            <cfset error = error & "<br><b>Your Passwords don't match, please try again!<b><br><br>">
            </cfif>

             

            Would this mess it up?