C# 必须声明标量变量“@UserName”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8933634/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Must declare the scalar variable "@UserName"
提问by Lyuben Todorov
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.
任何帮助，将不胜感激。
采纳答案by Mubarek
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;
}
回答by Matt Grande
cmd.Parameters.Add("@uname", SqlDbType.VarChar).Value = userName;
Note the @ in front of uname.
注意uname 前面的@。
回答by Charles Lambert
You need to pass cmdto the SqlDataAdapterconstructor instead of the cmdStringand connobjects.
您需要传递cmd给SqlDataAdapter构造函数而不是cmdStringandconn对象。
回答by gbianchi
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...
除了下面描述的所有错误（或在这里;) .. 你正在传递一个命令行和一个连接到数据适配器，但你正在填充一个你没有使用的命令的参数.. ;) 所以你有几个错误...
回答by aditya
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;
