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 »
10,640 Comments
69,065 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 »
10,640 Comments
69,065 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 »
10,640 Comments
69,065 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 »
10,640 Comments
69,065 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
InVision App - Prototyping Made Beautiful With Prototyping Tools Ben Nadel's Company - Epicenter Consulting Recent Blog Comments
Misty
Feb 12, 2012 at 3:37 AM
Learning ColdFusion 8: CFImage Part III - Watermarks And Transparency
Hi Ben, Just to ask currently it is placed bottom right corner, if i need to replace the same rendered image on the bottom left side or in the bottom center, how that can be calculated. bottom ce ... read »
Raphael Mobis
Feb 11, 2012 at 9:29 PM
Use jQuery's SlideDown() With Fixed-Width Elements To Prevent Jumping
I can't say how glad I am that I found your post. Thank you very much. ... read »
Bartezz
Feb 10, 2012 at 7:21 PM
jQuery AJAX Strips Script Tags And Inserts Them After Parent-Most Elements
Update! Instead of $(eval(options.insertAfter)).after(data['insertData']); I now use: var ajaxNode = document.createElement('span'); var parent = $(eval(options.insertAfter))[0].parentNode; ... read »
Bartezz
Feb 10, 2012 at 6:18 PM
jQuery AJAX Strips Script Tags And Inserts Them After Parent-Most Elements
encountered this same, what I consider, jQuery bug last week. I'm building a site in which I load some content via AJAX. This content contains Linkedin share button placeholders which Linkedin API ne ... read »
Drew Wells
Feb 10, 2012 at 11:30 AM
Cross-Origin Resource Sharing (CORS) AJAX Requests Between jQuery And Node.js
After you understand the concepts here, this is an awesome cheatsheet for enabling CORS in just about anything http://enable-cors.org/ ... read »
JM
Feb 10, 2012 at 9:10 AM
My Safari Browser SQLite Database Hello World Example
@Amy, Here is a very good tutorial on how to use JOIN: http://www.sqltutorial.org/sqljoin-innerjoin.aspx ... read »
Stefan Richter
Feb 10, 2012 at 4:42 AM
Building A Twitter-Inspired RESTful API Architecture In ColdFusion
This is great, very useful Ben. I spotted a small typo in the api.cgm listing: <cfthrow type="Unauthroized" /> Cheers Stefan ... read »
Hugh
Feb 9, 2012 at 10:35 PM
CFDirectory Filtering Uses Pipe Character For Multiple Filters (Thanks Steve Withington)
I was wondering if there would be a filter you could apply so that you got everything but what you included in the filter. As in show me all docs that are not a .pdf. ... read »