This was an interesting error that I encountered today. About two months ago, I added a bunch of cfqueryparams to the page that began returning this error today, "Error Executing Database Query. [Macromedia][SQLServer JDBC Driver]Value can not be converted to requested type.". I assumed the value being passed to the cfqueryparam was actually invalid.

I started out on the wrong path, doing everything to make sure the value being passed in was correct. Then my team member and I discovered this blog entry by Ben Nadel.

Basically, he says this error happens when you use SELECT * in your query with a cfqueryparam and you change the database structure. ColdFusion caches the query when you use cfqueryparam and if you change the database structure after this occurs, the columns in the cached query do not map to the database correctly anymore.

The fix? Specify all the columns you need to grab from the database and this will not happen. SELECT * shouldn't be used anyway, good practice says we should only be grabbing the fields that are needed.

This entry was posted on February 19, 2010 at 6:13 PM and has received 673 views. There are currently 1 comments. Print this entry.