Click here to Skip to main content
Rate this: bad
good
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;
        try
        {
            cmd.Connection.Open();
            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;
            cmd.ExecuteNonQuery();
            cmd.Connection.Close();
            lblMsg.Text = cmd.Parameters["@Msg"].Value.ToString();
            //cmd.Connection
        }
        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
 
AS
BEGIN
 
	insert into Table_Purchase(FirmName,Place,PurchaseDate,BillNo,Amount,Category,CreatedBy,CreatedDate) 
					values(@FirmName,@Place,@PurchaseDate,@BillNo,@Amount,@Category,@CreatedBy,@CreatedDate)
	set @Msg='Purchase Saved...'
END
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
JF201553.2K
v3
Comments
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
good
Please Sign up or sign in to vote.

Solution 1

The input parameter @amount of your stored proc must be changed from
decimal(18,0)
to
decimal(18,2)
 
the "2" in "(18,2)" is the number of representative digits after the decimal separator. When saying "(18,0)" you only accept integers.
  Permalink  
v4
Comments
Namit KB at 4-Oct-12 13:54pm
   
Thanks
Rate this: bad
good
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:
 
Quote:
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.
  Permalink  
Comments
Namit KB at 4-Oct-12 13:54pm
   
Thanks

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

  Print Answers RSS
0 Maciej Los 200
1 Kornfeld Eliyahu Peter 175
2 BillWoodruff 160
3 George Jonsson 140
4 OriginalGriff 126


Advertise | Privacy | Mobile
Web03 | 2.8.141015.1 | Last Updated 2 Oct 2012
Copyright © CodeProject, 1999-2014
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