FormatException when trying to write string to database

Question

I am writting a small web application and I want to add some details to my database using a web form. I am using asp.net and c# to create this web application and visual studio 2013 as developing tool. I am using a barcode scanner to scan the barcode from a product. Because i can not include a screenshot from my user interface, i have 3 labels, 3 textboxes (Barcode,Description,Price), a add button and i have created a database to save my details. My 3 colums in the database is Barcode, Description, Price and their data types are varchar(50), varchar(50), decimal(10,2). When I run the page and scan the barcode i get an exception:

System.FormatException: Input string was not in a correct format.

I have tried it without the barcode scanner and put the barcode by keyboard and is working fine.

Here are more details from the exception:

Server Error in '/' Application.

Input string was not in a correct format. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack

trace for more information about the error and where it originated in the code.

Exception Details: System.FormatException: Input string was not in a correct format.

Source Error:

Line 31: con.Open(); Line 32: Line 33:
da.InsertCommand.ExecuteNonQuery(); Line 34: Line 35:
con.Close();

Source File: path.aspx.cs Line: 33

Stack Trace:

[FormatException: Input string was not in a correct format.]
System.Number.StringToNumber(String str, NumberStyles options, NumberBuffer& number, NumberFormatInfo info, Boolean

parseDecimal) [phone] System.Number.ParseDecimal(String value, NumberStyles options, NumberFormatInfo numfmt) +172
System.Convert.ToDecimal(String value, IFormatProvider provider) +67
System.String.System.IConvertible.ToDecimal(IFormatProvider provider) +10 System.Convert.ChangeType(Object value, Type conversionType, IFormatProvider provider) [phone]
System.Data.SqlClient.SqlParameter.CoerceValue(Object value, MetaType destinationType, Boolean& coercedToDataFeed,

Boolean& typeChanged, Boolean allowStreaming) +5332823

[FormatException: Failed to convert parameter value from a String to a Decimal.] System.Data.SqlClient.SqlParameter.CoerceValue(Object value, MetaType destinationType, Boolean& coercedToDataFeed,

Boolean& typeChanged, Boolean allowStreaming) +5331815
System.Data.SqlClient.SqlParameter.GetCoercedValue() +185
System.Data.SqlClient.SqlParameter.Validate(Int32 index, Boolean isCommandProc) +102
System.Data.SqlClient.SqlCommand.BuildParamList(TdsParser parser, SqlParameterCollection parameters) +201
System.Data.SqlClient.SqlCommand.BuildExecuteSql(CommandBehavior behavior, String commandText, SqlParameterCollection

parameters, _SqlRPC& rpc) +237
System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean

returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds) +1421
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean

returnStream, String method, TaskCompletionSource1 completion, Int32 timeout, Task& task, Boolean asyncWrite) +177
System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource1 completion, String methodName, Boolean

sendToPipe, Int32 timeout, Boolean asyncWrite) +208
System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +163
PosSystem.Stock.addStock.btnAdd_Click(Object sender, EventArgs e) in path\add.aspx.cs:33
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +9615682
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +103
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +10
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +13
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +35 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

+1724

Any one has an idea why is that and how i can fix it please?

Here is my code for when the button is clicked:

    protected void btnAdd_Click(object sender, EventArgs e)
    {
        int y;
        da.InsertCommand = new SqlCommand("INSERT INTO Products VALUES(@Barcode,@Description,@Price)", con);
        da.InsertCommand.Parameters.Add("@Barcode", SqlDbType.VarChar).Value = txtBarcode.Text;
        da.InsertCommand.Parameters.Add("@Description", SqlDbType.VarChar).Value = txtDescription.Text;
        da.InsertCommand.Parameters.Add("@Price", SqlDbType.Decimal).Value = txtPPrice.Text;
        con.Open();

        da.InsertCommand.ExecuteNonQuery();

        con.Close();
    }

Answer 1

This line...

da.InsertCommand.Parameters.Add("@Price", SqlDbType.Decimal).Value = txtPPrice.Text;

Seems to be the problem. you are trying to save text to a decimal field. it could work if the value of the string is convertible to a decimal by the sql driver but on this occasion it is failing. maybe because the price string contains currency formatting (e.g. £, $).

Try parsing this as a decimal first

Answer 2

I realize this is an old question, but -- I encounter code like this all the time and the issue is allowing .NET and Sql to coerce values.

I think that you are still not focusing on the real issue, which is that you are relying on coercion of data that you have not validated. You can certainly Trim any white space, however -- if you would do the conversion yourself, you would cover far more ground than by trying to bat away the input issues in favor of writing more defensive code.

I usually keep a "Sandbox" ready to test things like this outside of the application. Have a look at this code...it is far from perfect, however -- it illustrates some flags you can pass to TryParse that will save you tons of head scratching and frustration.

    Dim theProblemWithTheData As String = String.Format("$-123.45{0}", vbCrLf)
Dim dontDoThis As New System.Data.SqlClient.SqlParameter("fakeParameter",
                                                         SqlDbType.Decimal)
Dim doThis As New System.Data.SqlClient.SqlParameter("betterFakeParameter",
                                                     SqlDbType.Decimal)
Dim doThisValue As Decimal

Try
  dontDoThis.Value = theProblemWithTheData
  Dim success As Boolean = Decimal.TryParse(theProblemWithTheData,
                                            System.Globalization.NumberStyles.Any,
                                            System.Globalization.CultureInfo.CurrentCulture,
                                            doThisValue)

  If success Then
    doThis.Value = doThisValue
  End If

Catch ex As Exception
  Dim exInfo As String = ex.ToString
End Try