CODEWITHSUNDEEP
c#sqlasp.netdatabaseauthentication
Lyuben Todorov
Lyuben Todorov

Reputation: 14173

Must declare the scalar variable "@UserName"

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

Must declare the scalar variable "@varname"

My aim is to create a login page that uses 2 textboxes and a button, where it checks if the user exits based on the information stored in a SQL database.

This is where I think the problem is coming from:

private bool DBConnection(string userName, string password)
{
    SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
    
    //string cmdString = ("SELECT UserName, Password FROM Users WHERE UserName ='" + userName +
    //                    "'AND Password ='" + password + "'");         //REMOVED AS THIS IS PRONE TO SQL INJECTIONS
    
    string cmdString = ("SELECT * FROM Users WHERE UserName = @uname AND Password = @pw");
    
    SqlCommand cmd = new SqlCommand(cmdString, conn);
    
    cmd.Parameters.Add("uname", SqlDbType.VarChar).Value = userName;
    cmd.Parameters.Add("pw", SqlDbType.VarChar).Value = password;

    DataSet loginCredentials = new DataSet();
    SqlDataAdapter dataAdapter;

    try
    {
        if (conn.State.Equals(ConnectionState.Closed))
        {
            conn.Open();

            dataAdapter = new SqlDataAdapter(cmdString, conn);
            dataAdapter.Fill(loginCredentials);

            conn.Close();

            if (loginCredentials != null)
            {
                if (loginCredentials.Tables[0].Rows.Count > 0)
                {
                    return true;
                }
                else
                {
                    lblMessage.Text = "Incorrect Username or Password";
                    lblMessage.Visible = true;
                }
            }   
        }
    }
    catch (Exception err)
    {
        lblMessage.Text = err.Message.ToString() + " Error connecting to the Database // " + cmd.Parameters.Count;
        lblMessage.Visible = true;
        return false;
    }

    return false;
}

specifically where dataAdapter.Fill(loginCredentials); is being executed.

The commented-out statement works successfully in logging in a user with the correct username and password, but as far as I know is not secure, as it's vulnerable to SQL injections and this is why I'm trying to parameterize the SQL statement.

error screenshot below: Error screenshot

Upvotes: 5

Views: 65550

Answers (5)

Mubarek
Mubarek

Reputation: 2689

You should pass the sqlcommand to the dataAdapter because in your case the sqlcommand (cmd) has more information than mere commandtext and connectionstring. Your code may look like the following:

private bool DBConnection(string userName, string password)
{
 SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);

//string cmdString = ("SELECT UserName, Password FROM Users WHERE UserName ='" + userName +
//                    "'AND Password ='" + password + "'");         //REMOVED AS THIS IS PRONE TO SQL INJECTIONS

string cmdString = ("SELECT * FROM Users WHERE UserName = @uname AND Password = @pw");

SqlCommand cmd = new SqlCommand(cmdString, conn);

cmd.Parameters.Add("uname", SqlDbType.VarChar).Value = userName;
cmd.Parameters.Add("pw", SqlDbType.VarChar).Value = password;

DataSet loginCredentials = new DataSet();
SqlDataAdapter dataAdapter;

try
{
    if (conn.State.Equals(ConnectionState.Closed))
    {
        conn.Open();

        dataAdapter = new SqlDataAdapter(cmd);
        dataAdapter.Fill(loginCredentials);

        conn.Close();

        if (loginCredentials != null)
        {
            if (loginCredentials.Tables[0].Rows.Count > 0)
            {
                return true;
            }
            else
            {
                lblMessage.Text = "Incorrect Username or Password";
                lblMessage.Visible = true;
            }
        }   
    }
}
catch (Exception err)
{
    lblMessage.Text = err.Message.ToString() + " Error connecting to the Database // " + cmd.Parameters.Count;
    lblMessage.Visible = true;
    return false;
}

return false;
}

Upvotes: 3

aditya
aditya

Reputation: 1

The most important thing is that first check if some value is assigned to the specific variable i.e 

cmd.parameter.add(@YOUR_VARIABLE, sqlDbtype.TYPE).value = ValueYouwantToGIveToThatVariable;

Upvotes: 0

gbianchi
gbianchi

Reputation: 2138

Besides all the error describes below (or up here ;) ).. you are passing a command line and a connection to the data adapter, but you are filling the parameters on a command that you are not using.. ;) so you have several errors...

Upvotes: 1

Charles Lambert
Charles Lambert

Reputation: 5132

You need to pass cmd to the SqlDataAdapter constructor instead of the cmdString and conn objects.

Upvotes: 3

Matt Grande
Matt Grande

Reputation: 12157

cmd.Parameters.Add("@uname", SqlDbType.VarChar).Value = userName;

Note the @ in front of uname.

Upvotes: 3

Related Questions