Must declare the scalar variable “@User”

Question

I keep getting an error that I don't understand:

Must declare the scalar variable "@user"

I'm actually doing a website and I'm using C# ASP.NET and SQL Server. I have a class name Connection and another one named Query. And here is the problem

public class Query
{
    public int ValidateLogin(string userID, string password)
    {
        string query = "Select * From tblLogin where UserID = @user and Password = @paswd";

        Connection objConn = new Connection();

        DataTable dtLogin = objConn.GetDataFromDB(query);

        int result = 0;

        if (dtLogin.Rows.Count > 0)
        {
            result = 1;
        }

        return result;
}

public class Connection
{
    string conn = ConfigurationManager.ConnectionStrings["DBConn"].ToString();

    public DataTable GetDataFromDB(string query)
    {
        SqlConnection myConn = new SqlConnection(conn);
        myConn.Open();

        SqlDataAdapter da = new SqlDataAdapter();
        da.SelectCommand = myConn.CreateCommand();
        da.SelectCommand.CommandText = query;

        DataTable dt = new DataTable();

        da.Fill(dt);

        da.Dispose();
        myConn.Close();

        return dt;
}

protected void Page_Load(object sender, EventArgs e)
{
}

protected void btn_login_Click(object sender, EventArgs e)
{
        int result = 0;
        Query q = new Query();
        result = q.ValidateLogin(txt_userID.Text, txt_password.Text);

        if (result == 1)
        {
            Response.Redirect("~/Performance Appraisal Form.aspx");
        }
        else
        {
        }
}

You are trying to use parameters with your query, but you are not attaching them to your command anywhere — Habib, May 07 '14 at 19:21

score 1 · Accepted Answer · answered May 07 '14 at 19:29

Add your parameters to the SelectCommand.

Example:

private static void UpdateDemographics(Int32 customerID,
    string demoXml, string connectionString)
{
    // Update the demographics for a store, which is stored 
    // in an xml column. 
    string commandText = "UPDATE Sales.Store SET Demographics = @demographics "
        + "WHERE CustomerID = @ID;";

    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        SqlCommand command = new SqlCommand(commandText, connection);
        command.Parameters.Add("@ID", SqlDbType.Int);
        command.Parameters["@ID"].Value = customerID;

        // Use AddWithValue to assign Demographics.
        // SQL Server will implicitly convert strings into XML.
        command.Parameters.AddWithValue("@demographics", demoXml);

        try
        {
            connection.Open();
            Int32 rowsAffected = command.ExecuteNonQuery();
            Console.WriteLine("RowsAffected: {0}", rowsAffected);
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
        }
    }
}

Your code

To do it add another parameter to your

  public DataTable GetDataFromDB(string query)

change it to

  public DataTable GetDataFromDB(string query, string[] params) //example, can be another type of collection like ParameterCollection.


 da.SelectCommand.parameters.add("@user",params[0]);
 da.SelectCommand.parameters.add("@paswd",params[1]);

To pass parameters to your method.

string[] Params= new string[2];
    Params[0] = txt_userID.Text;
    Params[1] =txt_password.Text;


 DataTable dtLogin = objConn.GetDataFromDB(query,Params);

Reference:

http://msdn.microsoft.com/es-es/library/system.data.sqlclient.sqlcommand.parameters(v=vs.110).aspx

score -2 · Answer 2 · answered May 07 '14 at 19:26

-2

You're using inline SQL, so that needs to be a valid SQL string. @user isn't a declared variable in the SQL code, so it is not detected. Either include a declare statement in your SQL string, or inject the actual value for "user" in your string.

Hope this helps..

(Oh, and as a side note.. please don't use inline SQL, it's terribly insecure. Switch to using Stored Procedures at the very least...)

answered May 07 '14 at 19:26

ewitkows

3,528
3
40
62

3

How is inline SQL "terribly insecure"? Using stored procs for a one-line SELECT seems like overkill to me. – D Stanley May 07 '14 at 19:31
You can use inline Sql but allways use Parameters. It will help you to avoid sql Injection. http://stackoverflow.com/questions/6547986/how-to-prevent-a-sql-injection-escaping-strings – Jaime García Pérez May 07 '14 at 19:34
In line SQL isn't insecure - how on earth are you making that assertion? As long as you're not using string concatenation to generate your queries it's absolutely fine; how else do you think Entity Framework or LINQ to SQL communicates with SQL Server? – Ed Courtenay May 07 '14 at 19:36
1

"...or inject the actual value for "user" in your string" - never, ever do this. http://xkcd.com/327/ – Ed Courtenay May 07 '14 at 19:41
@EdCourtenay +1 for xkcd. My answer was solving the problem by the original poster, it's not the way I would write this code, but it's not my code, substituting for that undeclared variable would fix the code. Phx good point on using parameters in local code. Then again I personally liked separating my SQL and application code, to avoid having to compile and deploy for new fixes. But that's just my 2 cents. – ewitkows May 08 '14 at 12:57

Must declare the scalar variable “@User”

2 Answers2