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
jaredwilli
May 19, 2013 at 2:31 PM
My Experience With AngularJS - The Super-heroic JavaScript MVW Framework
It's funny really just how well that image describes the way I would imagine most people that go with angular for some project is. I have had a similar roller-coaster ride with it as well, but not qu ... read »
Chris Hubert
May 17, 2013 at 7:42 PM
HashKeyCopier - An AngularJS Utility Class For Merging Cached And Live Data
Ben - thanks so much for posting these Angular articles and findings, they've been a huge help towards learning one of the more 'complex' JavaScript frameworks out there (IMO). I have been using Angu ... read »
sgeorge
May 16, 2013 at 5:01 PM
UPDATE: Parsing CSV Data Files In ColdFusion With csvToArray()
Your code was the closest thing I've found to obtaining some direction for converting ISO fields to values that CF can translate properly. Thank you for posting! ... read »
shah
May 15, 2013 at 10:37 PM
Very Simple Pusher And ColdFusion Powered Chat
hi id making plz easy ... read »
Chris B.
May 15, 2013 at 6:07 PM
Making SOAP Web Service Requests With ColdFusion And CFHTTP
Ben, you once again saved my bacon at work. Thank you, thank you, thank you! ... read »
Jonah
May 15, 2013 at 4:15 PM
What If All User Interface (UI) Data Came In Reports?
@Josh, Thanks! @Ben, I definitely recommend the David West book "Object Thinking" I've been quoting from. It goes deeply into the philosophy and history of OO programming. His breadth ... read »
Alex
May 15, 2013 at 11:36 AM
Ask Ben: Print Part Of A Web Page With jQuery
I found this helpfull when you need to keep (refresh) the original parent page after closing the iframe child print dialog (Hoping you're not using a form at this time so it won't submit again): On ... read »
Ben Nadel
May 14, 2013 at 7:13 PM
What If All User Interface (UI) Data Came In Reports?
@Jonah, If there's any books you'd recommend on the subject of domain modelling, I'd love to hear it. I just downloaded the free PDF of "Domain Driven Design Quickly". Figured I'd give it ... read »
InVision App - Prototyping Made Beautiful With Prototyping Tools