Click here to Skip to main content
11,577,494 members (43,787 online)
Rate this: bad
Please Sign up or sign in to vote.
I want to save amount in my table like(5000.00), even if user enter the amount 5000 in the text Box. For that what datatype should i use(i have used numeric(18,2)) and using stored procedure. But it saving only int value, so how to save amount like 5000.00 and how to make parameter. My Example of code is below.
SqlConnection con = new SqlConnection();
con.ConnectionString = ConfigurationManager.AppSettings.Get("connectionstring");
        SqlCommand cmd = new SqlCommand("Proc_InsertPurchaseDetails", con);
        cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@FirmName", SqlDbType.NVarChar, 50).Value = ddlFirmName.SelectedItem.Text;
            cmd.Parameters.Add("@Place", SqlDbType.NVarChar, 50).Value = lblPlace.Text;
            cmd.Parameters.Add("@PurchaseDate", SqlDbType.DateTime).Value = txtPurchaseDate.Text;
            cmd.Parameters.Add("@BillNo", SqlDbType.NVarChar, 50).Value = txtBillNo.Text.Trim();
            cmd.Parameters.Add("@Amount",SqlDbType.Decimal).Value = txtAmount.Text.Trim();
            cmd.Parameters.Add("@Category", SqlDbType.NVarChar, 50).Value = rblCategory.SelectedValue.ToString();
            cmd.Parameters.Add("@CreatedBy", SqlDbType.NVarChar, 50).Value = lblUserName.Text;
            cmd.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = DateTime.Now.ToString();
            cmd.Parameters.Add("@Msg", SqlDbType.NVarChar, 50).Direction = ParameterDirection.Output;
            lblMsg.Text = cmd.Parameters["@Msg"].Value.ToString();
        catch (Exception Ex)
            lblMsg.Text = Ex.Message;
ALTER PROCEDURE [dbo].[Proc_InsertPurchaseDetails]
@FirmName NVarChar(50),
@Place NVarChar(50),
@PurchaseDate DateTime,
@BillNo NVarChar(50),
@Amount Decimal(18,0),
@Category NVarChar(50),
@CreatedBy NVarChar(50),
@CreatedDate DateTime,
@Msg nvarchar(50) Output
	insert into Table_Purchase(FirmName,Place,PurchaseDate,BillNo,Amount,Category,CreatedBy,CreatedDate) 
	set @Msg='Purchase Saved...'
Can any one help me to short out the issues, Thanks in advance
Posted 2-Oct-12 2:42am
Namit KB1.3K
Edited 2-Oct-12 2:46am
gladiatron at 2-Oct-12 9:07am
can u not use money type or decimal in SQL server?
Namit KB at 2-Oct-12 9:21am
If i will use money, it will take 4 digit after decimal. If i am using decimal it is possible then how to use it, will it work suggest me. Thank u
Rate this: bad
Please Sign up or sign in to vote.

Solution 1

The input parameter @amount of your stored proc must be changed from

the "2" in "(18,2)" is the number of representative digits after the decimal separator. When saying "(18,0)" you only accept integers.
Namit KB at 4-Oct-12 13:54pm
Rate this: bad
Please Sign up or sign in to vote.

Solution 2

on second thoughts, why do you even want to do it? You can let it get saved without ".00" but when you read the values to show on the GUI, set the culture to the local culture and format the amount field to display currency. It will automatically display figures as "5000.00" for e.g. "£5000.00".

Just as a safety measure I would recommend this:

cmd.Parameters.Add("@Amount",SqlDbType.Decimal).Value = txtAmount.Text.Trim();

to be changed to this:

decimal val;
decimal.TryParse(txtAmount.Text.Trim(), out val);
cmd.Parameters.Add("@Amount",SqlDbType.Decimal).Value = val;

this makes sure that if garbage value is entered in the textbox, then "0.00" should get stored rather than the app crashing.
Namit KB at 4-Oct-12 13:54pm

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 DamithSL 180
1 OriginalGriff 115
2 Richard MacCutchan 100
3 Abhinav S 90
4 Peter Leow 80
0 OriginalGriff 820
1 Sergey Alexandrovich Kryukov 681
2 Abhinav S 528
3 F-ES Sitecore 420
4 Suvendu Shekhar Giri 375

Advertise | Privacy | Mobile
Web03 | 2.8.150603.1 | Last Updated 2 Oct 2012
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100