Convert string to decimal number

Israel

Hi,

I do try many time to save decimal number into my database but there is erro saying that its cannot save string number. My database has two column (debit and credit) with number type. But when click to save its doesnt and stuck on line I coulored in red.

if (conn.State != ConnectionState.Open)
conn.Open();
SqlCommand comm = new SqlCommand();
comm.Connection = conn;
SqlParameter debit = new SqlParameter("@debit", SqlDbType.Decimal);
SqlParameter credit = new SqlParameter("@credit", SqlDbType.Decimal);
comm.Parameters.Add(debit);
comm.Parameters.Add(credit);
debit.Value = Convert.ToDecimal(txtDebit.Text, CultureInfo.InvariantCulture); //its stucks here
credit.Value = Convert.ToDecimal(txtCredit.Text, CultureInfo.InvariantCulture); //its stucks here
comm.Connection = conn;
comm.CommandText = "insert into pay ([debit],[credit])values(@debit,@credit)";
{
if (MessageBox.Show("Are you sure to save?", "Save", MessageBoxButtons.YesNo) == DialogResult.Yes)
{
try
{
comm.ExecuteNonQuery();
}
finally
{
conn.Close();
}

10 Replies

Know the answer? Post it — somebody with the same question will find it here.

Jignesh Kumar5y ago
Accepted answer
Hi,

Please use this one and restrict your input and allow only decimal values from textbox.
if (conn.State != ConnectionState.Open)
{
            conn.Open();
            SqlCommand comm = new SqlCommand();
            comm.Connection = conn;
            comm.Parameters.Add("@debit", SqlDbType.Decimal).Value = System.Convert.ToDecimal(txtDebit.Text);
            comm.Parameters.Add("@credit", SqlDbType.Decimal).Value = System.Convert.ToDecimal(txtCredit.Text);
            comm.Connection = conn;

            comm.CommandText = "insert into pay ([debit],[credit])values(@debit,@credit)";

                if (MessageBox.Show("Are you sure to save?", "Save", MessageBoxButtons.YesNo) == DialogResult.Yes)
                {
                    try
                    {
                        comm.ExecuteNonQuery();
                    }

                    finally
                    {
                        conn.Close();
                    }
                }

}
Israel5y ago
Your opinion was really helpful. Many thanx to:
@Sachin
@Nitin
@Amit
Then the best response and simple one resolving my problem without bugg(s) is Jignesh. Thank you again.
Sachin Singh5y ago
use NumberStyles.allowThousands
if(!decimal.TryParse(txtDebit.Text, NumberStyles.AllowThousands, CultureInfo.CurrentCulture, out debit))
Israel5y ago
@Sachin Your codes doesnt save the number inserted into textbox. Its shows only 0,00 into Database after saving. There is no bugg just what I am explaning now.
Nitin Sontakke5y ago
Sachin's answer should solve the problem.

You were entering comma which wouldn't be accepted by the database. It must be parsed to be decimal.
Amit Gupta5y ago
Please change your datatype to decimal(18,2) in db, and your issue will get resolved.
Israel5y ago
@Nitin
'failed to convert parameter value of string to decimal'
@Amit
debit - numeric(18,2)
credit - numeric(18,2)

But sometime its multiple itself by 000
For example if I insert into debit 150,000 when you save its show into database 150,000,000
Sachin Singh5y ago
in sql server you have two options
1. numeric(p,s) ex:- numeric(10,3) or
2. decimal(p,s) ex:- decimal(10,4)
if you are using right p and s in your database then in your code there is no chance to get error.
Note :-
p is the number of total digits your decimal can have.
s is the max number of digits you can have after the decimal point.
better is to use decimal as database coulmn then in your code do like below
decimal debit,credit;
if(!decimal.TryParse(txtDebit.Text, NumberStyles.None, CultureInfo.CurrentCulture, out debit))
{
    // Message for your user -- Invalid number --
}
if(!decimal.TryParse(txtCredit.Text, NumberStyles.None, CultureInfo.CurrentCulture, out credit))
{
    // Message for your user -- Invalid number --
}
d


using (SqlConnection con = new SqlConnection(cs))
                {
                    SqlCommand cmd = new SqlCommand("insert into pay ([debit],[credit])values(@debit,@credit)", con);


                    cmd.Parameters.AddWithValue("@debit", debit);
                    cmd.Parameters.AddWithValue("@credit", credit);

                    con.Open();
                    cmd.ExecuteNonQuery();

                }
Nitin Sontakke5y ago
Please post the error that you are receiving, the relevant part of the create table script of the pay table and the sample values that you are entering in the text boxes when it gives error.
Amit Gupta5y ago
Whats the datatype in db and share the value of txtDebit.Text value