Is there a function in SQL Server to convert to a decimal number?

Question

I have a cell value in a column that is "12,000". And I want to change to "11,000" and display it... but it only displays a blank space. And in the database the value appears NULL.

In the database the type is Decimal(18,3).

My code in C# is like this:

decimal dec = Convert.ToDecimal(dgvRow.Cells[16].Value.ToString());
string query = "UPDATE cabecdoc SET CDU_Peso = TRY_CONVERT(DECIMAL(18,3),'" + dec + "' ) WHERE Id = '" + idDoc + "'";

If I do the query:

UPDATE CabecDoc 
SET CDU_Peso = TRY_CONVERT(DECIMAL(18,3), '11.000') 
WHERE Id = 'fb9668a9-46fa-11ec-9494-00155d01b010'

in Microsoft SQL Server - it works... but in my program in C# it displays a blank space value.

Answer 1

Ok, assuming the text value will NOT have the $ (or currency character), then this will work:

        string strSQL = "UPDATE cabecdoc " +
                        "SET CDU_Peso = @Peso " +
                        "WHERE Id = @ID";
        using (SqlCommand cmdSQL = new SqlCommand(strSQL, conn))
        {
            conn.Open();
            cmdSQL.Parameters.Add("@Peso", SqlDbType.Decimal).Value = dgvRow.Cells[16].Text;
            cmdSQL.Parameters.Add("@ID", SqlDbType.NVarChar).Value = idDoc;
            cmdSQL.ExecuteNonQuery();
        }

So if the cell has:

12000
12,000
12,000.00

Then the above will work fine.

However, if the cell is to have:

 $12,000.00

Then you need to use the globalization converters for this.

Say like this:

   // -- using System.Globalization;

            Decimal TestNum = 0;
            decimal.TryParse(TextBox1.Text,
            NumberStyles.Currency,
            CultureInfo.CurrentCulture.NumberFormat, out TestNum); 

Now, if the converter fails, then the TestNum will not be changed (the "out" return value in above), and then we now have this:

        string strSQL = "UPDATE cabecdoc " +
                        "SET CDU_Peso = @Peso " +
                        "WHERE Id = @ID";
        using (SqlCommand cmdSQL = new SqlCommand(strSQL, conn))
        {
            conn.Open();
            cmdSQL.Parameters.Add("@Peso", SqlDbType.Decimal).Value = TestNum;
            cmdSQL.Parameters.Add("@ID", SqlDbType.NVarChar).Value = idDoc;
            cmdSQL.ExecuteNonQuery();
        }

And while the first will work as long as no currency character such as "$"?

Well, it will work, but first example does not handle "" (empty string).

so, you could say use this:

string MyPeso = dgvRow.Cell[16].Text;
if (MyPeso == "")
   MyPeso = "0";

....
cmdSQL.Parameters.Add("@Peso", SqlDbType.Decimal).Value = MyPeso;

Also, as noted, not only is using paramters a lot easier - you don't even have to know (or think) if you need to surround the values - so the sql is much easier to read, and as noted, it is sql injection safe.

eg this: