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.

Short link: https://bennadel.com/1135

Reader Comments

sz Apr 21, 2008 at 3:02 PM

1 Comments

Thank you for this one. I was very close to desperation.

Ben Nadel Apr 21, 2008 at 3:27 PM

15,848 Comments

@Sz,

Glad to have helped.

Sal V Dec 4, 2008 at 3:27 PM

1 Comments

Thank you thank you! This has been driving me nuts for a while!

Fraser Jan 18, 2009 at 3:26 PM

1 Comments

Thanks for sharing this. I think you just saved me a lot of time!!

David Feb 25, 2009 at 6:04 AM

7 Comments

great thanks Ben, you saved the day again!

Daniel Fischer May 28, 2009 at 11:38 AM

1 Comments

Thanks Ben. This was a great help.

Ben Nadel Jun 1, 2009 at 1:35 PM

15,848 Comments

@All,

Glad this helped!

Dave Jun 2, 2009 at 1:08 PM

2 Comments

Thanks Ben! You saved my neck on a time sensitive project.

Ben Nadel Jun 2, 2009 at 1:24 PM

15,848 Comments

@Dave,

Awesome!

Tim Jul 14, 2009 at 11:26 AM

7 Comments

Well, this post just saved me what could have potentially taken a while to find out. Three cheers for testing locally using Access!

Jack Poe Apr 8, 2010 at 12:40 PM

2 Comments

Life saving advice yet again! Thanks from the USAF!

Sean Flynn Jun 16, 2010 at 2:49 PM

1 Comments

Thanks so much for this post; it saved me HOURS of frustration.

You rock. :D

Sean Flynn
Web Developer
NWF State College

Ben Nadel Jun 16, 2010 at 6:42 PM

15,848 Comments

@Sean,

Glad to help!

Tim Feb 11, 2011 at 9:47 AM

3 Comments

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.

Oh my chickens, this post is old!

Hit me up on Twitter if you want to discuss it further.