Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

declare decimal in C#

Posted on 2009-07-14
21
Medium Priority
?
1,468 Views
Last Modified: 2012-05-07
i have something like 100.50

i declare it using
public static decimal calculated_sub_total = 0.00M;

i still get 'input string is not in the correct format'.
0
Comment
Question by:doramail05
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 9
  • 3
21 Comments
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 24856031
What is the code where you are passing the input string to the decimal variable?  What is the piece of code you are getting the error from?
0
 
LVL 1

Author Comment

by:doramail05
ID: 24856114
when the calculated sub_total total ups and it is 100.00,
but when the calculated sub_total total ups and it is 100.50 and insert into table , it says 'input string was not in correct format'.

 SQL Server sub_total is in numeric (18,2)
public static decimal calculated_sub_total = 0.00M;
 
SqlCommand sqlcmd_insert_into_oneuSO = sqlsourceconnextract.CreateCommand();
                                        sqlcmd_insert_into_oneuSO.CommandText = "USE " + txtSourceDatabase.Text + "; INSERT INTO dbo.ONEU_Sales_Order (sale_id, sale_order_no, sale_order_invoice, sale_datetime, sale_status, sub_total, discount_percent, discount_amount, tax_percent, tax_amount, service_charge_percent, service_charge_amount, grand_total, line_type, chg_type, whse_code, upload_status) " +
                                    " VALUES ('" + generated_saleid + "', NULL, NULL, '" + Convert.ToDateTime(dsselectSOLfields.Tables[0].Rows[m]["sol_date_stamp"])
                                    + "', '11', " + calculated_sub_total + ", 0, " + dsselectSOLfields.Tables[0].Rows[m]["sol_line_amount"].ToString() + ", 0, 0, 0, 0, 0, '', '', '', 0)";

Open in new window

0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 24856144
Maybe it is precision issue, try like this:
public static decimal calculated_sub_total = 0.00M;
 
SqlCommand sqlcmd_insert_into_oneuSO = sqlsourceconnextract.CreateCommand();
                                        sqlcmd_insert_into_oneuSO.CommandText = "USE " + txtSourceDatabase.Text + "; INSERT INTO dbo.ONEU_Sales_Order (sale_id, sale_order_no, sale_order_invoice, sale_datetime, sale_status, sub_total, discount_percent, discount_amount, tax_percent, tax_amount, service_charge_percent, service_charge_amount, grand_total, line_type, chg_type, whse_code, upload_status) " +
                                    " VALUES ('" + generated_saleid + "', NULL, NULL, '" + Convert.ToDateTime(dsselectSOLfields.Tables[0].Rows[m]["sol_date_stamp"])
                                    + "', '11', " + (double)calculated_sub_total + ", 0, " + dsselectSOLfields.Tables[0].Rows[m]["sol_line_amount"].ToString() + ", 0, 0, 0, 0, 0, '', '', '', 0)";

Open in new window

0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 31

Expert Comment

by:RiteshShah
ID: 24856146
try using this:

dsselectSOLfields.Tables[0].Rows[m]["sol_date_stamp"].ToString()


rather than

dsselectSOLfields.Tables[0].Rows[m]["sol_date_stamp"]
0
 
LVL 1

Author Comment

by:doramail05
ID: 24856299
the (double)calculated_subtotal  still aint work
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24856817
ok than do one thing. comment your SQLCommand code right now, execute below code which will print Insert statement. show it to me and also show me your Create Table script for table dbo.ONEU_Sales_Order.


string strSQL;
        strSQL = "USE " + txtSourceDatabase.Text + "; INSERT INTO dbo.ONEU_Sales_Order (sale_id, sale_order_no, sale_order_invoice,";
        strSQL=strSQL + " sale_datetime, sale_status, sub_total, discount_percent, discount_amount, tax_percent, tax_amount, ";
        strSQL = strSQL + " service_charge_percent, service_charge_amount, grand_total, line_type, chg_type, whse_code, upload_status) ";
        strSQL = strSQL + " VALUES ('" + generated_saleid + "', NULL, NULL, '";
        strSQL = strSQL + Convert.ToDateTime(dsselectSOLfields.Tables[0].Rows[m]["sol_date_stamp"]);
        strSQL = strSQL + " ', '11', " + (double)calculated_sub_total + ", 0, ";
        strSQL=strSQL + dsselectSOLfields.Tables[0].Rows[m]["sol_line_amount"].ToString() + ", 0, 0, 0, 0, 0, '', '', '', 0)";
        Response.Write(strSQL);

Open in new window

0
 
LVL 31

Accepted Solution

by:
RiteshShah earned 1500 total points
ID: 24856824
one wild guess, this may work, try this one too:



public static decimal calculated_sub_total = 0.00M;
 
SqlCommand sqlcmd_insert_into_oneuSO = sqlsourceconnextract.CreateCommand();
                                        sqlcmd_insert_into_oneuSO.CommandText = "USE " + txtSourceDatabase.Text + "; INSERT INTO dbo.ONEU_Sales_Order (sale_id, sale_order_no, sale_order_invoice, sale_datetime, sale_status, sub_total, discount_percent, discount_amount, tax_percent, tax_amount, service_charge_percent, service_charge_amount, grand_total, line_type, chg_type, whse_code, upload_status) " +
                                    " VALUES ('" + generated_saleid + "', NULL, NULL, '" + Convert.ToDateTime(dsselectSOLfields.Tables[0].Rows[m]["sol_date_stamp"])
                                    + "', '11', " + Convert.ToString(calculated_sub_total) + ", 0, " + dsselectSOLfields.Tables[0].Rows[m]["sol_line_amount"].ToString() + ", 0, 0, 0, 0, 0, '', '', '', 0)";

Open in new window

0
 
LVL 1

Author Comment

by:doramail05
ID: 24857092
cannot work

i think decimal cannot accept string

0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24857096
show me insert string and create table script
0
 
LVL 1

Author Comment

by:doramail05
ID: 24857280
the sub total is changed to decimal in design mode.

the 'response' keyword does not apply, i copy and paste from the html visualiser.

i tried the date time convert.toDatetime(... .ToString()) but got the same error 'input format.. ";

notice that the previous record did insert into table , like 0.00, once it has 0.50 ,  that does not insert
USE [RetailDB]
GO
/****** Object:  Table [dbo].[ONEU_Sales_Order]    Script Date: 07/14/2009 09:45:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ONEU_Sales_Order](
	[sale_id] [nvarchar](50) NOT NULL,
	[sale_order_no] [varchar](max) NULL,
	[sale_order_invoice] [varchar](max) NULL,
	[sale_datetime] [datetime] NOT NULL,
	[sale_status] [nvarchar](max) NOT NULL,
	[sub_total] [money] NOT NULL,
	[discount_percent] [float] NULL,
	[discount_amount] [money] NULL,
	[tax_percent] [float] NULL,
	[tax_amount] [money] NULL,
	[service_charge_percent] [float] NULL,
	[service_charge_amount] [money] NULL,
	[grand_total] [money] NOT NULL,
	[line_type] [varchar](max) NULL,
	[chg_type] [varchar](max) NULL,
	[whse_code] [varchar](max) NULL,
	[upload_status] [int] NULL,
 CONSTRAINT [PK_1U_Sales_Order] PRIMARY KEY CLUSTERED 
(
	[sale_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
 
GO
SET ANSI_PADDING OFF
 
 
USE RetailDB; INSERT INTO dbo.ONEU_Sales_Order (sale_id, sale_order_no, sale_order_invoice, sale_datetime, sale_status, sub_total, discount_percent, discount_amount, tax_percent, tax_amount, service_charge_percent, service_charge_amount, grand_total, line_type, chg_type, whse_code, upload_status) VALUES ('SO53000000000002', NULL, NULL, '

Open in new window

0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24857617
I can't see complete INSERT statement,
0
 
LVL 1

Author Comment

by:doramail05
ID: 24857659
i write from msgbox, hope that declare

one thing is that i have 'money' in discount_amount, but i put it as
dsselectSOLfields.Tables[0].Rows[m]["sol_line_amount"].ToString()
it write to the field as 5530.0000 .

USE RetailDB; INSERT INTO dbo.ONEU_Sales_Order(sale_id, sale_order_no, sale_order_invoice, sale_datetime, sale_status, sub_total, discount_percent, discount amount, tax_percent, tax_amount, service_charge_amount, grand_total, line_type, chg_type, whse_code, upload_status) VALUES ('SO5300000000002', NULL, NULL, '10/22/2008 :12:00:00 AM', '11', 649, 0, 649, 0, 0 , 0, 0, 0 , ' ', ' ', ' ', 0)
0
 
LVL 1

Author Comment

by:doramail05
ID: 24857680
previously it wrote 11 rows,

now below only wrote 1 row.
 SqlCommand sqlcmd_insert_into_oneuSO = sqlsourceconnextract.CreateCommand();
                                                    sqlcmd_insert_into_oneuSO.CommandText = "USE " + txtSourceDatabase.Text + "; INSERT INTO dbo.ONEU_Sales_Order (sale_id, sale_order_no, sale_order_invoice, sale_datetime, sale_status, sub_total, discount_percent, discount_amount, tax_percent, tax_amount, service_charge_percent, service_charge_amount, grand_total, line_type, chg_type, whse_code, upload_status) " +
                                                " VALUES ('" + generated_saleid + "', NULL, NULL, '" + Convert.ToDateTime(dsselectSOLfields.Tables[0].Rows[m]["sol_date_stamp"])
                                                + "', '11', " + Convert.ToString(calculated_sub_total) + ", 0, " + dsselectSOLfields.Tables[0].Rows[m]["sol_line_amount"].ToString() + ", 0, 0, 0, 0, 0, '', '', '', 0)";

Open in new window

0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24857720
if you see your select statement given above, I found three mistakes,

1.) "_" missing in Discount_amount field
2.) one more field in VALUES clause
3.) wrong date format, ":" before 12 is wrong.

if you correct these three mistakes, you will be able to insert records as I did just now.


have a look



INSERT INTO dbo.ONEU_Sales_Order(
 
sale_id, 
sale_order_no, 
sale_order_invoice, 
sale_datetime, 
sale_status, 
sub_total, 
discount_percent, 
discount_amount, --you were MISSING _ sign here
tax_percent, 
tax_amount, 
service_charge_amount, 
grand_total, 
line_type, 
chg_type, 
whse_code, 
upload_status
 
) VALUES (
 
'SO5300000000002', 
NULL, 
NULL, 
'10/22/2008 12:00:00 AM', 
'11', 
649, 
0, 
649, 
0, 
0 , 
0, 
0, 
0 , 
' ', 
' ', 
0
) 

Open in new window

0
 
LVL 1

Author Comment

by:doramail05
ID: 24857794
3.) wrong date format, ":" before 12 is wrong.

10/22/2008 is the default date format in sql server i think, that's US time,

how should the date format be and the code's one?
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 24857810
The colon itself is wrong.

'10/22/2008 :12:00:00 AM'

Should be:

'10/22/2008 12:00:00 AM'
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24857815
you can see proper date in my insert script I have provided, that is completely working script, I have inserted record.
0
 
LVL 1

Author Comment

by:doramail05
ID: 24858292
i prepost the comment earlier and wondering it didnt get thru, i post again for my findings

i firstly changed the data type of datetime to varchar(max), then pass in NULL in code
still it has the same error 'input string was not in correct format'

secondly, i replaces all money and float to varchar(max), then pass in same all NULL, still getting

then finally, i left sale_id only and all varchar(max) NULL.. still getting error and only write in 1 line into the table..  
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24858338
can you run query in SSMS directly?
0
 
LVL 1

Author Comment

by:doramail05
ID: 24858927
i think it will work, in another 12 hours i will be in the office
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24859377
if it is working from SSMS than it has no reason to not work from .NET. I tried the query I gave you and it is working with the table you gave above.
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question