Must declare the scalar variable @ when updating table

Question

I have this code:

connection.Open();
string query = "UPDATE [dbo].[EmailPassword] SET HashedPassword = @hashedPassword, Salt = @salt, ForgotHisPassword = @forgotHisPassword where Email = @email";

SqlCommand command = new SqlCommand(query, connection);
string recoveredPassword = RandomPasswordGenerator.Generate();
PasswordHash passwordHash = new PasswordHash();
byte[] newHashedPassword = passwordHash.ComputeHash(recoveredPassword);
command.Parameters.AddWithValue("@hashedPassword", newHashedPassword);
command.Parameters.AddWithValue("@salt", passwordHash.saltAsString);
command.Parameters.AddWithValue("@forgotHisPassword", 1);
command.Parameters.AddWithValue("@email", TxtSendPasswordToEmail.Text);
command.ExecuteNonQuery();

And I'm getting this error:

Must declare the scalar variable "@hashedPassword".

I've been looking for the solution for quite long and I couldn't find any answer so I'll apprecieate your help very much!

Edit: i finally found the problem:

here is the line right after this code lines which at first i thought had nothing to do with my error:

if (TableFunctions.querySucceeded(command))

here is how the function was built:

 public static bool querySucceeded(string query, string strConnection)
    {
        SqlConnection connection = new SqlConnection(strConnection);
        bool succeeded = false;
        try
        {
            connection.Open();
            SqlCommand command = new SqlCommand(query, connection);
            if (command.ExecuteScalar() != null)
            {
                succeeded = true;
            }

        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }
        return succeeded;
    }

which means i send it only the query i want to execute and the connection and it should execute it and return if succeeded BUT the query doesn't contain the parameters i added to the previous command thus, this command tries to executes the previous command without the paramters of the previous command and this is what is failes and says: Must declare varaiable @HashedPassword. I found it after i used debugging and it also took me some time to get it, but thank you all for the support!

What is the DataType of the column HashedPassword in your database? — Steve, Jan 06 '17 at 20:26
See http://stackoverflow.com/questions/15518051/save-byte-array-in-sql-server — Loring, Jan 06 '17 at 20:28
`AddWithValue()` has known problems. Add with the explicit data type. — Gordon Linoff, Jan 06 '17 at 20:28

score 1 · Answer 1 · answered Jan 06 '17 at 20:41

1

Just like @Gordon-linoff and others mentioned in comments. you should change your code to this:

command.Parameters.Add("@hashedPassword", SqlDbType.Binary);
command.Parameters["@hashedPassword"].Value = newHashedPassword;

Check this out.

answered Jan 06 '17 at 20:41

Ali M

801
11
23

score 0 · Answer 2 · answered Jan 06 '17 at 20:37

You can try this code. Supposing your column in your database is a varchar. If not you can easily change the SqlDbType to the type you have:

connection.Open();
            string query = "UPDATE [dbo].[EmailPassword] SET HashedPassword = @hashedPassword, Salt = @salt, ForgotHisPassword = @forgotHisPassword where Email = @email";

        SqlCommand command = new SqlCommand(query, connection);
        string recoveredPassword = RandomPasswordGenerator.Generate();
        PasswordHash passwordHash = new PasswordHash();
        byte[] newHashedPassword = passwordHash.ComputeHash(recoveredPassword);
        command.Parameters.Add("@hashedPassword", SqlDbType.Varchar); // you have to send the data type explicitely
        command.Parameters["@hashedPassword"].Value = Encoding.ASCII.GetBytes(newHashedPassword); 
        command.Parameters.AddWithValue("@salt", passwordHash.saltAsString);
        command.Parameters.AddWithValue("@forgotHisPassword", 1);
        command.Parameters.AddWithValue("@email", TxtSendPasswordToEmail.Text);
        command.ExecuteNonQuery();

Must declare the scalar variable @ when updating table

2 Answers2