SQL查询表示的参数没有被提供，但是被添加到SqlCommand对象 [英] SQL Query says a parameter is not supplied, but is added to the SqlCommand object

问题描述

我有一个存储过程，有一个参数调用用户名和我的code后面我有我添加参数与Add方法SqlCommand对象。但对于一些时候，命令对象试图运行ExecuteReader方法的原因，它抛出一个异常。我完全处于亏损状态，而且不知道为什么它不承认的参数。在ExecuteReader方法运行我有一个断点设置，所以我可以确认命令对象不包含被设置的参数，这是真的。我知道什么时候该参数不添加到命令对象的存储过程不会返回正确的数据，但很难codeD的实际存储过程的研究。下面是给出了catch块的异常信息。我也贴我的code和存储过程的第一部分。我将非常AP preciate在这个问题上的任何帮助，看到我已经尝试了许多不同的方法都没有用。先谢谢了。

异常消息

过程或函数'someStoredProcedure需要参数@UserName，但未提供。

code背后

 私人数据表GetLossMitData（字符串code，日期时间？开始，日期时间？完）
{
DataTable的结果=新的DataTable（）;
字符串的connectionString = ConfigurationManager.ConnectionStrings [ASDF]的ConnectionString。
字符串username =的String.Empty;

尝试
{
    使用（的SPSite网站=新的SPSite（ConfigurationManager.AppSettings [someName]））
    {
        使用（的SPWeb网站= site.OpenWeb（））
        {
            USERNAME = web.CurrentUser.Email.ToString（）;
        }
    }

    使用（SqlConnection的连接1 =新的SqlConnection（的connectionString））
    {
         connection1.Open（）;
         使用（SqlCommand的命令1 =新的SqlCommand（someStoredProcedure，连接1））
         {
             command1.Parameters.Add（新的SqlParameter（@用户名，用户名））;
             command1.Parameters.Add（新的SqlParameter（@产品code，code））;

             SqlDataReader的博士= command1.ExecuteReader（CommandBehavior.CloseConnection）;
             results.Load（DR）;
         }
         connection1.Close（）;
    }
}
赶上（例外前）
{
}
返回结果;
}
 

存储过程

  @UserName为nvarchar（256），
@产品code为nvarchar（256），
@StartDate为nvarchar（256）='1/1/1900'，
@EndDate为nvarchar（256）= '12 / 30/2012

如
开始
SET NOCOUNT ON;

声明@UserID INT

选择@UserID = Users.UserID
从用户
其中，Users.Email = @UserName
 

解决方案

尽量确保命令类型设置为存储过程。

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

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.

Exception Message

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

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

解决方案

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

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

这篇关于SQL查询表示的参数没有被提供，但是被添加到SqlCommand对象的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！