必须声明标量变量“@UserName”

I keep getting an error that i dont understand. Must declare the scalar variable "@varname"

我一直收到一个我不明白的错误。必须声明标量变量“@varname”

Tens of hours of research, tried multiple solutions without sucess.

经过数小时的研究,尝试了多种解决方案而无需成功。

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.

我的目标是创建一个使用2个文本框和一个按钮的登录页面,它根据存储在Sql数据库中的信息检查用户是否退出。

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 the "dataAdapter.Fill(loginCredentials);" is being executed.

具体在哪里“dataAdapter.Fill(loginCredentials);”正在执行。

the commented out statement works successfully in logging in a user with correct username and password, but as far as i know is not secure, as its vulnerable to sql injections and this is why i'm trying to parameterize the sql statement.

注释掉的语句在使用正确的用户名和密码登录用户时成功运行,但据我所知,它不安全,因为它容易受到sql注入,这就是我试图参数化sql语句的原因。

error screenshot below:

错误截图如下:

any help would be appreciated.

任何帮助,将不胜感激。

5 个解决方案

#1

Edit: 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:

编辑:您应该将sqlcommand传递给dataAdapter,因为在您的情况下,sqlcommand(cmd)具有的信息不仅仅是commandtext和connectionstring。您的代码可能如下所示:

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;
}

#2

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

您需要将cmd传递给SqlDataAdapter构造函数而不是cmdString和conn对象。

#3

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

Note the @ in front of uname.

注意uname前面的@。

#4

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...

除了下面描述的所有错误(或者在这里;))..你正在传递命令行和数据适配器的连接,但是你正在填写你没有使用的命令的参数..;)所以你有几个错误......

#5

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;

#1