Skip to main content
C
cfsetNewbie
Known Participant
July 7, 2010
Answered

Innerjoin type mismatch error

  • July 7, 2010
  • 1 reply
  • 4212 views

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.

This topic has been closed for replies.
Correct answer ilssac

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.

1 reply

ilssac
ilssacCorrect answer
Inspiring
July 7, 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.

C
cfsetNewbieAuthor
Known Participant
July 7, 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.

C
cfsetNewbieAuthor
Known Participant
July 7, 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.


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.

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices