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

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, Feb 02 '16 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, Feb 02 '16 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, Feb 02 '16 at 12:33
everything is working fine for nvarchar datatype. i get error only for decimal type — Waqas Ali, Feb 02 '16 at 12:33
Run the code and hover over the "query" variable and see what the SQL looks like. There are a few possible problems, some might be in other code but if you show us what the SQL is, we will be able to easily see it. — Lukos, Feb 02 '16 at 12:35
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, Feb 02 '16 at 12:35
sorry it was RemainingPayment, i typed it TotalPayment, i edited the question — Waqas Ali, Feb 02 '16 at 12:37
while value with single quote it also throws an error icorrect syntax near , — Waqas Ali, Feb 02 '16 at 12:39
could u please explain the Parameter method?as i am new to Programming , Please — Waqas Ali, Feb 02 '16 at 12:42

score 1 · Answer 1 · answered Feb 02 '16 at 12:38

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!

Steve · Accepted Answer · 2016-02-02T13:00:21.720

1

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

edited Feb 02 '16 at 13:00

answered Feb 02 '16 at 12:45

Steve

213,761
22
232
286

when i apply this code, it says "input string was not in correct format", where is the error ? – Waqas Ali Feb 02 '16 at 12:51
If your textbox tbRemainingPayment doesn't contain a valid decimal value then you need to convert it with tryParse. I will update the answer – Steve Feb 02 '16 at 12:58
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 – Waqas Ali Feb 02 '16 at 12:59
Also CHQNo is a numeric value? – Steve Feb 02 '16 at 13:00
Thank you very much bro.. it worked fine – Waqas Ali Feb 02 '16 at 13:02
its Bank Account number, i assigned Nvarchar datatype to it – Waqas Ali Feb 02 '16 at 13:03

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

2 Answers2