Doubt about "Error Converting data type varchar to float"

Question

Error Converting data type varchar to float

This error is occurring when performing a Update in a table, follows below the code:

SqlCommand cmd = new SqlCommand("UPDATE Notas SET nota = '" + nota.nota + "' WHERE idMateria = '" + nota.idMateria + "' and idAluno = '" + nota.idAluno + "';", con);
cmd.ExecuteNonQuery();

The note field is created in the BD as float, I tried to perform the conversion but I’m not getting any hint?

Answer 1

You need to use parameters:

var cmd = new SqlCommand(@"UPDATE Notas 
                           SET nota = @nota 
                           WHERE idMateria = @idMateria 
                           and idAluno = @idAluno", con);
cmd.Parameters.Add("@nota", SqlDbType.Float).Value = nota.nota;
cmd.Parameters.Add("@idMateria", SqlDbType.Int).Value = nota.idMateria;
cmd.Parameters.Add("@idAluno", SqlDbType.Int).Value = nota.idAluno;
cmd.ExecuteNonQuery();

Parameters solve for you the type of the variable, prevent SQL Injection and strange behavior in the execution. See more here.

Answer 2

Editing
Warning: This response may leave your code vulnerable to SQL Injection, use that other answer.

Well, let’s go in pieces...

• In his enclosure WHERE you are using simple quotes for fields that apparently are of the type int, but roughly this should only be done in fields of the type varchar.

• As we discussed in the issue chat, it appears your database is set to default en-US where the actual values are separated by dot and not comma as here in our Brasilzão.

Apergunta is:
What type of variable is nota.nota?
string or float?

---

string

In this case you will need to make one REPLACE in your query by replacing the comma by a period, and after that you will also need to make a CONVERT or CAST so that the string magically transformed into a value of the type float. This is necessary as it is not possible to insert a string in a field that was created to receive float.

The query would look like this:

SqlCommand cmd = new SqlCommand("UPDATE Notas SET nota = CONVERT(FLOAT, REPLACE('" + nota.nota + "', ',', '.')) WHERE idMateria = " + nota.idMateria + " and idAluno = " + nota.idAluno + ";", con);

Or you could still do the replace via C# before creating the query:

nota.nota = nota.nota.Replace(",", ".");

And so generate the query without replace:

SqlCommand cmd = new SqlCommand("UPDATE Notas SET nota = CONVERT(FLOAT, '" + nota.nota + "') WHERE idMateria = " + nota.idMateria + " and idAluno = " + nota.idAluno + ";", con);

Would those be the best options? In my view it does not have a very good smell...
In my humble opinion nota.nota be the type float would be much cuter.

---

float

How the value is coming from View?
string or float?

If you are coming as a string, convert to float in the Controller, but I would particularly prefer convert to float in View already sending so the object ready.

So your query would look like this:

SqlCommand cmd = new SqlCommand("UPDATE Notas SET nota = " + nota.nota + " WHERE idMateria = " + nota.idMateria + " and idAluno = " + nota.idAluno + ";", con);