Error while Inserting Null (empty TextBox)values to SQL Server through C# code [duplicate]

Question

I am inserting some values into SQL Server using C# code. I have created my RemainingPayment column in the database as Decimal(18,2) datatype and also allowed null. But when I execute my query from C#, it complains about:

Incorrect syntax near ,

It's important to note that I get that error only for the RemainingPayment column that has Decimal(18,2) data type. For another column that has nvarchar datatype, it's working fine.

Here is my code:

try
{
    using (SqlConnection con = new SqlConnection(ConStr))
    {
        string query = "INSERT INTO tblExpEntry(RemainingPayment, CHQNo, TransactionID) VALUES (" + tbRemainingPayment.Text + ", '"+tbCHQNo.Text+"', '"+lbl_ID.Text+"')";

        using (SqlCommand cmd = new SqlCommand(query,con))
        {
            cmd.CommandType = CommandType.Text;
            con.Open();
            cmd.ExecuteNonQuery();

            MessageBox.Show("Transaction Successful.", "Created");
            generateTransactionID();
        }
    }
}
catch (Exception ex) 
{
    MessageBox.Show(ex.Message);  
}

Use parameters rather than stuffing the values into the query string. — Gordon Linoff
– Gordon Linoff, Commented Feb 2, 2016 at 12:31
It will also help avoid the kinds of errors you are seeing that are sometimes caused by forgetting quotes and all the string problems that you can create. This method is also a MASSIVE SQL injection vulnerability. — Lukos
– Lukos, Commented Feb 2, 2016 at 12:32
You didn't even specify TotalPayment column in your code. What are the types of RemainingPayment, CHQNo and TransactionID columns? — Soner Gönül
– Soner Gönül, Commented Feb 2, 2016 at 12:33
No, you think that all is working fine for the nvarchar datatype. Try to add a value that contains a single quote to your textboxes used to fill a nvarchar column — Steve
– Steve, Commented Feb 2, 2016 at 12:35

Steve · Accepted Answer · 2016-02-02 13:00:21Z

You should use parameters to execute an sql command and not string concatenation. A parameter contains a DataType field that explicitly defines the type passed to the database engine and doesn't allow a conversion from a string to introduce characters that are not valid. For example your locale settings could define the comma as separator for decimals, but your database wants a point. The implicit call at ToString method used to concatenate the textbox value to your string produces an invalid input (and, of course, also a missing value in the textbox)

With parameters your code will be

decimal remainingPayment;
if(!decimal.TryParse(tbRemainingPayment.Text, out remainingPayment))
{
    // Add a message for your user and return, or continue 
    // inserting the default value for a decimal (that's zero)
}

using (SqlConnection con = new SqlConnection(ConStr))
{
    string query = @"INSERT INTO tblExpEntry
    (RemainingPayment , CHQNo, TransactionID)
     VALUES(@total, @chq, @transid)";

    using (SqlCommand cmd = new SqlCommand(query,con))
    {
        con.Open();
        cmd.Parameters.Add("@total", SqlDbType.Decimal).Value = remainingPayment;
        cmd.Parameters.Add("@chq", SqlDbType.NVarChar).Value =  tbCHQNo.Text;
        cmd.Parameters.Add("@transid", SqlDbType.NVarChar).Value =  lbl_ID.Text;
        cmd.ExecuteNonQuery();

        MessageBox.Show("Transaction Successful.", "Created");
        generateTransactionID();
    }
}

when i apply this code, it says "input string was not in correct format", where is the error ?
If your textbox tbRemainingPayment doesn't contain a valid decimal value then you need to convert it with tryParse. I will update the answer
when i leave RemainingPayment textbox empty, it throws an error that input string was not in correct format. when i put ' in CHQNo textbox it also throws same error. working fine if i fill both textboxes (with correct datatype values) . error only if i remain empty

Lukos · Accepted Answer · 2016-02-02 12:38:08Z

1

If the problem is when the textbox is empty then that is your answer. You are simply putting tbRemainingPayment.Text into a string which normally would produce ...(12.23, 'whatever','whatever') but if the text box is blank, you will get ...(, 'whatever','whatever')

Parse the text value into a decimal so if it is blank, you will get zero instead of the text error.

Another reason you should NEVER create SQL like this!

answered Feb 2, 2016 at 12:38

Lukos

1,8622 gold badges15 silver badges29 bronze badges

Collectives™ on Stack Overflow

Error while Inserting Null (empty TextBox)values to SQL Server through C# code [duplicate]

2 Answers 2

6 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Linked

Related