declare decimal in C#

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'.
LVL 1
doramail05Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kevin CrossChief Technology OfficerCommented:
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
doramail05Author Commented:
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
Kevin CrossChief Technology OfficerCommented:
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
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

RiteshShahCommented:
try using this:

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


rather than

dsselectSOLfields.Tables[0].Rows[m]["sol_date_stamp"]
0
doramail05Author Commented:
the (double)calculated_subtotal  still aint work
0
RiteshShahCommented:
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
RiteshShahCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
doramail05Author Commented:
cannot work

i think decimal cannot accept string

0
RiteshShahCommented:
show me insert string and create table script
0
doramail05Author Commented:
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
RiteshShahCommented:
I can't see complete INSERT statement,
0
doramail05Author Commented:
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
doramail05Author Commented:
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
RiteshShahCommented:
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
doramail05Author Commented:
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
Kevin CrossChief Technology OfficerCommented:
The colon itself is wrong.

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

Should be:

'10/22/2008 12:00:00 AM'
0
RiteshShahCommented:
you can see proper date in my insert script I have provided, that is completely working script, I have inserted record.
0
doramail05Author Commented:
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
RiteshShahCommented:
can you run query in SSMS directly?
0
doramail05Author Commented:
i think it will work, in another 12 hours i will be in the office
0
RiteshShahCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.