CODEWITHSUNDEEP
c#sql
learningthemachine
learningthemachine

Reputation: 614

C# sql command parameters not being used?

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?

Upvotes: 0

Views: 1680

Answers (3)

Yuri
Yuri

Reputation: 2900

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

Upvotes: 1

Igor
Igor

Reputation: 62213

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.

Upvotes: 2

DRapp
DRapp

Reputation: 48129

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.

Upvotes: 0

Related Questions