RE: Sql Server DECIMAL datatype
Try Price as decimal(9,2). Dave -Original Message- From: Matthew Small [mailto:[EMAIL PROTECTED] Sent: Thursday, September 01, 2005 10:35 AM To: CF-Talk Subject: RE: Sql Server DECIMAL datatype OK, I did this and it still does not help. I don't understand why this is a problem. Could by chance is the decimal datatype in the SP be the problem? I'm going to check into that, but I don't quite know how to fix the problem if it is. It basically looks like: StoredProc TableId as int, Price as decimal, etc Is there a way to define the decimal datatype for Price so it has a scale of 2? Thank you, Matthew Small Web Developer American City Business Journals 704-973-1045 [EMAIL PROTECTED] ** The information contained in this message, including attachments, may contain privileged or confidential information that is intended to be delivered only to the person identified above. If you are not the intended recipient, or the person responsible for delivering this message to the intended recipient, ALLTEL requests that you immediately notify the sender and asks that you do not read the message or its attachments, and that you delete them without copying or sending them to anyone else. ~| 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:217097 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: Sql Server DECIMAL datatype
OK, I did this and it still does not help. I don't understand why this is a problem. Could by chance is the decimal datatype in the SP be the problem? I'm going to check into that, but I don't quite know how to fix the problem if it is. It basically looks like: StoredProc TableId as int, Price as decimal, etc Is there a way to define the decimal datatype for Price so it has a scale of 2? Thank you, Matthew Small Web Developer American City Business Journals 704-973-1045 [EMAIL PROTECTED] -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Thursday, September 01, 2005 10:17 AM To: CF-Talk Subject: RE: Sql Server DECIMAL datatype Your problem is Scale 0. Check out this page: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ da-db_8rc5.asp "Precision is the number of digits in a number. Scale is the number of digits to the right of the decimal point in a number. For example, the number 123.45 has a precision of 5 and a scale of 2." If you have a precision of 18 and a scale of 0, that means you are allowed 18 digits total, but zero can be to the right of the decimal point, which would force your values to be stored as integers. Change the data type to a scale of 2 or 4 or however many digits you want to the right of the decimal point, and adjust your precision if necessary to allow the correct total number of digits. So, for example, if you want a maximum of say nine million nine hundred ninety-nine thousand nine hundred ninety-nine dollars and ninety-nine cents, you would do it like this: 999.99 = precision 9, scale 2 Dave -Original Message- From: Matthew Small [mailto:[EMAIL PROTECTED] Sent: Thursday, September 01, 2005 10:05 AM To: CF-Talk Subject: RE: Sql Server DECIMAL datatype 18. I previously had the datatype as money, but am in the process of trying decimal right now. Scale 0. These are default entries. ** The information contained in this message, including attachments, may contain privileged or confidential information that is intended to be delivered only to the person identified above. If you are not the intended recipient, or the person responsible for delivering this message to the intended recipient, ALLTEL requests that you immediately notify the sender and asks that you do not read the message or its attachments, and that you delete them without copying or sending them to anyone else. ~| 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:217094 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: Sql Server DECIMAL datatype
Your problem is Scale 0. Check out this page: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_da-db_8rc5.asp "Precision is the number of digits in a number. Scale is the number of digits to the right of the decimal point in a number. For example, the number 123.45 has a precision of 5 and a scale of 2." If you have a precision of 18 and a scale of 0, that means you are allowed 18 digits total, but zero can be to the right of the decimal point, which would force your values to be stored as integers. Change the data type to a scale of 2 or 4 or however many digits you want to the right of the decimal point, and adjust your precision if necessary to allow the correct total number of digits. So, for example, if you want a maximum of say nine million nine hundred ninety-nine thousand nine hundred ninety-nine dollars and ninety-nine cents, you would do it like this: 999.99 = precision 9, scale 2 Dave -Original Message- From: Matthew Small [mailto:[EMAIL PROTECTED] Sent: Thursday, September 01, 2005 10:05 AM To: CF-Talk Subject: RE: Sql Server DECIMAL datatype 18. I previously had the datatype as money, but am in the process of trying decimal right now. Scale 0. These are default entries. ** The information contained in this message, including attachments, may contain privileged or confidential information that is intended to be delivered only to the person identified above. If you are not the intended recipient, or the person responsible for delivering this message to the intended recipient, ALLTEL requests that you immediately notify the sender and asks that you do not read the message or its attachments, and that you delete them without copying or sending them to anyone else. ~| 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:217092 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: Sql Server DECIMAL datatype
Let's say your most-expensive item is $1000.00. You need to set your precision to the total number of digits, including the decimal portion, but excluding the decimal. Then, you set the scale to the number digits after the decimal. In this example, the precision is 6, the scale is 2. Obviously, make the precision large enough to hold your largest cost. M!ke -Original Message- From: Matthew Small [mailto:[EMAIL PROTECTED] Sent: Thursday, September 01, 2005 9:05 AM To: CF-Talk Subject: RE: Sql Server DECIMAL datatype 18. I previously had the datatype as money, but am in the process of trying decimal right now. Scale 0. These are default entries. Matthew Small Web Developer American City Business Journals 704-973-1045 [EMAIL PROTECTED] ~| 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:217091 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: Sql Server DECIMAL datatype
18. I previously had the datatype as money, but am in the process of trying decimal right now. Scale 0. These are default entries. Matthew Small Web Developer American City Business Journals 704-973-1045 [EMAIL PROTECTED] -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Thursday, September 01, 2005 10:01 AM To: CF-Talk Subject: RE: Sql Server DECIMAL datatype Do you know what the precision is set to on the column? Dave -Original Message- From: Matthew Small [mailto:[EMAIL PROTECTED] Sent: Thursday, September 01, 2005 9:57 AM To: CF-Talk Subject: OT: Sql Server DECIMAL datatype Hi, I have a strange problem using the decimal datatype in MS SQL 2000. I have a "price" field in a table that is of the decimal datatype. I have a stored procedure that should store a decimal number into the table. However, when the number is stored, it is rounded as if it were an integer. I can't figure out what's going on with it. Does anybody have any pointers on how to use this datatype? BTW, I don't want to change this datatype to anything else just yet. Thank you, Matthew Small ~| 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:217089 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: Sql Server DECIMAL datatype
Do you know what the precision is set to on the column? Dave -Original Message- From: Matthew Small [mailto:[EMAIL PROTECTED] Sent: Thursday, September 01, 2005 9:57 AM To: CF-Talk Subject: OT: Sql Server DECIMAL datatype Hi, I have a strange problem using the decimal datatype in MS SQL 2000. I have a "price" field in a table that is of the decimal datatype. I have a stored procedure that should store a decimal number into the table. However, when the number is stored, it is rounded as if it were an integer. I can't figure out what's going on with it. Does anybody have any pointers on how to use this datatype? BTW, I don't want to change this datatype to anything else just yet. Thank you, Matthew Small ~| 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:217088 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: SQL Server Decimal
Erm, I set it up with a precision of 9 and a scale of 18 (or at least that's what I thought I'd set up in EM). Paul > -Original Message- > From: Peter Stolz [mailto:[EMAIL PROTECTED]] > Sent: 07 October 2000 07:33 > To: CF-Talk > Subject: RE: SQL Server Decimal > > > The digits to the right of the decimal point were truncated because > SQL Server stores decimal numbers with precision(max total number > of digits) > and scale(max digits to the right of the decimal point). > The default scale is 0, that is no digits to the right of the > decimal point. > You can change this in EM. > > Also, the use of float data types is not recommended when storing prices, > money amounts etc. as the representation is approximate : not all numbers > can be precisely represented. > Decimal is the data type you need. > > P. > > > -Original Message- > From: Paul Johnston [mailto:[EMAIL PROTECTED]] > Sent: Friday, October 06, 2000 9:08 AM > To: CF-Talk > Subject: RE: SQL Server Decimal > > > Actually, I change the field to a float and it's fine! > > Sorry to bother everyone with that one, although I do think that > a data type > of "decimal" should be able to hold a "decimal" number! Whoever decided it > shouldn't, was a stupid person. > > Paul > > > -Original Message- > > From: Andy Ewings [mailto:[EMAIL PROTECTED]] > > Sent: 06 October 2000 13:55 > > To: CF-Talk > > Subject: RE: SQL Server Decimal > > > > > > Paul > > > > Take a step back mate..what is getting put into the SQL > table? If > > the wrong data is getting put in there run the same query > through ISQL and > > see what happens > > > > -- > > Andrew Ewings > > Project Manager > > Thoughtbubble Ltd > > -- > > > > > > -Original Message- > > From: Paul Johnston [mailto:[EMAIL PROTECTED]] > > Sent: 06 October 2000 13:44 > > To: CF-Talk > > Subject: RE: SQL Server Decimal > > > > > > By means of an example, here is some test output: > > > > -- > > TESTING > > > > Source Data > > > > Case Price: 133.8 > > Bottle Price: 11.15 > > > > Web Data > > > > Case Price: 133 > > Bottle Price: 11 > > -- > > > > Erm, I can't figure out why it does this. > > > > Here is the query to get the data in: > > > > > > INSERT INTO hr_wine > > ( > > ... > > > > ... > > , CasePrice > > , BottlePrice > > ) > > VALUES > > ( > > ... > > > > ... > > ,> value="#REReplace(DecimalFormat(caseprice),",","","all")#" > > cfsqltype="CF_SQL_DECIMAL"> > > ,> value="#REReplace(DecimalFormat(case_bottle_price),",","","all")#" > > cfsqltype="CF_SQL_DECIMAL"> > > ) > > > > Can't figure this one out. > > > > Paul > > > > > -Original Message- > > > From: Andy Ewings [mailto:[EMAIL PROTECTED]] > > > Sent: 06 October 2000 13:25 > > > To: CF-Talk > > > Subject: RE: SQL Server Decimal > > > > > > > > > Paul > > > > > > You don't need the quotes around it... > > > > > > INSERT INTO (, ) > > > VALUES (#decimal1#, #decimal2#) > > > > > > I'm not sure this is the problem that you were getting the other > > > day but if > > > this doesn't work let us know the error message you get. > > > > > > -- > > > Andrew Ewings > > > Project Manager > > > Thoughtbubble Ltd > > > -- > > > > > > > > > -Original Message- > > > From: Paul Johnston [mailto:[EMAIL PROTECTED]] > > > Sent: 06 October 2000 13:22 > > > To: CF-Talk > > > Subject: SQL Server Decimal > > > > > > > > > I am having problems putting a decimal into a SQL Server field. > > > > > > The data type is decimal and I am passing the value in with > > > DecimalFormat(#var#) with the commas taken out (so that it is > > > ju
RE: SQL Server Decimal
The digits to the right of the decimal point were truncated because SQL Server stores decimal numbers with precision(max total number of digits) and scale(max digits to the right of the decimal point). The default scale is 0, that is no digits to the right of the decimal point. You can change this in EM. Also, the use of float data types is not recommended when storing prices, money amounts etc. as the representation is approximate : not all numbers can be precisely represented. Decimal is the data type you need. P. -Original Message- From: Paul Johnston [mailto:[EMAIL PROTECTED]] Sent: Friday, October 06, 2000 9:08 AM To: CF-Talk Subject: RE: SQL Server Decimal Actually, I change the field to a float and it's fine! Sorry to bother everyone with that one, although I do think that a data type of "decimal" should be able to hold a "decimal" number! Whoever decided it shouldn't, was a stupid person. Paul > -Original Message- > From: Andy Ewings [mailto:[EMAIL PROTECTED]] > Sent: 06 October 2000 13:55 > To: CF-Talk > Subject: RE: SQL Server Decimal > > > Paul > > Take a step back mate..what is getting put into the SQL table? If > the wrong data is getting put in there run the same query through ISQL and > see what happens > > -- > Andrew Ewings > Project Manager > Thoughtbubble Ltd > -- > > > -Original Message- > From: Paul Johnston [mailto:[EMAIL PROTECTED]] > Sent: 06 October 2000 13:44 > To: CF-Talk > Subject: RE: SQL Server Decimal > > > By means of an example, here is some test output: > > -- > TESTING > > Source Data > > Case Price: 133.8 > Bottle Price: 11.15 > > Web Data > > Case Price: 133 > Bottle Price: 11 > -- > > Erm, I can't figure out why it does this. > > Here is the query to get the data in: > > > INSERT INTO hr_wine > ( > ... > > ... > , CasePrice > , BottlePrice > ) > VALUES > ( > ... > > ... > ,value="#REReplace(DecimalFormat(caseprice),",","","all")#" > cfsqltype="CF_SQL_DECIMAL"> > ,value="#REReplace(DecimalFormat(case_bottle_price),",","","all")#" > cfsqltype="CF_SQL_DECIMAL"> > ) > > Can't figure this one out. > > Paul > > > -Original Message- > > From: Andy Ewings [mailto:[EMAIL PROTECTED]] > > Sent: 06 October 2000 13:25 > > To: CF-Talk > > Subject: RE: SQL Server Decimal > > > > > > Paul > > > > You don't need the quotes around it... > > > > INSERT INTO (, ) > > VALUES (#decimal1#, #decimal2#) > > > > I'm not sure this is the problem that you were getting the other > > day but if > > this doesn't work let us know the error message you get. > > > > -- > > Andrew Ewings > > Project Manager > > Thoughtbubble Ltd > > -- > > > > > > -Original Message- > > From: Paul Johnston [mailto:[EMAIL PROTECTED]] > > Sent: 06 October 2000 13:22 > > To: CF-Talk > > Subject: SQL Server Decimal > > > > > > I am having problems putting a decimal into a SQL Server field. > > > > The data type is decimal and I am passing the value in with > > DecimalFormat(#var#) with the commas taken out (so that it is > > just xx.xx > > and no more). > > > > Exactly what SQL do I need to add in? > > > > INSERT INTO... > > > > ... > > VALUES > > ( > > #decimal1# > > , #decimal2# > > ) > > > > or > > > > INSERT INTO... > > > > ... > > VALUES > > ( > > '#decimal1#' > > , '#decimal2#' > > ) > > > > or what? > > > > Paul > > > > > > -- > > -- > > -- > > Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ > > To Unsubscribe visit > > http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or > send a message to [EMAIL PROTECTED] with 'unsubscribe' in > the body. > -- >
RE: SQL Server Decimal
Actually, I change the field to a float and it's fine! Sorry to bother everyone with that one, although I do think that a data type of "decimal" should be able to hold a "decimal" number! Whoever decided it shouldn't, was a stupid person. Paul > -Original Message- > From: Andy Ewings [mailto:[EMAIL PROTECTED]] > Sent: 06 October 2000 13:55 > To: CF-Talk > Subject: RE: SQL Server Decimal > > > Paul > > Take a step back mate..what is getting put into the SQL table? If > the wrong data is getting put in there run the same query through ISQL and > see what happens > > -- > Andrew Ewings > Project Manager > Thoughtbubble Ltd > -- > > > -Original Message- > From: Paul Johnston [mailto:[EMAIL PROTECTED]] > Sent: 06 October 2000 13:44 > To: CF-Talk > Subject: RE: SQL Server Decimal > > > By means of an example, here is some test output: > > -- > TESTING > > Source Data > > Case Price: 133.8 > Bottle Price: 11.15 > > Web Data > > Case Price: 133 > Bottle Price: 11 > -- > > Erm, I can't figure out why it does this. > > Here is the query to get the data in: > > > INSERT INTO hr_wine > ( > ... > > ... > , CasePrice > , BottlePrice > ) > VALUES > ( > ... > > ... > ,value="#REReplace(DecimalFormat(caseprice),",","","all")#" > cfsqltype="CF_SQL_DECIMAL"> > ,value="#REReplace(DecimalFormat(case_bottle_price),",","","all")#" > cfsqltype="CF_SQL_DECIMAL"> > ) > > Can't figure this one out. > > Paul > > > -Original Message- > > From: Andy Ewings [mailto:[EMAIL PROTECTED]] > > Sent: 06 October 2000 13:25 > > To: CF-Talk > > Subject: RE: SQL Server Decimal > > > > > > Paul > > > > You don't need the quotes around it... > > > > INSERT INTO (, ) > > VALUES (#decimal1#, #decimal2#) > > > > I'm not sure this is the problem that you were getting the other > > day but if > > this doesn't work let us know the error message you get. > > > > -- > > Andrew Ewings > > Project Manager > > Thoughtbubble Ltd > > -- > > > > > > -Original Message- > > From: Paul Johnston [mailto:[EMAIL PROTECTED]] > > Sent: 06 October 2000 13:22 > > To: CF-Talk > > Subject: SQL Server Decimal > > > > > > I am having problems putting a decimal into a SQL Server field. > > > > The data type is decimal and I am passing the value in with > > DecimalFormat(#var#) with the commas taken out (so that it is > > just xx.xx > > and no more). > > > > Exactly what SQL do I need to add in? > > > > INSERT INTO... > > > > ... > > VALUES > > ( > > #decimal1# > > , #decimal2# > > ) > > > > or > > > > INSERT INTO... > > > > ... > > VALUES > > ( > > '#decimal1#' > > , '#decimal2#' > > ) > > > > or what? > > > > Paul > > > > > > -- > > -- > > -- > > Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ > > To Unsubscribe visit > > http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or > send a message to [EMAIL PROTECTED] with 'unsubscribe' in > the body. > -- > > Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ > To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body. -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body. -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body. -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
RE: SQL Server Decimal
Paul Take a step back mate..what is getting put into the SQL table? If the wrong data is getting put in there run the same query through ISQL and see what happens -- Andrew Ewings Project Manager Thoughtbubble Ltd -- -Original Message- From: Paul Johnston [mailto:[EMAIL PROTECTED]] Sent: 06 October 2000 13:44 To: CF-Talk Subject: RE: SQL Server Decimal By means of an example, here is some test output: -- TESTING Source Data Case Price: 133.8 Bottle Price: 11.15 Web Data Case Price: 133 Bottle Price: 11 -- Erm, I can't figure out why it does this. Here is the query to get the data in: INSERT INTO hr_wine ( ... ... , CasePrice , BottlePrice ) VALUES ( ... ... , , ) Can't figure this one out. Paul > -Original Message- > From: Andy Ewings [mailto:[EMAIL PROTECTED]] > Sent: 06 October 2000 13:25 > To: CF-Talk > Subject: RE: SQL Server Decimal > > > Paul > > You don't need the quotes around it... > > INSERT INTO (, ) > VALUES (#decimal1#, #decimal2#) > > I'm not sure this is the problem that you were getting the other > day but if > this doesn't work let us know the error message you get. > > -- > Andrew Ewings > Project Manager > Thoughtbubble Ltd > -- > > > -Original Message- > From: Paul Johnston [mailto:[EMAIL PROTECTED]] > Sent: 06 October 2000 13:22 > To: CF-Talk > Subject: SQL Server Decimal > > > I am having problems putting a decimal into a SQL Server field. > > The data type is decimal and I am passing the value in with > DecimalFormat(#var#) with the commas taken out (so that it is > just xx.xx > and no more). > > Exactly what SQL do I need to add in? > > INSERT INTO... > > ... > VALUES > ( > #decimal1# > , #decimal2# > ) > > or > > INSERT INTO... > > ... > VALUES > ( > '#decimal1#' > , '#decimal2#' > ) > > or what? > > Paul > > > -- > -- > -- > Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ > To Unsubscribe visit > http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or > send a message to [EMAIL PROTECTED] with 'unsubscribe' in > the body. > -- > > Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ > To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body. -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body. -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
RE: SQL Server Decimal
By means of an example, here is some test output: -- TESTING Source Data Case Price: 133.8 Bottle Price: 11.15 Web Data Case Price: 133 Bottle Price: 11 -- Erm, I can't figure out why it does this. Here is the query to get the data in: INSERT INTO hr_wine ( ... ... , CasePrice , BottlePrice ) VALUES ( ... ... , , ) Can't figure this one out. Paul > -Original Message- > From: Andy Ewings [mailto:[EMAIL PROTECTED]] > Sent: 06 October 2000 13:25 > To: CF-Talk > Subject: RE: SQL Server Decimal > > > Paul > > You don't need the quotes around it... > > INSERT INTO (, ) > VALUES (#decimal1#, #decimal2#) > > I'm not sure this is the problem that you were getting the other > day but if > this doesn't work let us know the error message you get. > > -- > Andrew Ewings > Project Manager > Thoughtbubble Ltd > -- > > > -Original Message- > From: Paul Johnston [mailto:[EMAIL PROTECTED]] > Sent: 06 October 2000 13:22 > To: CF-Talk > Subject: SQL Server Decimal > > > I am having problems putting a decimal into a SQL Server field. > > The data type is decimal and I am passing the value in with > DecimalFormat(#var#) with the commas taken out (so that it is > just xx.xx > and no more). > > Exactly what SQL do I need to add in? > > INSERT INTO... > > ... > VALUES > ( > #decimal1# > , #decimal2# > ) > > or > > INSERT INTO... > > ... > VALUES > ( > '#decimal1#' > , '#decimal2#' > ) > > or what? > > Paul > > > -- > -- > -- > Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ > To Unsubscribe visit > http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or > send a message to [EMAIL PROTECTED] with 'unsubscribe' in > the body. > -- > > Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ > To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body. -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
RE: SQL Server Decimal
Paul Try running the query in ISQL (query Analyser) replacing the variables with the values. See what gets inserted into the table. If the correct values get inserted the put some debugging code in your cfm templates to output the values of the variables before you run the query so you can see exactly what is being passed in -- Andrew Ewings Project Manager Thoughtbubble Ltd -- -Original Message- From: Paul Johnston [mailto:[EMAIL PROTECTED]] Sent: 06 October 2000 13:39 To: CF-Talk Subject: RE: SQL Server Decimal Either way it doesn't work (I've tried both and it's just wierd). There isn't an error as such. The field just discard everything after the dot. Should I be using a scientific format ie 361*10^-2 or something like that? Paul PS I even tried and that didn't work either. > -Original Message- > From: Andy Ewings [mailto:[EMAIL PROTECTED]] > Sent: 06 October 2000 13:25 > To: CF-Talk > Subject: RE: SQL Server Decimal > > > Paul > > You don't need the quotes around it... > > INSERT INTO (, ) > VALUES (#decimal1#, #decimal2#) > > I'm not sure this is the problem that you were getting the other > day but if > this doesn't work let us know the error message you get. > > -- > Andrew Ewings > Project Manager > Thoughtbubble Ltd > -- > > > -Original Message- > From: Paul Johnston [mailto:[EMAIL PROTECTED]] > Sent: 06 October 2000 13:22 > To: CF-Talk > Subject: SQL Server Decimal > > > I am having problems putting a decimal into a SQL Server field. > > The data type is decimal and I am passing the value in with > DecimalFormat(#var#) with the commas taken out (so that it is > just xx.xx > and no more). > > Exactly what SQL do I need to add in? > > INSERT INTO... > > ... > VALUES > ( > #decimal1# > , #decimal2# > ) > > or > > INSERT INTO... > > ... > VALUES > ( > '#decimal1#' > , '#decimal2#' > ) > > or what? > > Paul > > > -- > -- > -- > Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ > To Unsubscribe visit > http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or > send a message to [EMAIL PROTECTED] with 'unsubscribe' in > the body. > -- > > Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ > To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body. -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body. -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
RE: SQL Server Decimal
Either way it doesn't work (I've tried both and it's just wierd). There isn't an error as such. The field just discard everything after the dot. Should I be using a scientific format ie 361*10^-2 or something like that? Paul PS I even tried and that didn't work either. > -Original Message- > From: Andy Ewings [mailto:[EMAIL PROTECTED]] > Sent: 06 October 2000 13:25 > To: CF-Talk > Subject: RE: SQL Server Decimal > > > Paul > > You don't need the quotes around it... > > INSERT INTO (, ) > VALUES (#decimal1#, #decimal2#) > > I'm not sure this is the problem that you were getting the other > day but if > this doesn't work let us know the error message you get. > > -- > Andrew Ewings > Project Manager > Thoughtbubble Ltd > -- > > > -Original Message- > From: Paul Johnston [mailto:[EMAIL PROTECTED]] > Sent: 06 October 2000 13:22 > To: CF-Talk > Subject: SQL Server Decimal > > > I am having problems putting a decimal into a SQL Server field. > > The data type is decimal and I am passing the value in with > DecimalFormat(#var#) with the commas taken out (so that it is > just xx.xx > and no more). > > Exactly what SQL do I need to add in? > > INSERT INTO... > > ... > VALUES > ( > #decimal1# > , #decimal2# > ) > > or > > INSERT INTO... > > ... > VALUES > ( > '#decimal1#' > , '#decimal2#' > ) > > or what? > > Paul > > > -- > -- > -- > Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ > To Unsubscribe visit > http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or > send a message to [EMAIL PROTECTED] with 'unsubscribe' in > the body. > -- > > Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ > To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body. -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
RE: SQL Server Decimal
Paul You don't need the quotes around it... INSERT INTO (, ) VALUES (#decimal1#, #decimal2#) I'm not sure this is the problem that you were getting the other day but if this doesn't work let us know the error message you get. -- Andrew Ewings Project Manager Thoughtbubble Ltd -- -Original Message- From: Paul Johnston [mailto:[EMAIL PROTECTED]] Sent: 06 October 2000 13:22 To: CF-Talk Subject: SQL Server Decimal I am having problems putting a decimal into a SQL Server field. The data type is decimal and I am passing the value in with DecimalFormat(#var#) with the commas taken out (so that it is just xx.xx and no more). Exactly what SQL do I need to add in? INSERT INTO... ... VALUES ( #decimal1# , #decimal2# ) or INSERT INTO... ... VALUES ( '#decimal1#' , '#decimal2#' ) or what? Paul -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body. -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
