Copy link to clipboard
Copied
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 ""> | ||||||
|
Can anyone see what's wrong?
Thank you.
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 likel
...Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
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?
Copy link to clipboard
Copied
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:
[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?
Copy link to clipboard
Copied
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)?
Copy link to clipboard
Copied
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
Copy link to clipboard
Copied
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...
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver]Numeric value out of range (null)
Copy link to clipboard
Copied
What is the value of form.creditExpY when the error occurs?
Copy link to clipboard
Copied
it loops through 10 yrs..
2010
2011
2012
the value is 2010 or what ever yr you choose between 2010 and 2020
Copy link to clipboard
Copied
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
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
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
Copy link to clipboard
Copied
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
Copy link to clipboard
Copied
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?
Copy link to clipboard
Copied
there are no limits. format is empty. field size is set as Long Integer
Copy link to clipboard
Copied
Allow Nulls?
Did you change the cfsqltype parameter on the <cfqueryparam...> it was varchar which is NOT a number.
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
this is the error, I also took out the queryperam and it still throws the 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 : '#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') |
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
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?
Copy link to clipboard
Copied
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.