Must declare scalar variable @

Question

For some reason, after defining my variables, I still get the error message “must declare a scalar variable”.

using (OleDbConnection conn = new OleDbConnection(connString)) { conn.Open(); using(OleDbCommand cmd = new OleDbCommand("SELECT UserID FROM tblUser WHERE Username=@user AND Password = @pass", conn)) { cmd.Parameters.AddWithValue("@user", user); cmd.Parameters.AddWithValue("@pass", pass); int UserID = (int)cmd.ExecuteScalar(); return UserID < 0 ? -1 : UserID; } } 

Answer 1

OleDb does not support named parameters. I guess this is what causes errors. Instead, use in a SQL query ? instead of the name param and make sure the order of the added parameters matches the order that they display in the request. So:

 using(OleDbCommand cmd = new OleDbCommand("SELECT UserID FROM tblUser WHERE Username=? AND Password = ?", conn)) { cmd.Parameters.AddWithValue("@user", user); cmd.Parameters.AddWithValue("@pass", pass); int UserID = (int)cmd.ExecuteScalar(); return UserID < 0 ? -1 : UserID; } 

Answer 2

Try using the following. If the value is passed as null, it will interpret it as missing.

 using (OleDbConnection conn = new OleDbConnection(connString)) { conn.Open(); using(OleDbCommand cmd = new OleDbCommand("SELECT UserID FROM tblUser WHERE Username=@user AND Password = @pass", conn)) { cmd.Parameters.AddWithValue("@user", user ?? DBNull.Value); cmd.Parameters.AddWithValue("@pass", pass ?? DBNull.Value); int UserID = (int)cmd.ExecuteScalar(); return UserID < 0 ? -1 : UserID; } } 

Answer 3

Add Clear () to remove all remaining parameters from the previous call. Let it work.

 cmd.Parameters.Clear() cmd.Parameters.AddWithValue("@user", user); cmd.Parameters.AddWithValue("@pass", pass);