I had a bitch of a problem this morning. I found that having moved hosts i couldn’t post long entries into my blog. I was getting a message back “Error Executing Database Query. Application uses a value of the wrong type for the current operation” - not too helpful.
I used a couple of UDFs from cflib to look at the datasources on the server and then retrieve the properties for my datasource - i found it was using the MS Access unicode driver whilst I’ve always used the standard MS Access driver when developing. When i changed the driver on my local machine I was able to duplicate the problem - which was a start!
The code i’m using is based on Ray’s blog app - the insert uses a cfqueryparam with type=”cf_sql_longvarchar” into a field of ‘memo’ type in MS Access. I found after a while that removing the cfqueryparam i could get data inserted - so the problem lay with the cfqueryparam tag.
It was suggested in Sean’s breeze room to try enabling CLOB/BLOB on the DSN but then i tried type=clob on the cfqueryparam and it worked! It turns out DWMX2004 only has 20 possible sql types in the cfqueryparam tag insight where if you look in the livedocs here you’ll see 22 types listed, including BLOB and CLOB. After changing the type to CLOB I tried the insertion again to see if it worked with the standard MS Access driver and it did - a few folks seem to think that a CLOB type is a better choice for the ‘memo’ field type in MS Access.
PS: CFeclipse has the correct types as defined in the livedocs 
PPS: I found other differences between the two drivers too, the unicode version has ‘action’ as a reserved SQL word, so if you use it as a column names it needs to be wrapped in [] brackets. Also Yes/No field types seem to require different code to be able to obtain correct results.
December 3rd, 2004 at 6:50 pm
prepare for the "why are you using access?" comments…
December 3rd, 2004 at 6:52 pm
i’ve had that today - but to be honest, I’ve been running my blog for almost a whole year on Access and i’ve never had any problems - yes I could use SQL/MySQL etc but i just don’t see the need.
December 3rd, 2004 at 8:49 pm
i believe you… someday you will find yourself cursing access for all it’s worth and then you will become an access-hater like everyone else. I use MSDE if it’s a desktop/one-off type of thing, MSSQL if the server/license exists, and MySql for everything else.
January 6th, 2005 at 9:11 am
John, I sympathize completely. The popular (and on paper at least, justifiable) prejudice against MS Access ignores one simple fact, that in purely performance terms it is perfectly capable of powering all but the most popular personal sites as well as (going out on a limb here…) the majority of commercial sites out there, especially given that only a small minority of commercial sites actually have a significant amount of traffic.
Personally, I’m not using Access (I traded in both Access and SQL Server for MySQL a while back), but the last company I worked for used it extensively to power the sites of their customers (who were mainly not-for-profit and governmental organisations, charities, and a few small businesses, admittedly). When I realised they were using Access I was astonished as like most people, I had been brought up on the conventional wisdom that Access is too feeble to serve as a Web database. Nevertheless, the company concerned have probably used it for at least fifteen or so projects over the last two to three years, most involving comprehensively featured CMSs and (until the time I left) had had no database problems, either technical or performance related, worth writing home about. Their customers love them, their sites go like rockets (admittedly, relatively low traffic and hosted on the company concerned’s own server), and they are very good at what they do. (Oh, they use ColdFusion for everything too…)
We all know that on paper Access lacks this, lacks that, has blah limitations, etc. etc. but the fact is that it can power the majority of Web sites out there and given that many shared hosting solutions offer Access connectivity with their most basic packages, it makes sense for a lot of people.
Personally, I wouldn’t use Access for even low-traffic commercial projects because, among other things, I don’t like the idea of having a built in scalability ceiling (even if the average site isn’t likely to hit that). But for a personal site project where cost is an issue and there is no requirement to learn pure RDBMS fundamentals, then why not? People give me condescending looks for even daring to suggest that Access is useable, but well over a dozen, smiling third sector organisations I can point to suggest otherwise.
My two (Euro) cents…
Roger
June 28th, 2005 at 3:14 am
John, thanks! This post really helped!!! Just to clarify for others out there who futzed around with the type=clob attribute, the full explication of that is:
cfsqltype="cf_sql_clob"
Now that I see figured it out it seems like a no brainer, but perhaps I can save someone else the extra 20 minutes it took me to figure that out.
Cheers,
Morgan
October 25th, 2005 at 12:49 pm
i am facing the same stupid problem in my app running with vb and oracle. it has happened overnight.
how do u get rid of it?
December 21st, 2005 at 10:42 pm
You’re a life saver! Thanks!
January 22nd, 2008 at 8:45 pm
I gotta say thanks! - This thread saved me some pain.
I’ve just moved a website to a new host - I was getting the "Application uses a value of the wrong type for the current operation" error on a custom CMS. I had no problem on other Hosts or dev environments before this… Evey thread I found said to shut off Unicode for Access and it would work - well I’m not the host and I thought the likelihood that I would be able to use that option slim. Sue enough Support Lvl 1 was sympathetic and tried a couple of times with 2nd lvl… 2nd Lvl said it was a scripting issue… at any rate it became apparent I had to look at doing it pro grammatically… Eventually I found this thread… I’ve never heard of SQL Injection until now nor have I ever used cfqueryparam. It’s my new best friend.
#Host#, you can keep Unicode on for all I care… even if it @#%^$#’s up memo fields that should allow 64,000 characters but only allow ~550
Here’s my Query - I had a hard time finding a SIMPLE example on using cfqueryparam.
<cfquery datasource="#appVar.DSN#">
UPDATE PageData
SET PageText = <cfqueryparam cfsqltype="CF_SQL_CLOB" value="#form.PageText#">
WHERE PageID = #form.PageID#
</cfquery>
And yes while I’m happy I got it fixed - I am thinking I’ll change up to MySQL and OR MSsql…