ColdFusion Query Error: Value Can Not Be Converted To Requested Type
Posted August 4, 2006 at 2:40 PM
My friend Si was getting this error on one of our sites:
Error Executing Database Query. [Macromedia][SQLServer JDBC Driver]Value can not be converted to requested type.
This error goes hand in hand with the error I have blogged about before "Error Occurred While Processing Request N >= N" and is, in fact, caused by the same thing. The problem is inevitably SQL SELECT statements that use (*) such as:
Launch code in new window » Download code as text file »
- SELECT
- *
- FROM
- [table]
I am not 100% sure why this takes place, but here's my version: ColdFusion caches the SQL statement (or SQL does the caching). Then you go and you change the database structure such as adding a column. Then you run the query again. Since the actually SQL string didn't change, SQL thinks nothing has changed and tries to run the same query. However, since a column was added to the database, the cached structure not longer maps properly to the database structure, which is why it thinks it has to convert data types.
THIS IS WHY SELECT * IS NOOOO GOOD.
I can't remember off hand, but I think this is made even worse if you use CFQueryParam and SELECT *. If you spell out your query column instead of using (*), you are alllll good.
Download Code Snippet ZIP File
Post Comment | Ask Ben | Permalink | Other Searches | Print Page
Newer Post
SQL COALESCE() Like ISNULL() On Steroids ... With Caveats
Older Post
Adding Numbers To Date/Time Values
Reader Comments
I get this error occasionally and I use * in my select statements because the SQL server selects fields and does all the joins for me. I also use cfqueryparam and it seems to have a problem with it. if I remove the cfqueryparam and just put #Arguments.value# it works, but of course that's no good. your post helped me figure out what was going wrong. It seems like if I restart the coldfusion application service, the problem goes away and in a development environment, that's ok.
I know this is an old post, but I just wanted to say that this post has saved my life. i was tearing my hair out with this error
Always glad to help save some time (and hair) :)
To circumvent that CF caches select * queries our DBmanager came up with a clever trick.
Instead of writing "select * from tblcustomer", then type "select c.* from tblcustomer as c"
CF is stupid enough to not understand c.* so it dosen't cache it.
However SQL server is smart enough to understand that * and c.* is identical statements and therefore SQL server will cache it (caching it the way it should be cached i might add)
That way you dont mess up with SQL servers caching (which is a good thing)
@Johansen,
Cool tip. When I see people get this error, I try to advocate them writing out the explicit column requests, but this might prove to be the quicker fix.
Fantastic Tip - popped up in Google as link number 2 when searching for "Value Can Not Be Converted To Requested Type" and was exactly what what was wrong... I can't even begin to think how long it would have taken me to figure this out on my own. I really hate going into the lowdown nitty gritty of how CF, JDBC and MSSQL interact and as a fellow lazy programmer this tip solved my problem in 5min!
Respect!
Glad to have helped :)
Wow. Thank you very much. This post probably saved me 3 hours worth of debugging
I was getting this error when using cfqueryparam to pass a value to a stored proc; restarting the coldfusion application service solved my problem.
@Carole,
That will work since it will flush the cached database structure. Are you using SELECT * in your queries?
Thanks for blogging about this error Ben. It seems like there are all sorts of database caching issues caused by cfqueryparam.
I was only able to resolve this issue by a restart of the CF service after changing the db structure. It's interesting to note that I'm not using * in my SELECT query where CF throws an error, yet it still seems to cache the structure.
Great answer! I have one client using Fusebox so my error messages were obscure at best. I would have AN error after a db change, and figured it was some kind of caching. The only fix I could come up with was to restart ColdFusion service (not great, I know). So I got the same error on another app (no Fusebox) and could see the actual error message. I started to replace the * based on your recommendation, then had an idea. I'm still using ColdFusion studio, so I refreshed the database in the db tab and it fixed the error. Still, I agree that the * is not great.
Thanks!
The error just came back. I guess I was wrong about the CF Studio fix.
@Cheryl,
You're saying you're still getting the error on non-select-* queries?
No. I'm saying that I could swear the error went away when I refreshed the datasource through CFStudio. Then I posted my comment, then I went back and refreshed the page and the error returned. So I went in and corrected the query and the error went away for good. I really appreciate the insight on this.
@Cheryl,
Ah, ok. As long as it's gone now :)
Yup. Your post saved me time!
Everything was working fine then WHAT Happened??? Lucky for me I didn't change too much (except for that deleted column).
Narrowed down my problem to the CFQUERYPARAM usage and then googled the error.
Thank you!
@Stephanie,
Glad to help :)
Replacing the "select *" with the column names worked like a charm. Thanks!
@Sharon,
Awesome!
Three questions about this issue:
1. Does this error occur when you query a CF query object or only when you query a DB table?
2. I assume this error occurs when you query views as well as tables.
3. Is this problem solely the byproduct of a "Select *" (i.e., if you explicitly reference columns in the select statement, no problem).
The reason I ask about item 3 is that Craig McDonald said in his post above that he wasn't using "Select *" and was still experiencing this problem.
BTW, this is my first posting here but I want to join others in thanking Ben for hosting this great discussion, as well as others on his site.
@Mike,
The problem comes from the query caching that ColdFusion is performing. I assume that this happens if you hit a database table OR a view.
As far as I have personally experienced, as long as you are not using "select *", then you should not experience this error as ColdFusion will not try to execute a cached structure that does NOT match your given query.
Doing a select c.* totally fixed this issue for me! Thanks
The aliasing (e.g., "select c.* from tblcustomer as c") has also worked in my case.
While I don't like to do a "select *", I have one table with 150 fields (a related view of that table has even more) and I use all of them in one of my apps, so listing all the individual fields in the query would be a pain and really futz up the code.
Thanks again everyone.
@Roman, @Mike,
Interesting; I wonder why aliasing the table makes a difference.
@Mike,
150 columns! Bananas :)
@Mike,
I resisted the long select statements too, but finally I started using CFCs so it only has to be done once (if queries are written flexibly). <cfinvoke>s are nice and clean in your code. I'm glad I made the switch.
Using ColdFusion Builder, double clicking on the field name places it at the cursor. That combined with a ", c." on the clip board and you've got yourself a proper query in no-time. : )
