Innerjoin type mismatch error

Report · Jul 07, 2010

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.

Report · Jul 07, 2010

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.

Report · Jul 07, 2010

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.

Report · Jul 07, 2010

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?

Report · Jul 07, 2010

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:

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?

Report · Jul 07, 2010

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)?

Report · Jul 07, 2010

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

Report · Jul 07, 2010

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

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)

Report · Jul 07, 2010

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

Report · Jul 07, 2010

it loops through 10 yrs..

2010

2011

2012

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

Report · Jul 07, 2010

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

Report · Jul 07, 2010

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.

Report · Jul 07, 2010

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.

Report · Jul 07, 2010

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

Report · Jul 07, 2010

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

Report · Jul 07, 2010

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?

Report · Jul 07, 2010

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

Report · Jul 07, 2010

Allow Nulls?

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

Report · Jul 07, 2010

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.

Report · Jul 07, 2010

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')

Report · Jul 07, 2010

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.

Report · Jul 07, 2010

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?

Report · Jul 07, 2010

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.

Adobe Community

Innerjoin type mismatch error

Error Executing Database Query.

1 Correct answer

Error Executing Database Query.

Error Executing Database Query.

Error Executing Database Query.