Cannot convert data type nvarchar to numeric

Question

get this error to the txtNum.Text i dont know what to do i already tried the

Convert.ToInt32 and SqlDbType.Int but it doesnt work please help me

the total has a $ sign in it maybe that's the problem. the Total's DataType is Decimal

com = new SqlCommand(@"INSERT INTO Orders(
                        OrderDate, 
                        Username, 
                        FirstName, 
                        LastName, 
                        Address, 
                        Phone, 
                        Total, 
                        HasBeenShipped
                    ) 
                    VALUES(
                        GetDate(),
                        @p5, 
                        @p1, 
                        @p2, 
                        @p3, 
                        @p4, 
                        @p6,
                        'false'
                    )", con2);
com.Parameters.AddWithValue("@p5", Label2.Text);
com.Parameters.AddWithValue("@p1", txtFname.Text);
com.Parameters.AddWithValue("@p2", TxtLname.Text);
com.Parameters.AddWithValue("@p3", TxtAdd.Text);
com.Parameters.AddWithValue("@p4", Convert.ToInt32(TxtNum.Text));
com.Parameters.AddWithValue("@p6", lblTotal.Text);
com.ExecuteNonQuery();

PhoneNumber should be varchar in database. Just check if it is `varchar` because in that case you can simply do `com.Parameters.AddWithValue("@p4", TxtNum.Text);`, without converting it to int — Habib, Feb 12 '14 at 18:00

Steve · Answer 1 · 2014-02-12T18:22:59.837

1

I suppose that the last field HasBeenShipped is a bit datatype in the database.
If this is the case then pass 0 (the numeric value) for it not 'false' (a string)

Moreover it is the parameter @p6 that contains the value for the field Total and thus it is this parameter that should be converted to a number, while the parameter @p4 could be a string if the field Phone is a NVarChar

com = new SqlCommand(@"INSERT INTO Orders 
             (OrderDate, Username, FirstName, LastName, Address, Phone, Total, HasBeenShipped)
       VALUES(GetDate(), @p5,      @p1,       @p2,      @p3,     @p4,   @p6,   0)", con2);
com.Parameters.AddWithValue("@p5", Label2.Text);
com.Parameters.AddWithValue("@p1", txtFname.Text);
com.Parameters.AddWithValue("@p2", TxtLname.Text);
com.Parameters.AddWithValue("@p3", TxtAdd.Text);
com.Parameters.AddWithValue("@p4", TxtNum.Text);
com.Parameters.AddWithValue("@p6", Convert.ToInt32(lblTotal.Text));

EDIT Following your comment below, if the lblTotal.Text contains the currency symbol then you cannot convert directly to an integer with Convert.ToInt32, you should use decimal.TryParse

decimal totalValue
decimal.TryParse(lblTotal.Text, NumberStyles.Currency, 
                 CultureInfo.CurrentCulture, out totalValue);
com.Parameters.AddWithValue("@p6", totalValue);

edited Feb 12 '14 at 18:22

answered Feb 12 '14 at 17:58

Steve

213,761
22
232
286

the total has a $ sign in it maybe that's the problem. – Rohan21 Feb 12 '14 at 18:16
Of course, this is not directly convertible in an integer, but this leads to: what is the datatype of the field Total? It is an integer or a decimal? – Steve Feb 12 '14 at 18:17
the datatype is decimal – Rohan21 Feb 12 '14 at 18:21
it works but when i check the sql server the value of total is 0.00 when i have a value in lblTotal.Text – Rohan21 Feb 12 '14 at 18:28
The conversion could fail resulting in a zero value for the variable if the currency label and the decimal sign are not the one used in your culture, In that case you need to pass an appropriate CultureInfo. You say that there is a $ in the label, what is the exact value and what is your culture? – Steve Feb 12 '14 at 19:24

score 1 · Accepted Answer · answered Feb 12 '14 at 18:30

If lblTotal is formatted as a currency then you can use:

com.Parameters.AddWithValue("@p6", Decimal.Parse(lblTotal.Text,NumberStyles.Currency));

Obviously it will throw an exception if it's not a valid currency value - you'd need to decide what to do in that case.

Cannot convert data type nvarchar to numeric

2 Answers2