22 Replies Latest reply on Jul 7, 2010 1:41 PM by cfsetNewbie

    Innerjoin type mismatch error

    cfsetNewbie Level 1

      Hello;

      I'm trying to write an innerjoin query, and I get an error with a mismatched expression... I'm posting my query, maybe someone else can see where I'm going wrong here...

       

      <cfquery name="logMem" datasource="#APPLICATION.dataSource#">
      SELECT Lcustomers.c_ID, Lcustomers.c_fname, Lcustomers.c_lname, Lcustomers.c_street, Lcustomers.c_city, Lcustomers.c_state, Lcustomers.c_zip, Lcustomers.c_email, MerchandiseOrdersItems.cardID, MerchandiseOrdersItems.c_ID, MerchandiseOrdersItems.cc_type, MerchandiseOrdersItems.cc_num, MerchandiseOrdersItems.cc_verify, MerchandiseOrdersItems.cc_expir_m, MerchandiseOrdersItems.cc_expir_y
      FROM Lcustomers
      INNER JOIN MerchandiseOrdersItems
      ON MerchandiseOrdersItems.c_ID = LCustomers.c_ID
      WHERE Lcustomers.c_email = '#SESSION.uscl.c_email#'
      </cfquery>

       

      This is the error:

      Error Executing Database Query.

      [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Type mismatch in expression.
      The error occurred in C:\Websites\187914kg3\store\confirmorder.cfm: line 52
      50 : INNER JOIN MerchandiseOrdersItems
      51 : ON MerchandiseOrdersItems.c_ID = LCustomers.c_ID
      52 : WHERE Lcustomers.c_email = '#SESSION.uscl.c_email#'
      53 : </cfquery>
      54 : <cfif logMem.cc_type EQ "">
      

      SQLSTATE  HY000
      SQL   SELECT Lcustomers.c_ID, Lcustomers.c_fname, Lcustomers.c_lname, Lcustomers.c_street, Lcustomers.c_city, Lcustomers.c_state, Lcustomers.c_zip, Lcustomers.c_email, MerchandiseOrdersItems.cardID, MerchandiseOrdersItems.c_ID, MerchandiseOrdersItems.cc_type, MerchandiseOrdersItems.cc_num, MerchandiseOrdersItems.cc_verify, MerchandiseOrdersItems.cc_expir_m, MerchandiseOrdersItems.cc_expir_y FROM Lcustomers INNER JOIN MerchandiseOrdersItems ON MerchandiseOrdersItems.c_ID = LCustomers.c_ID WHERE Lcustomers.c_email = 'mjc@phoenixdesignstudio.com'
      VENDORERRORCODE  -3079

       

       

      Can anyone see what's wrong?
      Thank you.

        • 1. Re: Innerjoin type mismatch error
          ilssac Level 5

          cfsetNewbie wrote:

           

          Can anyone see what's wrong?

           

          Not really, because you are not showing us where the problem would be.  This is your database complaining so you need to look in the database for the problem.  Luckily the error is fairly straight forward.  Type mismatch.  That means you are comparing two things in the database and the database is telling you that the types are different and that it can not compare them.  Since the only comparison I see in your SQL is in the ON clause, the most likely scenario is that the c_ID field in the MerchandiscOrdersItems table is one type and the c_ID field in the LCustomers table is another.

           

          How you fix that depends on whether you want and|or can change the database design OR if you just want to force the issue in the SQL.  For the former, change the type of one table to be compatible with the other table.  For the latter, use the database cast() function (whatever your database calls that function) to change the type of one side of the ON clause to match other.

           

          If it is not the ON clause, then investigate the rest of your SQL looking for other places where the data type makes a difference.

          • 2. Re: Innerjoin type mismatch error
            cfsetNewbie Level 1

            they are different, one is an autonumber and the other is a number. It's an access database

            .. basically, the MerchandiseOrdersItems.c_ID = LCustomers.c_ID

            is how the 2 tables innerjoin, when it's written, one table is member info, and the other credit

            , the member info puts it's ID (autonumber) into the MerchandiseOrdersItems.c_ID putting it to the members account. when they are logged in, the session will have the email address as it's variable and bring up all the records for this member.

             

            That is how I have the isnsert set up, now, I am trying to bring up the record for the logged in member.
            So how would I rewrite this to work for this type of function? or is it just a simple change?

             

            I know.. access.. ugg.

            • 3. Re: Innerjoin type mismatch error
              ilssac Level 5

              Well *I* would expect an autonumber field to match a number field, but it has been a decade since I have used Access in a web application.

               

              Can you right the equivalent query in the database and compare the SQL there to your SQL and see how they might differ?

               

              The only other comparison I see in your SQL is Lcustomers.c_email = 'mjc@phoenixdesignstudio.com'.  This would only cause a problem if the Lcustomers.c_email field is not some type of text field?

              • 4. Re: Innerjoin type mismatch error
                cfsetNewbie Level 1

                You were right, I found my cell wasn't set properly and the DB was crying about it...

                 

                I have on eother issue my DB doesn't like. It's an error where I'm trying to add the year to a cell, here is how it's set in the Database, and the

                part of teh query that is throwing it... and the error

                 

                the DB cell is set as number (long interger)

                 

                this is the code:

                <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#form.creditExpY#">

                 

                 

                this is my error:

                Error Executing Database Query.

                [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver]Numeric value out of range (null)   The error occurred in C:\Websites\187914kg3\accManage\signUp.cfm: line 226
                Called from C:\Websites\187914kg3\accManage\signUp.cfm: line 198
                Called from C:\Websites\187914kg3\accManage\signUp.cfm: line 196
                Called from C:\Websites\187914kg3\accManage\signUp.cfm: line 4
                Called from C:\Websites\187914kg3\accManage\signUp.cfm: line 1
                Called from C:\Websites\187914kg3\accManage\signUp.cfm: line 226
                Called from C:\Websites\187914kg3\accManage\signUp.cfm: line 198
                Called from C:\Websites\187914kg3\accManage\signUp.cfm: line 196
                Called from C:\Websites\187914kg3\accManage\signUp.cfm: line 4
                Called from C:\Websites\187914kg3\accManage\signUp.cfm: line 1

                224 :         <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#form.securitCode#">,
                225 :         <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#form.creditExpM#">,
                226 :         <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#form.creditExpY#">)
                227 : </cfquery>
                228 : 
                

                 

                 


                SQLSTATE  22003
                SQL   INSERT INTO MerchandiseOrdersItems (c_ID, cc_type, cc_num, cc_verify, cc_expir_m, cc_expir_y) VALUES ( (param 1) , (param 2) , (param 3) , (param 4) , (param 5) , (param 6) )
                VENDORERRORCODE  3

                 

                 

                I can't figure out why I tried a number of different CF_SQL types and they aren't working. Any ideas?

                • 5. Re: Innerjoin type mismatch error
                  JR "Bob" Dobbs Level 4

                  What is the data type of the cc_expir_y field in the database?  If integer why are you using CF_SQL_VARCHAR?

                   

                  What is the value of form.creditExpY (including any whitespace)?

                   


                  • 6. Re: Innerjoin type mismatch error
                    JR "Bob" Dobbs Level 4

                    I suspect that the value of form.creditExpY is outside the possible range that Access accepts as an integer value.  This could explain the error "Numeric value out of range".

                     


                    See this link for information on max field sizes in Access:
                    http://office.microsoft.com/en-us/access-help/set-the-field-size-HA010341996.aspx

                    • 7. Re: Innerjoin type mismatch error
                      cfsetNewbie Level 1

                      this is the form field, it's a select box:

                       

                      <cfselect enabled="No" name="creditExpY" multiple="no" selected="#form.creditExpY#">
                      <cfloop from="#year(now())#" to="#val(year(now())+10)#" index="x">
                      <option value="#x#">#x#</option>
                      </cfloop>
                      </cfselect>

                       

                      the table cell is set as number (long integer)

                       

                      as it sits now, I get the same error...

                      Error Executing Database Query.

                      [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver]Numeric value out of range (null)

                      • 8. Re: Innerjoin type mismatch error
                        JR "Bob" Dobbs Level 4

                        What is the value of form.creditExpY when the error occurs?

                        • 9. Re: Innerjoin type mismatch error
                          cfsetNewbie Level 1

                          it loops through 10 yrs..

                          2010

                          2011

                          2012

                           

                           

                          the value is 2010 or what ever yr you choose between 2010 and 2020

                          • 10. Re: Innerjoin type mismatch error
                            JR "Bob" Dobbs Level 4

                            Things to check:

                             

                            1. Use CFDUMP to verify that the form fields contain the values you except.

                             

                            2. Are there CFOUTPUT tags around your CFSELECT?  Without CFOUTPUTs the value of form.creditExpY will be  the string literal "#x#" and not a number.

                            • 11. Re: Innerjoin type mismatch error
                              cfsetNewbie Level 1

                              lol.. I did both of those, it is passing the proper value, it's the database not liking the fact I

                              'm trying to put in this number for some reason.. when you look at the page source code, it even shows the proper values and display

                              .

                               

                              So for some reason, my database doesn't like this number. I'm stumped on this one.

                              • 12. Re: Innerjoin type mismatch error
                                Adam Cameron. Level 5

                                cfsetNewbie wrote:

                                 

                                it loops through 10 yrs..

                                2010

                                2011

                                2012

                                 

                                 

                                the value is 2010 or what ever yr you choose between 2010 and 2020

                                 

                                Sure: that's what the value is supposed to be.  But is it?

                                 

                                Do a <cfdump var="#form#"> immediately before the insert query and check all the values you are passing to the DB driver are valid for the data types.

                                 

                                What is the create-table DDL statement for the table in question?  (Can Access generate one of those?  Dunno).

                                 

                                 

                                Now for the usual "don't use Access" plea: if you are just starting out on this project, is it too late to not use Access?  It's just a desktop app, and it's not intended to be used as a server-based DB solution.  It is not fit-for-purpose for server-side usage.

                                 

                                --

                                Adam

                                • 13. Re: Innerjoin type mismatch error
                                  Reed Powell Level 3

                                  What happens if you write a new page that just tries to do a CFQUERY for an INSERT statement with the data (including the out of range number) entered literally?  That will help to isolate the cause and determine for sure whether this is an Access problem with processing the CFQUERY or a problem with how your code is delivering the data to the CFQUERY.  If the stand-alone code works, then the next step would be to dump out all of the data just prior to the CFQUERY.  If the data still looks good at that point, then try removing the CFQUERYPARAMs to see if they are munging the data before it gets sent to Access.  Also, remember that Access is much less forgiving that MS-SQL in terms of automatic datatype conversion.

                                  -reed

                                  • 14. Re: Innerjoin type mismatch error
                                    Adam Cameron. Level 5

                                    What are the param values for all the params you're passing?

                                     

                                    What happens if you use those values to directly insert the data into the DB using MSAccess.exe?

                                     

                                    --

                                    Adam

                                    • 15. Re: Innerjoin type mismatch error
                                      ilssac Level 5

                                      You have, of course, checked the database configuration to make sure it does not have any limits defined on the field that would prevent these values from being inserted?

                                      • 16. Re: Innerjoin type mismatch error
                                        cfsetNewbie Level 1

                                        there are no limits. format is empty. field size is set as Long Integer

                                        • 17. Re: Innerjoin type mismatch error
                                          ilssac Level 5

                                          Allow Nulls?

                                           

                                          Did you change the cfsqltype parameter on the <cfqueryparam...> it was varchar which is NOT a number.

                                          • 18. Re: Innerjoin type mismatch error
                                            ilssac Level 5

                                            P.S.  Are you getting the generate sql as part of the error message.  It should be available.  It can often be informative to see what CF is actually sending to the databae as the SQL statement.

                                            • 19. Re: Innerjoin type mismatch error
                                              cfsetNewbie Level 1

                                              this is the error, I also took out the queryperam and it still throws the error

                                               

                                              Error Executing Database Query.

                                              [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver]Numeric value out of range (null)   The error occurred in C:\Websites\187914kg3\accManage\signUp.cfm: line 230
                                              Called from C:\Websites\187914kg3\accManage\signUp.cfm: line 202
                                              Called from C:\Websites\187914kg3\accManage\signUp.cfm: line 200
                                              Called from C:\Websites\187914kg3\accManage\signUp.cfm: line 4
                                              Called from C:\Websites\187914kg3\accManage\signUp.cfm: line 1
                                              Called from C:\Websites\187914kg3\accManage\signUp.cfm: line 230
                                              Called from C:\Websites\187914kg3\accManage\signUp.cfm: line 202
                                              Called from C:\Websites\187914kg3\accManage\signUp.cfm: line 200
                                              Called from C:\Websites\187914kg3\accManage\signUp.cfm: line 4
                                              Called from C:\Websites\187914kg3\accManage\signUp.cfm: line 1

                                              228 :         <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#form.securitCode#">,
                                              229 :         <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#form.creditExpM#">,
                                              230 :         '#form.creditExpY#')
                                              231 : </cfquery>
                                              232 : 
                                              

                                               


                                              SQLSTATE  22003
                                              SQL   INSERT INTO MerchandiseOrdersItems (c_ID, cc_type, cc_num, cc_verify, cc_expir_m, cc_expir_y) VALUES ( (param 1) , (param 2) , (param 3) , (param 4) , (param 5) , '2013')

                                              • 20. Re: Innerjoin type mismatch error
                                                ilssac Level 5
                                                INSERT INTO MerchandiseOrdersItems (c_ID, cc_type, cc_num,  cc_verify, cc_expir_m, cc_expir_y) 
                                                VALUES ( (param 1) , (param 2) ,  (param 3) , (param 4) , (param 5) , '2013') 

                                                 

                                                See the single quotes around the value 2013?  That means that the database is seeing "2013" as a text value, it is not going to allow that into a number field.  Just because the text is a series of numerical digits, does not matter.  Database management systems are very particular.

                                                 

                                                Remove the quotes, or better yet put back the <cfqueryparam...> with the correct cfsqltype value, and I bet things will work better.

                                                • 21. Re: Innerjoin type mismatch error
                                                  ilssac Level 5
                                                  225 :         <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#form.creditExpM#">,

                                                  This one works

                                                   

                                                  226 :         <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#form.creditExpY#">)

                                                  This one doesn't

                                                   

                                                  What is the difference between the two?

                                                  • 22. Re: Innerjoin type mismatch error
                                                    cfsetNewbie Level 1

                                                    the first one puts in just a number 1 - 12 (months)

                                                    the other puts in 2010 or what ever yr it needs.

                                                     

                                                    I changed my field to text and now my sql isn't running.. still.

                                                     

                                                    I used this one.. cfsqltype="CF_SQL_LONGVARCHAR"

                                                    and varchar both give me this error:

                                                    [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver]Numeric value out of range (null)   The error occurred in C:\Websites\187914kg3\accManage\signUp.cfm: line 230
                                                    Called from C:\Websites\187914kg3\accManage\signUp.cfm: line 202
                                                    Called from C:\Websites\187914kg3\accManage\signUp.cfm: line 200
                                                    Called from C:\Websites\187914kg3\accManage\signUp.cfm: line 4
                                                    Called from C:\Websites\187914kg3\accManage\signUp.cfm: line 1
                                                    Called from C:\Websites\187914kg3\accManage\signUp.cfm: line 230
                                                    Called from C:\Websites\187914kg3\accManage\signUp.cfm: line 202
                                                    Called from C:\Websites\187914kg3\accManage\signUp.cfm: line 200
                                                    Called from C:\Websites\187914kg3\accManage\signUp.cfm: line 4
                                                    Called from C:\Websites\187914kg3\accManage\signUp.cfm: line 1

                                                    228 :         <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#form.securitCode#">,
                                                    229 :         <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#form.creditExpM#">,
                                                    230 :         <cfqueryparam cfsqltype="cf_sql_varchar" value="#FORM.creditExpY#">)
                                                    231 : </cfquery>
                                                    232 : 
                                                    

                                                     

                                                     

                                                     

                                                    is there a better way to write a cfselect loop like this? maybe the database doesn't like it? I dumped the whole form as well, and it is passing everything the way I need it too. So what's the problem? I don't get it for something this simple.