ColdFusion / SQL Error: Invalid Precision Value
Earlier today, I was working on a project that was using an MS Access Database (sexy, I know!) when one of the queries starting throwing this error:
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft]
[ODBC Microsoft Access Driver]Invalid precision value
When I went to look at the query, which was just storing FORM data, I noticed that all of the data types in my ColdFusion CFQueryParam tags were set to CF_SQL_VARCHAR. Thanks to some advice I received months ago from fellow ColdFusion programmer, Spencer Strickland, I knew exactly what the problem was. In Microsoft SQL Server, which is what I am accustomed to, CF_SQL_VARCHAR will work for any kind of text field; however, in MS Access, CF_SQL_VARCHAR will not work with fields of data-type, Memo. For some reason, and I guess this is a driver issue, Memo fields require a CFQueryParam of sql type, CF_SQL_LONGVARCHAR.
Minor issue, but a huge ass pain if you didn't know what to look for.
Reader Comments
Thank you for this one. I was very close to desperation.
@Sz,
Glad to have helped.
Thank you thank you! This has been driving me nuts for a while!
Thanks for sharing this. I think you just saved me a lot of time!!
great thanks Ben, you saved the day again!
Thanks Ben. This was a great help.
@All,
Glad this helped!
Thanks Ben! You saved my neck on a time sensitive project.
@Dave,
Awesome!
Well, this post just saved me what could have potentially taken a while to find out. Three cheers for testing locally using Access!
Life saving advice yet again! Thanks from the USAF!
Thanks so much for this post; it saved me HOURS of frustration.
You rock. :D
Sean Flynn
Web Developer
NWF State College
@Sean,
Glad to help!
I have recently had the same error with a SQL Server "text" field/column. cfsqltype of cf_sql_text and cf_sql_varchar gave errors when the value was over a specific length... cf_sql_longvarchar seems to do the trick.
Realizing this is a dated post - but still useful.