ColdFusion Query Error: Value Can Not Be Converted To Requested Type

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

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)

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!

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.

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.

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.

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!

Replacing the "select *" with the column names worked like a charm. Thanks!

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.

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.

@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. : )

An old post which won't die! :-) Obviously still an irritation to many of us.

Here are my observations:

1) This effects all (inc. non-cached) cfquery's.

2) Replacing the * with column names works.

3) Aliasing the * (e.g. a.*) only works if it was not previously aliased (for me). So if you already had a.* in the query, then deleted a column in table a, the aliasing doesn't protect you from seeing this error.

4) Late breaking news - rebooting the ColdFusion server seems to clear the problem!

My thinking is that table structures used by cfquery are cached somehow. Make a change on the SQL side, the query may fail. Make a change in the cfquery, it is then re-cached, so the query works again. Rebooting of course also clears the cache. Just a hypothesis!

Hope this helps - keep up the good work, Ben!

You can also clear the cache by ticking "Disable Connections" under the data source's Advance Settings in CF admin. Save. Then untick. Then save again.

Does anybody know why this error is appearing for us now when it never did in the past? Are we caching queries when we shouldn't be? Is this something new to CF9?

Commenting on Oxide said...

I had the following select statement that was still throwing this error. So, I don't think that is a definite fix.

SELECT UI.salutation, UI.firstname, UI.lastname, UI.email, UI.nickname, UI.photo, UA.*, S.state_abbr,

Thanks for this post, very helpful.

I was looking for a way to flush this DB/query cache. It looks like the way to do it is 'suspend all client connections' on the datasource.
Then re-run your query, then re-enable connections. Maybe.

Just ran into this issue with some legacy code. Thanks for getting this out there for us, Ben.

Thanks for the post, it was helpful.

I would like to point the fact that a * in the query as nothing to do with the problem itself. I had a query with no * in it and had this problem. Cf version was 9,0,0,251028. Changed an int field to a varchar(255).

I hit this on CF9, but I am explicitly naming all the columns instead of doing a select *.

We are using SQL Server and have a lot of our columns as bit flags. I changed one of the views to do a case statement to translate these to Yes/No on the request of a client who didn't like seeing the 1/0.

Since the datatype is now a string and not a bit, it must not have matched the structure and would throw an error.

Thanks, saved me yet again.

Just received this same error but with named columns. The problem was an empty space ' ' after the last column (ex: desc as description' '). Took a few minutes to spot that. Might save somebody some troubles.

If you get this error and add a space to the query that does the (select *) the error will go away. If you undo you space there error will come back. Until you restart the server. Something figured out the hard way. I found this post after alot of debugging. Ugh....

I've had some issues using select * ... and no longer do it ...

select t1.* seems to work for me though ...

Hi Ben,

I just ran into this after adding a varchar column to a table. Interestingly, all the queries that were erroring already had tbl_alias.*. I'm thinking that the only reason that seemed to work for some people is because it was a change to the query (notice in the comments that some people just added a space somewhere and that fixed it too).

I agree that the columns should be listed out, but there are a number of queries the my app I inherited and I didn't have time. So I tried the solution posted above by Tri and MrBuzzy, which was to go to the datasource in CFAdmin, tick "Disable Connections -- Suspend all client connections" under Advanced Settings, save the change, go back in and untick it, save it again, and refresh the application. That seemed to do it.