ColdFusion / SQL Error: Invalid Precision Value

Posted January 31, 2008 at 8:05 PM by Ben Nadel

Tags: ColdFusion

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

sz
Apr 21, 2008 at 3:02 PM // reply »
1 Comments
1 Points

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


Ben Nadel
Apr 21, 2008 at 3:27 PM // reply »
11,238 Comments
75,337 Points

@Sz,

Glad to have helped.


Sal V
Dec 4, 2008 at 3:27 PM // reply »
1 Comments
1 Points

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


Fraser
Jan 18, 2009 at 3:26 PM // reply »
1 Comments
11 Points

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


David
Feb 25, 2009 at 6:04 AM // reply »
7 Comments
77 Points

great thanks Ben, you saved the day again!


Daniel Fischer
May 28, 2009 at 11:38 AM // reply »
1 Comments
9 Points

Thanks Ben. This was a great help.


Ben Nadel
Jun 1, 2009 at 1:35 PM // reply »
11,238 Comments
75,337 Points

@All,

Glad this helped!


Dave
Jun 2, 2009 at 1:08 PM // reply »
2 Comments
12 Points

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


Ben Nadel
Jun 2, 2009 at 1:24 PM // reply »
11,238 Comments
75,337 Points

@Dave,

Awesome!


Tim
Jul 14, 2009 at 11:26 AM // reply »
7 Comments
78 Points

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 // reply »
2 Comments
24 Points

Life saving advice yet again! Thanks from the USAF!


Sean Flynn
Jun 16, 2010 at 2:49 PM // reply »
1 Comments
10 Points

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 // reply »
11,238 Comments
75,337 Points

@Sean,

Glad to help!


Tim V
Feb 11, 2011 at 9:47 AM // reply »
1 Comments
11 Points

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.



Post A Comment

Comment Etiquette: Please do not post spam. Please keep the comments on-topic. Please do not post unrelated questions or large chunks of code. And, above all, please be nice to each other - we're trying to have a good conversation here.

Please review the following issues:

Author Name:
Author Email:
Author Website:
Comment:
Supported HTML tags for formatting: <strong>bold</strong>   <em>italic</em>   <code>code</code>







  • Help Wanted - Find Your Next ColdFusion Job
Ben Nadel's Company - Epicenter Consulting Recent Blog Comments
Jeff
May 21, 2013 at 7:46 PM
Using Plupload For Drag & Drop File Uploads In ColdFusion
No luck. At least I have uncovered the cause, URLScan 3.1. Here is what I see in the IIS log when a file is over 30mb. 2013-05-21 23:29:05 10.105.45.128 GET /plupload/assets/jquery/jquery-1.8. ... read »
Jeff
May 21, 2013 at 6:12 PM
Using Plupload For Drag & Drop File Uploads In ColdFusion
Ben, I did not see you after Pete Freitag's Lockdown session at cfObjective but he said that IIS sets file size limits at 30MB by default which just happened to be the threshold for file size when ... read »
Ted Daniels
May 21, 2013 at 11:51 AM
Ask Ben: Parsing Very Large XML Documents In ColdFusion
Looking at my first ever XML document that I have to parse and put into MS SQL 2000 with CF8. I get it to list the desired Field name, many times over, and have a long list of this field name displa ... read »
Prashant Gupta
May 21, 2013 at 9:25 AM
Turning Off and On Identity Column in SQL Server
you are awesome..i am lucky to get this blog between such a garbage one....Thanks, Prashant ... read »
Ben Nadel
May 20, 2013 at 4:38 PM
Using A Dynamic Column Name With ValueList() In ColdFusion
@Dana, Your confusion is well founded, since this is a very confusing features. In fact, it ONLY works if you use array notation. Meaning, that this: arrayToList( query[ "columnName" ] ) ... read »
DanaK
May 20, 2013 at 4:34 PM
Using A Dynamic Column Name With ValueList() In ColdFusion
I was thinking chicken and the egg, I wouldn't have expected it to work in the valuelist going in I guess. Maybe I just need a beer, long day :) ... read »
Ben Nadel
May 20, 2013 at 4:29 PM
Using A Dynamic Column Name With ValueList() In ColdFusion
@Dana, That's if you're trying to reference a specific row. In this case, we're trying to reference the entire query column as one cohesive value. So, you are correct that if you wanted to output a ... read »
DanaK
May 20, 2013 at 4:24 PM
Using A Dynamic Column Name With ValueList() In ColdFusion
I thought when you used array notation to reference queries you always had to have the row or it would throw a similar error as well? ... read »
InVision App - Prototyping Made Beautiful With Prototyping Tools