C# sql command parameters not being used?

Question

SqlCommand command = new SqlCommand("UPDATE qcCheckList SET reviewed = @0, errorLevel = @1, comment = @2 WHERE guid = @3 and itemId = @4", connection);
command.Parameters.AddWithValue("0", cli.reviewed.Checked);
int errorLevel = 0;
if (cli.error.Text == "Level 1")
    errorLevel = 1;
else if (cli.error.Text == "Level 2")
    errorLevel = 2;
else if (cli.error.Text == "Level 3")
    errorLevel = 3;
command.Parameters.AddWithValue("1", errorLevel);
command.Parameters.AddWithValue("2", cli.comments.Text);
command.Parameters.AddWithValue("3", guid);
command.Parameters.AddWithValue("4", cli.itemId);
command.ExecuteNonQuery();
Console.WriteLine(command.CommandText);

When i run this code it doesn't look like my sql command's parameters are being replaced by their actual values. I want to be able to see their values when the command is compiled so i can see if the correct command is being sent. Any ideas?

Have you tried including the @ prefix when adding the parameters? — itsme86
– itsme86, Commented Feb 8, 2019 at 18:51
it doesn't look like my sql command's parameters are being replaced by their actual values. <= What do you mean by this? Also the values do not replace the parameters, the parameters and their values are sent with the query. — Igor
– Igor, Commented Feb 8, 2019 at 18:52
You need the @ symbol when adding to the parameter collection... command.Parameters.AddWithValue("@1", errorLevel); — Andrew
– Andrew, Commented Feb 8, 2019 at 18:54

Igor · Accepted Answer · 2019-02-08 19:08:43Z

Answer is based on my assumption that you are expecting the query statement (string) to change. That assumption is based on your wording as well as the last sentence in your code statement.

Console.WriteLine(command.CommandText);

The query text is not altered, ie. the parameters in the query text are not replaced with the assigned parameter values. The parameters, and their values, are sent with the query text to the DBMS for execution. In Sql Server you can see this if you were to profile the Sql Server Instance using Profiler.

As a side note I do not recommend you use AddWithValue. See also Can we stop using AddWithValue() already?. Instead just use Add. Also you should give meaningful names to your parameters, like "@errorLevel", when you are able to, it makes debugging easier.

command.Parameters.Add("@errorLevel", SqlDbType.Int).Value = errorLevel;

It is not necessary to prefix the parameter names with "@" when calling Add as it will be done for by the method if you did not. It is considered good practice though but that is a matter of opinion.

Yuri · Accepted Answer · 2019-02-08 20:22:12Z

1

When you are assigning value to the parameters, command string will not change. To see actual values of the parameters you can get values after they are being assigned

 foreach (SqlParameter item in command.Parameters)
 {
    Console.WriteLine(item.ParameterName + "=" + item.Value);
 }

This will display actual values

answered Feb 8, 2019 at 20:22

Yuri

2,9084 gold badges31 silver badges41 bronze badges

Comments

DRapp · Accepted Answer · 2019-02-08 18:56:26Z

0

dont use @ numbers, but named.. such as @parmReview, @parmError, etc... then the NAMED values are in your

command.Parameters.AddWithValue( "parmReview", cli.reviewed.Checked)
command.Parameters.AddWithValue("parmError", errorLevel);
etc.

answered Feb 8, 2019 at 18:56

DRapp

48.3k13 gold badges80 silver badges149 bronze badges

Collectives™ on Stack Overflow

C# sql command parameters not being used?

3 Answers 3

Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Related