C# SQL 查询表示未提供参数，但已将其添加到 SqlCommand 对象
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2282143/
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
SQL Query says a parameter is not supplied, but is added to the SqlCommand object
提问by jhorton
I have a stored procedure that has a parameter called UserName and in my code behind I have a SqlCommand object that I add the parameters to with the Add method. But for some reason when the command object tries to run the ExecuteReader method, it throws an exception. I am totally at a loss and have no idea why it's not recognizing the parameter. Before the ExecuteReader method is run I have a break point set so I can confirm the command object does contain the parameters being set, which is true. I know the stored procedure does return the correct data when the parameters are not added to the command object, but are hard coded in the actual stored procedure. Below is the exception message that is given in the catch block. I will also paste my code and first part of stored procedure. I would greatly appreciate any help in this issue, seeing that I have tried many different approaches to no avail. Thanks in advance.
我有一个存储过程，它有一个名为 UserName 的参数，在我后面的代码中，我有一个 SqlCommand 对象，我使用 Add 方法向其中添加参数。但是由于某种原因，当命令对象尝试运行 ExecuteReader 方法时，它会引发异常。我完全不知所措，不知道为什么它不能识别参数。在运行 ExecuteReader 方法之前，我设置了一个断点，因此我可以确认命令对象确实包含正在设置的参数，这是真的。我知道当参数未添加到命令对象中时，存储过程确实会返回正确的数据，但在实际存储过程中进行了硬编码。下面是 catch 块中给出的异常消息。我还将粘贴我的代码和存储过程的第一部分。我将不胜感激在这个问题上的任何帮助，看到我尝试了很多不同的方法都无济于事。提前致谢。
Exception Message
异常信息
Procedure or function 'someStoredProcedure' expects parameter '@UserName', which was not supplied.
过程或函数“someStoredProcedure”需要未提供的参数“@UserName”。
Code Behind
背后的代码
private DataTable GetLossMitData(string code, DateTime? start, DateTime? end)
{
DataTable results = new DataTable();
string connectionString = ConfigurationManager.ConnectionStrings["asdf"].ConnectionString;
string userName = String.Empty;
try
{
using (SPSite site = new SPSite(ConfigurationManager.AppSettings["someName"]))
{
using (SPWeb web = site.OpenWeb())
{
userName = web.CurrentUser.Email.ToString();
}
}
using (SqlConnection connection1 = new SqlConnection(connectionString))
{
connection1.Open();
using (SqlCommand command1 = new SqlCommand("someStoredProcedure", connection1))
{
command1.Parameters.Add(new SqlParameter("@UserName", userName));
command1.Parameters.Add(new SqlParameter("@ProductCode", code));
SqlDataReader dr = command1.ExecuteReader(CommandBehavior.CloseConnection);
results.Load(dr);
}
connection1.Close();
}
}
catch (Exception ex)
{
}
return results;
}
Stored Procedure
存储过程
@UserName nvarchar(256),
@ProductCode nvarchar(256),
@StartDate nvarchar(256) = '1/1/1900',
@EndDate nvarchar(256) = '12/30/2012'
AS
BEGIN
SET NOCOUNT ON;
Declare @UserID int
Select @UserID = Users.UserID
from Users
where Users.Email = @UserName
采纳答案by Aaron
Try making sure that the command type is set to stored procedure.
尝试确保命令类型设置为存储过程。
mycommand.CommandType = System.Data.CommandType.StoredProcedure;
回答by SLaks
By default, the CommandTextproperty needs to contain a complete SQL command, not just the name of the stored procedure.
默认情况下，该CommandText属性需要包含完整的 SQL 命令，而不仅仅是存储过程的名称。
You can change this by to set the SqlCommand's CommandTypeproperty to StoredProcedure.
您可以通过将SqlCommand的CommandType属性设置为 来更改此设置StoredProcedure。
Alternatively, you could explicitly pass the parameters, by changing the CommandTextto "someStoredProcedure @UserName, @ProductCode"; this is a complete SQL statement and will work with the default CommandTypeof Text.
或者，您可以通过将CommandTextto更改为"someStoredProcedure @UserName, @ProductCode";来显式传递参数。这是一个完整的 SQL 语句，将使用默认CommandType的Text.
EDIT: I just tried it, and the only way to get that error message without setting CommandTypeto StoredProcedure(which he did not do) is if CommandTextis EXEC someStoredProcedure. Passing a nullparameter gives a different error.
编辑：我刚刚尝试过，而在不设置CommandType为StoredProcedure（他没有这样做）的情况下获取该错误消息的唯一方法是 if CommandTextis EXEC someStoredProcedure。传递null参数会产生不同的错误。
回答by Ray
You will get this exception if the value of your 'userName' variable is null
如果“userName”变量的值为空，您将收到此异常
If null is valid, then pass 'DBNull.Value' to the db instead:
如果 null 有效，则将 'DBNull.Value' 传递给数据库：
command1.Parameters.Add(new SqlParameter("@UserName", (userName ?? DBNull.Value));
回答by Craig
Command1.CommandType = System.Data.CommandType.StoredProcedure
This will force the ExecuteReader to perform the exec instead of just trying it as a flat command.
这将强制 ExecuteReader 执行 exec 而不是仅仅将其作为一个平面命令来尝试。
