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

Innerjoin type mismatch error

  • July 7, 2010
  • 1 reply
  • 4253 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.

ilssac
ilssac
Inspiring
July 7, 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?

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