Re: Invalid Precision Value from SQL Server CF9
yes, I could do it that way. However, all dates I try to insert are giving the error, not just ones I'm using the function #now()# in. Turns out the issue is my data source in CF. We have been using the ODBC socket to connect CF to the System DSN which uses the SQL Native driver. When I change my CF data source to the SQL Server driver instead of the ODBC Socket, all works fine! database have built in functions for getting the current date, so you don't need to use CF. e.g. for sql server use editdate - getDate() ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:344362 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Invalid Precision Value from SQL Server CF9
Turns out what is causing the error is the line EditDate=cfqueryparam value=#now()# cfsqltype=cf_sql_timestamp the EditDate is a datetime field. Any ideas? thanks! ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:344305 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Invalid Precision Value from SQL Server CF9
database have built in functions for getting the current date, so you don't need to use CF. e.g. for sql server use editdate - getDate() On Fri, May 6, 2011 at 9:21 PM, Nicki Tabb vnt...@alaska.edu wrote: Turns out what is causing the error is the line EditDate=cfqueryparam value=#now()# cfsqltype=cf_sql_timestamp the EditDate is a datetime field. Any ideas? thanks! ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:344306 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Invalid Precision Value from SQL Server CF9
I have tested the query using 0 instead of No for the default form value (see reply thread) and I am still receiving the Invalid Precision Value error. In the error, it highlights the last line as the issue (although sometimes that is deceiving) cfqueryparam cfsqltype=cf_sql_varchar value=#FORM.Notes# maxlength=1000 I've seen forums that say this error is seen when using MS Access, but I am using SQL Server 2008. thanks, nicki I am testing some code on CF9 before we move all our sites from CF8 and have come upon this issue. The page that runs the query below is returning an Invalid Precision Value error. cfquery name=addVRRF datasource=eworfs INSERT INTO VRRFs (RequestDateTime, Name, Email, Phone, Department, ENum, MilesHours, Urgency, VLocation, RepairDate, LoanerYN, Notes) VALUES (cfqueryparam cfsqltype=cf_sql_timestamp value=#now()#, cfqueryparam cfsqltype=cf_sql_varchar value=#FORM.name# maxlength=50, cfqueryparam cfsqltype=cf_sql_varchar value=#FORM.email# maxlength=150, cfqueryparam cfsqltype=cf_sql_varchar value=#FORM.phone# maxlength=12, cfqueryparam cfsqltype=cf_sql_varchar value=#FORM.Department# maxlength=50, cfqueryparam cfsqltype=cf_sql_varchar value=#FORM.ENum# maxlength=50, cfqueryparam cfsqltype=cf_sql_varchar value=#FORM.MilesHours# maxlength=50, cfqueryparam cfsqltype=cf_sql_varchar value=#FORM.Urgency# maxlength=10, cfqueryparam cfsqltype=cf_sql_varchar value=#FORM.VLocation# maxlength=150, cfqueryparam cfsqltype=cf_sql_timestamp value=#FORM.RepairDate# null=#iif((FORM.RepairDate eq ), de(yes), de(no))#, cfqueryparam cfsqltype=cf_sql_bit value=#FORM.LoanerYN#, cfqueryparam cfsqltype=cf_sql_varchar value=#FORM.Notes# maxlength=1000) /cfquery I am using SQL Server 2008 (driver is SQL Native client 10.0). This query works fine on CF8 with the same database but gives the error on the CF9 installation. From what i can find through searching, this is usually due to a numeric value or using an Access database. Since I am not inserting any numeric values and it's not Access, I'm baffled. Any ideas greatly appreciated! Thanks, Nicki ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:342473 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Invalid Precision Value from SQL Server CF9
The default is NO so it always has a value What is the value of #FORM.LoanerYN# when you try to insert it in the bit field? ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:342431 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Invalid Precision Value from SQL Server CF9
Surely a bit field will need 0 or 1, not a string? Or was that not what you meant? - Original Message From: Nicki Tabb vnt...@alaska.edu To: cf-talk cf-talk@houseoffusion.com Sent: Fri, 18 February, 2011 15:32:19 Subject: Re: Invalid Precision Value from SQL Server CF9 The default is NO so it always has a value What is the value of #FORM.LoanerYN# when you try to insert it in the bit field? ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:342433 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Invalid Precision Value from SQL Server CF9
SQL will read a NO or FALSE or 0 as the same Surely a bit field will need 0 or 1, not a string? Or was that not what you meant? The default is NO so it always has a value What is the value of #FORM.LoanerYN# when you try to insert it in the bit field? ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:342437 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: Invalid Precision Value from SQL Server CF9
No it doesn't.
Here is an example on SQL 2005:
declare @test table (
testVal bit
)
insert into @test (testval) values (0);
insert into @test (testval) values ('Yes'); -- Results in error
Conversion failed when converting the varchar value 'Yes' to data type
bit.
insert into @test (testval) values ('True'); -- Results in error
Conversion failed when converting the varchar value 'True' to data type
bit.
insert into @test (testval) values ('No'); -- Results in error
Conversion failed when converting the varchar value 'No' to data type
bit.
insert into @test (testval) values ('False'); -- Results in error
Conversion failed when converting the varchar value 'False' to data type
bit.
select * from @test
Sql expects data types that match. ColdFusion isn't strongly typed so
false and no are equivalent to 0.
Steve
-Original Message-
From: Nicki Tabb [mailto:vnt...@alaska.edu]
Sent: Friday, February 18, 2011 1:17 PM
To: cf-talk
Subject: Re: Invalid Precision Value from SQL Server CF9
SQL will read a NO or FALSE or 0 as the same
~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:342438
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Invalid Precision Value from SQL Server CF9
sorry,
I guess I meant that, when using CF, I have never had issues with No or False
or 0 being read as the same by the SQL Server
No it doesn't.
Here is an example on SQL 2005:
declare @test table (
testVal bit
)
insert into @test (testval) values (0);
insert into @test (testval) values ('Yes'); -- Results in error
Conversion failed when converting the varchar value 'Yes' to data type
bit.
insert into @test (testval) values ('True'); -- Results in error
Conversion failed when converting the varchar value 'True' to data type
bit.
insert into @test (testval) values ('No'); -- Results in error
Conversion failed when converting the varchar value 'No' to data type
bit.
insert into @test (testval) values ('False'); -- Results in error
Conversion failed when converting the varchar value 'False' to data type
bit.
select * from @test
Sql expects data types that match. ColdFusion isn't strongly typed so
false and no are equivalent to 0.
Steve
SQL will read a NO or FALSE or 0 as the same
~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:342439
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Invalid Precision Value from SQL Server CF9
CF will read in bit values from SQL Server and evaluate them as
equivalent to true/false or yes/no. However, SQL Server expects 0 or 1
from ColdFusion when writing to a bit field. There's probably a UDF on
CFLib.org that converts CF boolean values into strict bit values, but
here's one I wrote for this purpose:
!--- BooltoBit can be used to convert logical boolean values
(on/off,true/false,yes/no, 1/0) to bit values for
insertion into SQL databases
Example:
foo = cfqueryparam value=#BooltoBit(foo)#
cfsqltype=cf_sql_bit
---
cffunction name=BooltoBit returntype=numeric
cfargument name=b type=string required=yes
cfset var result =
cfif LCase(b) eq on
cfset b = True
cfelseif LCase(b) eq off
cfset b = False
/cfif
cfif IsBoolean(b)
cfif b
cfset result = 1
cfelse
cfset result = 0
/cfif
cfelse
cfset result = -1
/cfif
cfreturn result
/cffunction
HTH,
Carl
On 2/18/2011 10:38 AM, Nicki Tabb wrote:
sorry,
I guess I meant that, when using CF, I have never had issues with No or False
or 0 being read as the same by the SQL Server
No it doesn't.
Here is an example on SQL 2005:
declare @test table (
testVal bit
)
insert into @test (testval) values (0);
insert into @test (testval) values ('Yes'); -- Results in error
Conversion failed when converting the varchar value 'Yes' to data type
bit.
insert into @test (testval) values ('True'); -- Results in error
Conversion failed when converting the varchar value 'True' to data type
bit.
insert into @test (testval) values ('No'); -- Results in error
Conversion failed when converting the varchar value 'No' to data type
bit.
insert into @test (testval) values ('False'); -- Results in error
Conversion failed when converting the varchar value 'False' to data type
bit.
select * from @test
Sql expects data types that match. ColdFusion isn't strongly typed so
false and no are equivalent to 0.
Steve
SQL will read a NO or FALSE or 0 as the same
~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:342440
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Invalid Precision Value from SQL Server CF9
I am testing some code on CF9 before we move all our sites from CF8 and have come upon this issue. The page that runs the query below is returning an Invalid Precision Value error. cfquery name=addVRRF datasource=eworfs INSERT INTO VRRFs (RequestDateTime, Name, Email, Phone, Department, ENum, MilesHours, Urgency, VLocation, RepairDate, LoanerYN, Notes) VALUES (cfqueryparam cfsqltype=cf_sql_timestamp value=#now()#, cfqueryparam cfsqltype=cf_sql_varchar value=#FORM.name# maxlength=50, cfqueryparam cfsqltype=cf_sql_varchar value=#FORM.email# maxlength=150, cfqueryparam cfsqltype=cf_sql_varchar value=#FORM.phone# maxlength=12, cfqueryparam cfsqltype=cf_sql_varchar value=#FORM.Department# maxlength=50, cfqueryparam cfsqltype=cf_sql_varchar value=#FORM.ENum# maxlength=50, cfqueryparam cfsqltype=cf_sql_varchar value=#FORM.MilesHours# maxlength=50, cfqueryparam cfsqltype=cf_sql_varchar value=#FORM.Urgency# maxlength=10, cfqueryparam cfsqltype=cf_sql_varchar value=#FORM.VLocation# maxlength=150, cfqueryparam cfsqltype=cf_sql_timestamp value=#FORM.RepairDate# null=#iif((FORM.RepairDate eq ), de(yes), de(no))#, cfqueryparam cfsqltype=cf_sql_bit value=#FORM.LoanerYN#, cfqueryparam cfsqltype=cf_sql_varchar value=#FORM.Notes# maxlength=1000) /cfquery I am using SQL Server 2008 (driver is SQL Native client 10.0). This query works fine on CF8 with the same database but gives the error on the CF9 installation. From what i can find through searching, this is usually due to a numeric value or using an Access database. Since I am not inserting any numeric values and it's not Access, I'm baffled. Any ideas greatly appreciated! Thanks, Nicki ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:342384 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Invalid Precision Value from SQL Server CF9
What is the value of #FORM.LoanerYN# when you try to insert it in the bit field? On Thu, Feb 17, 2011 at 8:19 AM, Nicki Tabb vnt...@alaska.edu wrote: I am testing some code on CF9 before we move all our sites from CF8 and have come upon this issue. The page that runs the query below is returning an Invalid Precision Value error. cfqueryparam cfsqltype=cf_sql_bit value=#FORM.LoanerYN#, I am using SQL Server 2008 (driver is SQL Native client 10.0). This query works fine on CF8 with the same database but gives the error on the CF9 installation. From what i can find through searching, this is usually due to a numeric value or using an Access database. Since I am not inserting any numeric values and it's not Access, I'm baffled ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:342418 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Invalid precision value
have you looked at the docs to see how dateFormat() is used? it's quite clear you haven't... take 5 secs and it will be VERY evident amigo tw Sent from my iPhone... don't hate On Oct 27, 2009, at 12:10 AM, Matthew Smith chedders...@gmail.com wrote: wrap dateformat() around the dateadd() function. tw Sent from my iPhone... don't hate On Oct 27, 2009, at 12:02 AM, Matthew Smith chedders...@gmail.com wrote: Still the same: cffunction access=public name=qry_contestthisweek_get output=false returntype=query cfargument name=lastthursday type=string default= cfset nextthursday = dateformat(dateadd(d, 7, arguments.lastthursday)) cfquery name=qry_contestthisweek_get datasource=#variables.datasource# select* from#variables.sqlobjectprefix#contest where1 = 1 and addeddate between cfqueryparam value=#createodbcdatetime(arguments.lastthursday)# cfsqltype=CF_SQL_TIMESTAMP and cfqueryparam value=#createodbcdatetime(nextthursday)# cfsqltype=CF_SQL_TIMESTAMP order by score, age /cfquery cfreturn qry_contestthisweek_get /cffunction ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:327753 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Invalid precision value
Getting this on a query. Define Getting. Where are these values displayed? ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:327759 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Invalid precision value
wrap dateformat() around the dateadd() function. NO. createOdbcDate expects a date value as a parameter. One should never submit a formated date to this function, the date will be de-formated anyway, with the risk that the format is not compatible (ie: european date format). ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:327760 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Invalid precision value
Right, my understanding is that cfqueryparam will take care of making sure the db gets the date as it expects it. If I'm not wrong, you should just ditch the CreateObdcDateTime call. Dominic 2009/10/27 Claude Schneegans schneeg...@internetique.com wrap dateformat() around the dateadd() function. NO. createOdbcDate expects a date value as a parameter. One should never submit a formated date to this function, the date will be de-formated anyway, with the risk that the format is not compatible (ie: european date format). ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:327761 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Invalid precision value
Getting this on a query.
Parameters:
nextthursday = {ts '2009-10-29 00:00:00'}
lastthursday = 2009-10-22 00:00:00.000
cffunction access=public name=qry_contestthisweek_get output=false
returntype=query
cfargument name=lastthursday type=string default=
cfset nextthursday = dateadd(d, 7, arguments.lastthursday)
cfquery
name=qry_contestthisweek_get
datasource=#variables.datasource#
select *
from#variables.sqlobjectprefix#contest
where 1 = 1
and addeddate between cfqueryparam
value=#createodbcdatetime(arguments.lastthursday)#
cfsqltype=CF_SQL_TIMESTAMP and cfqueryparam
value=#createodbcdatetime(nextthursday)# cfsqltype=CF_SQL_TIMESTAMP
order by score, age
/cfquery
cfreturn qry_contestthisweek_get
/cffunction
~|
Want to reach the ColdFusion community with something they want? Let them know
on the House of Fusion mailing lists
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:327735
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Invalid precision value
wrap dateformat() around the dateadd() function.
tw
Sent from my iPhone... don't hate
On Oct 27, 2009, at 12:02 AM, Matthew Smith chedders...@gmail.com
wrote:
Getting this on a query.
Parameters:
nextthursday = {ts '2009-10-29 00:00:00'}
lastthursday = 2009-10-22 00:00:00.000
cffunction access=public name=qry_contestthisweek_get
output=false returntype=query
cfargument name=lastthursday type=string default=
cfset nextthursday = dateadd(d, 7, arguments.lastthursday)
cfquery
name=qry_contestthisweek_get
datasource=#variables.datasource#
select*
from#variables.sqlobjectprefix#contest
where1 = 1
and addeddate between cfqueryparam
value=#createodbcdatetime(arguments.lastthursday)#
cfsqltype=CF_SQL_TIMESTAMP and cfqueryparam
value=#createodbcdatetime(nextthursday)#
cfsqltype=CF_SQL_TIMESTAMP
order by score, age
/cfquery
cfreturn qry_contestthisweek_get
/cffunction
~|
Want to reach the ColdFusion community with something they want? Let them know
on the House of Fusion mailing lists
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:327736
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Invalid precision value
wrap dateformat() around the dateadd() function. tw Sent from my iPhone... don't hate On Oct 27, 2009, at 12:02 AM, Matthew Smith chedders...@gmail.com wrote: Still the same: cffunction access=public name=qry_contestthisweek_get output=false returntype=query cfargument name=lastthursday type=string default= cfset nextthursday = dateformat(dateadd(d, 7, arguments.lastthursday)) cfquery name=qry_contestthisweek_get datasource=#variables.datasource# select * from#variables.sqlobjectprefix#contest where 1 = 1 and addeddate between cfqueryparam value=#createodbcdatetime(arguments.lastthursday)# cfsqltype=CF_SQL_TIMESTAMP and cfqueryparam value=#createodbcdatetime(nextthursday)# cfsqltype=CF_SQL_TIMESTAMP order by score, age /cfquery cfreturn qry_contestthisweek_get /cffunction ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:327737 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Invalid precision value
I am receiving the following CF error: I don't have an answer for the question you asked, but I think you're asking the wrong question. First, instead of having a column which contains a comma-delimited list, you'd probably be better off with a lookup table with those values instead, and an intersection table between your table and the lookup table. Your database doesn't appear to be in third normal form, and it should be. Second, instead of doing a bunch of LIKE searches to find values within database columns, you'd probably be better off using full-text indexing. Searching for a string in the middle of a larger string using LIKE is very expensive, since your database can't use indexes for such a search. Fortunately, CF comes with Verity, which is ideal for these sorts of searches. Of course, if you redesign the database properly you can avoid this kind of search in this specific case, but you may run into future cases where full-text indexing is the better approach. Finally, you might try using the Access with Unicode driver instead. This driver uses ADODB instead of ODBC, I think, so it may not suffer from the same limitations. To see if it's an ODBC limitation, you could try running the query from an ODBC client directly (like MS Query) rather than from CF. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! Kevin/Dave: Thanks for your feedback. Changing the driver to Access with Unicode returned the same error, although running the query outsdie CF is successful. Kevin: I removed the 0 = 0 portion as it is no longer needed. Thanks for pointing that out. The error reamins, however. Could you describe the SQL I can use to perform the query another way? Dave: I agree that the database structure should be modified. Unfortunatley, I inherited the design and the DB is being used by another application. Changing the architecture would have a definite impact in other areas. I will look into the possibility of using Verity. Thanks to both of you for your replies. -Mike ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:208242 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Invalid precision value
I am receiving the following CF error: Error Executing Database Query. [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver]Invalid precision value If I try to run the query directly from MS Access, using the same SQL, I receive no error and the results are returned properly. Any ideas on this one? The SQL is: SELECT * FROM foo WHERE targetCoStage LIKE '%Start-up%' AND targetCoStage LIKE '%Development%' AND targetCoStage LIKE '%Early Stage%' AND targetCoStage LIKE '%Late Stage%' AND targetCoStage LIKE '%Middle Stage%' AND UpdateStatus = 'Updated' AND 0 = 0 ORDER BY ID ASC The query above will run successfully in CF as long as I have no more than four AND clauses. Once I add a fifth, no matter what the clause is, CF chokes, but Access is fine. The targetCoStage column contains a comma delimited values, e.g.: Start-up,Growth,Development,Early Stage Thanks! Mike ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:207856 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Invalid precision value
Its not CF thats choking, its the DB driver. Unless you can replace it with something more stable, you might need to change your query to something else that gets the job done. Also, why do you have 0=0 within your query? That seems a little odd... Kevin -Original Message- From: Mike Rogan [mailto:[EMAIL PROTECTED] Sent: Friday, May 27, 2005 8:41 AM To: CF-Talk Subject: Invalid precision value I am receiving the following CF error: Error Executing Database Query. [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver]Invalid precision value If I try to run the query directly from MS Access, using the same SQL, I receive no error and the results are returned properly. Any ideas on this one? The SQL is: SELECT * FROM foo WHERE targetCoStage LIKE '%Start-up%' AND targetCoStage LIKE '%Development%' AND targetCoStage LIKE '%Early Stage%' AND targetCoStage LIKE '%Late Stage%' AND targetCoStage LIKE '%Middle Stage%' AND UpdateStatus = 'Updated' AND 0 = 0 ORDER BY ID ASC The query above will run successfully in CF as long as I have no more than four AND clauses. Once I add a fifth, no matter what the clause is, CF chokes, but Access is fine. The targetCoStage column contains a comma delimited values, e.g.: Start-up,Growth,Development,Early Stage Thanks! Mike ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:207859 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Invalid precision value
I am receiving the following CF error: Error Executing Database Query. [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver]Invalid precision value If I try to run the query directly from MS Access, using the same SQL, I receive no error and the results are returned properly. Any ideas on this one? The SQL is: SELECT * FROM foo WHERE targetCoStage LIKE '%Start-up%' AND targetCoStage LIKE '%Development%' AND targetCoStage LIKE '%Early Stage%' AND targetCoStage LIKE '%Late Stage%' AND targetCoStage LIKE '%Middle Stage%' AND UpdateStatus = 'Updated' AND 0 = 0 ORDER BY ID ASC The query above will run successfully in CF as long as I have no more than four AND clauses. Once I add a fifth, no matter what the clause is, CF chokes, but Access is fine. The targetCoStage column contains a comma delimited values, e.g.: Start-up,Growth,Development,Early Stage I don't have an answer for the question you asked, but I think you're asking the wrong question. First, instead of having a column which contains a comma-delimited list, you'd probably be better off with a lookup table with those values instead, and an intersection table between your table and the lookup table. Your database doesn't appear to be in third normal form, and it should be. Second, instead of doing a bunch of LIKE searches to find values within database columns, you'd probably be better off using full-text indexing. Searching for a string in the middle of a larger string using LIKE is very expensive, since your database can't use indexes for such a search. Fortunately, CF comes with Verity, which is ideal for these sorts of searches. Of course, if you redesign the database properly you can avoid this kind of search in this specific case, but you may run into future cases where full-text indexing is the better approach. Finally, you might try using the Access with Unicode driver instead. This driver uses ADODB instead of ODBC, I think, so it may not suffer from the same limitations. To see if it's an ODBC limitation, you could try running the query from an ODBC client directly (like MS Query) rather than from CF. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:207882 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Invalid Precision Value
I am getting an occasional error message upon form submission that says: Invalid Precision Value Predictably enough, the line referenced is my database insert line, which reads as follows: cfinsert datasource=Falls tablename=tblFalls Noteworthy is the fact that there are close to 50 form fields that get submitted as a result of this form. The quick research I have done suggests that it might be prudent to make use of queryparam as part of my insert statement, but I fear that would require me to specifically write out each and every fieldname and datatype in my insert statement. This is not an opposition to doing things the right way, but I would like to know if this is truly the gist of my problem, or if I need to be looking elsewhere. TIA, Tim ~| Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net http://www.cfhosting.net Message: http://www.houseoffusion.com/lists.cfm/link=i:4:186462 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Invalid Precision Value
Precision...I believe..refers to the number of decimal places in a number...I would dump the form upon submission and check and see if you are getting any .23489759384759834 going into a DB field that is set to only accept say 2 decimal places..? On Tue, 7 Dec 2004 10:35:43 -0500, Claremont, Timothy [EMAIL PROTECTED] wrote: I am getting an occasional error message upon form submission that says: Invalid Precision Value Predictably enough, the line referenced is my database insert line, which reads as follows: cfinsert datasource=Falls tablename=tblFalls Noteworthy is the fact that there are close to 50 form fields that get submitted as a result of this form. The quick research I have done suggests that it might be prudent to make use of queryparam as part of my insert statement, but I fear that would require me to specifically write out each and every fieldname and datatype in my insert statement. This is not an opposition to doing things the right way, but I would like to know if this is truly the gist of my problem, or if I need to be looking elsewhere. TIA, Tim ~| Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net http://www.cfhosting.net Message: http://www.houseoffusion.com/lists.cfm/link=i:4:186470 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: invalid precision value
For all those who helped with the Invalid Precision value error that came up when inserting from a CSV, thanks - I figured it out on my own finally - it turns out that for whatever reason, I had to go into Excel and highlight that column and manually set the cells to TEXT, then resave it, even though I'd saved it as a CSV file already... go figure. ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
invalid precision value
I'm uploading a csv file into Access in a loop using the CSV2Data custom tag- adding new records and I get the following error: ODBC Error Code = S1104 (Invalid precision value) I've uploaded ten csv files that all worked well but this one blows up... What can cause this error message? One of my fields contains a lot of text (importing it into a Memo field, and there's commas inside the contents of the field but I was under the impression that this is acceptable in such a tag. ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: invalid precision value
Which record is causing it to bomb? I've found that the following sort of record causes the most problems with CSV tags: OK Field 1, 2, 3 - This is, perhaps reasonable so, OK, 4-This tends to Blow Up, many times. Field 4 tends to end up as 2 or more fields, and if your tag doesn't fix this bombs out the insert/update. Andy -Original Message- From: Rafael Alan Bleiweiss [mailto:[EMAIL PROTECTED]] Sent: Thursday, January 23, 2003 12:47 PM To: CF-Talk Subject: invalid precision value I'm uploading a csv file into Access in a loop using the CSV2Data custom tag- adding new records and I get the following error: ODBC Error Code = S1104 (Invalid precision value) I've uploaded ten csv files that all worked well but this one blows up... What can cause this error message? One of my fields contains a lot of text (importing it into a Memo field, and there's commas inside the contents of the field but I was under the impression that this is acceptable in such a tag. ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: invalid precision value
OK Field 1, 2, 3 - This is, perhaps reasonable so, OK, 4-This tends to Blow Up, many times. Field 4 tends to end up as 2 or more fields, and if your tag doesn't fix this bombs out the insert/update. Further research shows that previous csv uploads had commas in that field and they were processed out properly in the tag loop... so there's some sort of bad data apparently in the csv I'm trying to post. I just can't find it. ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
