Re: Error on site - Error converting data type varchar to numeric
I'll wager somebody's description includes a comma. I'll reinforce: use cfqueryparam ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:298323 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Error on site - Error converting data type varchar to numeric
Are any of your columns numeric? I would think that CatID, UserID, and Price
should be numeric columns, but your query treats them as strings. My guess
would be that a price is getting submitted with non-numeric data. (dollar
sign, commas, letters?)
The line number in the error detail may not necessarily be the line that is
actually causing the error when it comes to a cfquery tag.
-Original Message-
From: Rick King [mailto:[EMAIL PROTECTED]
Sent: Tuesday, February 05, 2008 2:39 PM
To: CF-Talk
Subject: Error on site - Error converting data type varchar to numeric
I have a site that allows people to list items for sell. I keep getting
errors emailed to me that state, Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]Error converting data type
varchar to numeric. brThe error occurred on line 30.
Here is my query:
cfquery name=Name datasource=DB
SET NOCOUNT ON;
INSERT INTO Products (CatID, UserID, Designer, Model, Color, DressSize,
Price, Material, Description, Status)
VALUES ('#Trim(FORM.CatID)#',
'#Trim(SESSION.Auth.UserID)#',
'#Trim(FORM.Designer)#',
'#Trim(FORM.Model)#',
'#Trim(FORM.Color)#',
'#Trim(FORM.DressSize)#',
'#Trim(FORM.Price)#',
'#Trim(FORM.Material)#',
'#Trim(FORM.Description)#',
'Approved');
SELECT @@identity AS ProdID FROM Products;
/cfquery
Line 30 is the #Trim(FORM.Description)# line in the query, but the
Description field IS set to varchar. Any ideas on why I'd be getting this
error? I can't seem to reproduce it, but several of my users are
consistently triggering the error.
Thanks
Rick
~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;160198600;22374440;w
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:298259
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Error on site - Error converting data type varchar to numeric
*always* validate on the server.
if you want to run some stuff on the client, that's fine... but
especially if you're talking about data... don't let the integrity of
your data rest on something you can't control (client side
validation).
easy enough to remove those non-numeric characters via cf on the server as well.
rereplace(myData, '[^0-9/.]', '', 'all')
On Feb 5, 2008 1:35 PM, Rick King [EMAIL PROTECTED] wrote:
Hmm...you're right, I'll remove the single quotes around CatID, UserID, and
Price.
I'm using JS validation that removes all non-numeric data from the Price
field, but maybe they have JS turned off?
Are any of your columns numeric? I would think that CatID, UserID, and Price
should be numeric columns, but your query treats them as strings. My guess
would be that a price is getting submitted with non-numeric data. (dollar
sign, commas, letters?)
The line number in the error detail may not necessarily be the line that is
actually causing the error when it comes to a cfquery tag.
I have a site that allows people to list items for sell. I keep getting
errors emailed to me that state, Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]Error converting data type
varchar to numeric. brThe error occurred on line 30.
Here is my query:
cfquery name=Name datasource=DB
SET NOCOUNT ON;
INSERT INTO Products (CatID, UserID, Designer, Model, Color, DressSize,
Price, Material, Description, Status)
VALUES ('#Trim(FORM.CatID)#',
'#Trim(SESSION.Auth.UserID)#',
'#Trim(FORM.Designer)#',
'#Trim(FORM.Model)#',
'#Trim(FORM.Color)#',
'#Trim(FORM.DressSize)#',
'#Trim(FORM.Price)#',
'#Trim(FORM.Material)#',
'#Trim(FORM.Description)#',
'Approved');
SELECT @@identity AS ProdID FROM Products;
/cfquery
Line 30 is the #Trim(FORM.Description)# line in the query, but the
Description field IS set to varchar. Any ideas on why I'd be getting this
error? I can't seem to reproduce it, but several of my users are
consistently triggering the error.
Thanks
Rick
~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;160198600;22374440;w
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:298274
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Error on site - Error converting data type varchar to numeric
Your query says that NONE of your data types are numeric. Is that true?
Ps... Every time you take data directly from forms and pass it to your
database without cleaning it... god kills a kitten. Please save the kittens
and use cfqueryparam.
..:.:.:.:.:.:.:.:.:.:.:.
Bobby Hartsfield
http://acoderslife.com
-Original Message-
From: Rick King [mailto:[EMAIL PROTECTED]
Sent: Tuesday, February 05, 2008 3:39 PM
To: CF-Talk
Subject: Error on site - Error converting data type varchar to numeric
I have a site that allows people to list items for sell. I keep getting
errors emailed to me that state, Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]Error converting data type
varchar to numeric. brThe error occurred on line 30.
Here is my query:
cfquery name=Name datasource=DB
SET NOCOUNT ON;
INSERT INTO Products (CatID, UserID, Designer, Model, Color, DressSize,
Price, Material, Description, Status)
VALUES ('#Trim(FORM.CatID)#',
'#Trim(SESSION.Auth.UserID)#',
'#Trim(FORM.Designer)#',
'#Trim(FORM.Model)#',
'#Trim(FORM.Color)#',
'#Trim(FORM.DressSize)#',
'#Trim(FORM.Price)#',
'#Trim(FORM.Material)#',
'#Trim(FORM.Description)#',
'Approved');
SELECT @@identity AS ProdID FROM Products;
/cfquery
Line 30 is the #Trim(FORM.Description)# line in the query, but the
Description field IS set to varchar. Any ideas on why I'd be getting this
error? I can't seem to reproduce it, but several of my users are
consistently triggering the error.
Thanks
Rick
~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;160198600;22374440;w
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:298276
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Error on site -Error converting data type varchar to numeric
VALUES ('#Trim(FORM.CatID)#',
'#Trim(SESSION.Auth.UserID)#',
First you open yourself up to SQL injection attack unless you use
cfqueryparam on all variables.
single quotes around CatID, UserID, and Price.
I'm using JS validation
You need to do server side validation before you think about JS validation.
JS validation is insecure and is for the user's experience only. Without
validating server side anyone can post any values to your action page. The
error you are seeing is usually a result of the inserted value being the
wrong data type example: $20.95 instead of 20.95 in a numeric field.
You cannot trust the data you receive from the browser.
Hmm..update..I just emailed the latest person who generated
the error. They said that they weren't able to upload
their images.
I have has issues where the user's Internet Security firewall was set so
tight that they could post the form that contained a multipart/form-data
field (File Upload) but there firewall would not send the data for that
field.
Best Regards,
Dennis Powers
UXB Internet - A website design and Hosting Company
690 Wolcott Road
P.O. Box 6029
Wolcott, CT 06716
Tel: (203)879-2844
http://www.uxbinternet.com/
http://www.uxb.net/
~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;160198600;22374440;w
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:298281
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
