SQLException: Error converting data type varchar to numeric

Question

I am most likely missing something simple here since I am pretty new to this but I cannot figure this out. I have a C# application that takes some user-entered data and updates a SQL Server database. One of the database fields I am updating is called "TransectLength" and has a datatype of 'decimal(5,1)'. The SQL looks like this:

string updateSQL = "UPDATE " + table1 + " SET TransectLength = NULLIF(@tl, '') WHERE ObjectID = @oid";
SqlCommand cmd = new SqlCommand(updateSQL, conn); 

The parameter @tl:

cmd.Parameters.AddWithValue("@tl", dr["TransectLength"]);

dr is a DataRow. dr["TransectLength"] has a value of 2.5 and type is object{decimal}. When I try:

cmd.ExecuteNonQuery();

I get the SQL Exception Error converting data type varchar to numeric. Please see below for the parameter @tl. The SqlDbType is Decimal. If I enter nothing for TransectLength (i.e. dr["TransectLength"] value is {}) then the update query works just fine and appropriately enters a sql NULL value. I also tried this:

cmd.Parameters.AddWithValue("@tl", Convert.ToDecimal(dr["TransectLength"]));

But nothing changed. Thanks for your help.

Answer 1

Change NULLIF(@tl, '') to ISNULL(@tl, 0.0)