Must declare the scalar variable "@username". How to overcome it

Question

I am having problem with my code Always having the error which i am not understanding. Please help with my code i want to retrieve the user details from the db for login page

 string uname = TextBox1.Text.Trim();
        string pass = TextBox2.Text.Trim();
        try
        {
            con.Open();
            string query = "SELECT user_name, user_password FROM [user] where user_name=@username and user_password=@password";
            cmd.Parameters.Add("@username", SqlDbType.VarChar).Value = uname;
            cmd.Parameters.Add("@password", SqlDbType.VarChar).Value = pass;
            cmd = new SqlCommand(query, con);
            cmd.ExecuteNonQuery();
            rd = cmd.ExecuteReader();
            if (rd.HasRows)
            {
                Response.Write("Login successful");

            }
            else
            {
                Response.Write("login Unsucessful");
            }
        }
        catch (Exception)
        {
            throw;
        }
        finally
        {
            con.Close();
        }
    }

I don't think you need `cmd.ExecuteNonQuery();` – ekad Jan 20 '15 at 05:52 — ekad, Jan 20 '15 at 05:52

score 5 · Accepted Answer · answered Jan 20 '15 at 05:52

5

You need to create your cmd prior to adding the paramaters. Your code should look like:

        con.Open();
        string query = "SELECT user_name, user_password FROM [user] where user_name=@username and user_password=@password";
        cmd = new SqlCommand(query, con);
        cmd.Parameters.Add("@username", SqlDbType.VarChar).Value = uname;
        cmd.Parameters.Add("@password", SqlDbType.VarChar).Value = pass;

EDIT: and as @ekad said, you do not need cmd.ExecuteNonQuery();

answered Jan 20 '15 at 05:52

Jacob Lambert

7,449
8
27
47

well i removed cmd.ExecuteNonQuery(); do i need any changes to made to avoid sql injection Please advice. code working fine now – parish parab Jan 20 '15 at 05:58
Nope, you should be fine. – Jacob Lambert Jan 20 '15 at 06:01
1

@parishparab aside from using very confusing name for password hash column your code is ok. – Alexei Levenkov Jan 20 '15 at 06:02
@Alexei Levenkov sorry didnt get you – parish parab Jan 20 '15 at 06:06
@parishparab I assume that you store [hash of password in DB](http://stackoverflow.com/questions/1054022/best-way-to-store-password-in-database) (whether salted or not), so it is somewhat strange to name column "password". One could be storing actual password and name of the column is ok, but than why such person would you worry about SQL injection? – Alexei Levenkov Jan 20 '15 at 06:11

Must declare the scalar variable "@username". How to overcome it

1 Answers1