ColdFusion / SQL Error: Invalid Precision Value

Posted January 31, 2008 at 8:05 PM

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.

Post Comment  |  Ask Ben  |  Other Searches  |  Print Page



Learning ColdFusion 9 - ColdFusion 9 tutorials, samples, examples, demos

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 »
7,513 Comments
36,203 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 riding
Feb 25, 2009 at 6:04 AM // reply »
5 Comments
56 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 »
7,513 Comments
36,203 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 »
7,513 Comments
36,203 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!


Post Comment  |  Ask Ben

Recent Blog Comments
Eric P.
Mar 16, 2010 at 11:49 AM
Ask Ben: Building An AJAX, jQuery, And ColdFusion Powered Application
> I wonder if returnFormat="json" will set the mimetype in the response? Just checked; it does not. I show: Content-Type text/html; charset=UTF-8 I usually use returnFormat="json" with returnt ... read »
Raymond Camden
Mar 16, 2010 at 11:34 AM
Managing ColdFusion Sessions In A ColdFusion Builder Extension
"It's all just good conversation - I certainly am not that well versed in Builder yet; heck, I wrote this blog post AS I was exploring the concept ;)" Heh true - didn't mean to imply you shouldn't p ... read »
Ben Nadel
Mar 16, 2010 at 11:31 AM
Managing ColdFusion Sessions In A ColdFusion Builder Extension
@Raymond, It's all just good conversation - I certainly am not that well versed in Builder yet; heck, I wrote this blog post AS I was exploring the concept ;) I tried dumping out the CGI; you act ... read »
Raymond Camden
Mar 16, 2010 at 11:24 AM
Managing ColdFusion Sessions In A ColdFusion Builder Extension
FYI, you can also consider using a "one page app", ala Terry's Flex based "Builder Stats" - or just using jQuery for a rich app. ... read »
Raymond Camden
Mar 16, 2010 at 11:22 AM
Managing ColdFusion Sessions In A ColdFusion Builder Extension
"Once you're in the "web" work flow, I don't think you should need to modify any links? At that point, I think the user is basically in a stand-alone browser type of situation (theory) where cookies ... read »
Ben Nadel
Mar 16, 2010 at 11:19 AM
Managing ColdFusion Sessions In A ColdFusion Builder Extension
@Raymond, Once you're in the "web" work flow, I don't think you should need to modify any links? At that point, I think the user is basically in a stand-alone browser type of situation (theory) whe ... read »
Raymond Camden
Mar 16, 2010 at 11:16 AM
Managing ColdFusion Sessions In A ColdFusion Builder Extension
URLSessionFormat does a check to see if cookies are enabled. If not, it auto-adds the url token to the end. This _should_ work all the time, but I've seen it fail. Seriously though - if your exten ... read »
Ben Nadel
Mar 16, 2010 at 11:15 AM
Managing ColdFusion Sessions In A ColdFusion Builder Extension
@Raymond, Also, I should probably clarify that this really only works IF you intend to switch from XML to HTML after the first request. I am not sure how cross-XML requests (multi-step XML wizard) ... read »