SQL Query 表示未提供参数，但已添加到 SqlCommand 对象

SQL Query says a parameter is not supplied, but is added to the SqlCommand object(SQL Query 表示未提供参数，但已添加到 SqlCommand 对象)

问题描述

我有一个存储过程，它有一个名为 UserName 的参数，在我后面的代码中，我有一个 SqlCommand 对象，我使用 Add 方法将参数添加到该对象中.但是由于某种原因，当命令对象尝试运行 ExecuteReader 方法时，它会抛出异常.我完全不知所措，不知道为什么它不能识别参数.在运行 ExecuteReader 方法之前，我设置了一个断点，因此我可以确认命令对象确实包含正在设置的参数，这是真的.我知道当参数没有添加到命令对象时，存储过程确实返回了正确的数据，但是在实际的存储过程中被硬编码.以下是 catch 块中给出的异常消息.我还将粘贴我的代码和存储过程的第一部分.看到我尝试了许多不同的方法都无济于事，我将不胜感激.提前致谢.

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.

过程或函数someStoredProcedure"需要参数@UserName"，但未提供该参数.

Procedure or function 'someStoredProcedure' expects parameter '@UserName', which was not supplied.

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

@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

推荐答案

尝试确保命令类型设置为存储过程.

Try making sure that the command type is set to stored procedure.

mycommand.CommandType = System.Data.CommandType.StoredProcedure;

这篇关于SQL Query 表示未提供参数，但已添加到 SqlCommand 对象的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！