How to save and make parameter string in my .cs page and save decimal value in tables

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

VS2005

, +

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.

C#

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;
        }

----------------------------------------------------------------------------------

SQL

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 KB

Updated 2-Oct-12 2:46am

JF2015

v3

Add a Solution

Comments

I.explore.code 2-Oct-12 9:07am

can u not use money type or decimal in SQL server?

Namit KB 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

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Guirec · Accepted Answer · 2012-10-02T03:18:00

Solution 1

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

SQL

decimal(18,0)

to

SQL

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.

Posted 2-Oct-12 3:18am

Guirec

Updated 2-Oct-12 3:23am

v4

Comments

Namit KB 4-Oct-12 13:54pm

Thanks

I.explore.code · Accepted Answer · 2012-10-02T03:23:00

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 "<currency_symbol>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:

C#

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.