SQL Query says a parameter is not supplied, but is added to the SqlCommand object.

Question

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

Answer 1

By default, the CommandText property needs to contain a complete SQL command, not just the name of the stored procedure.

You can change this by to set the SqlCommand's CommandType property to StoredProcedure.

Alternatively, you could explicitly pass the parameters, by changing the CommandText to "someStoredProcedure @UserName, @ProductCode"; this is a complete SQL statement and will work with the default CommandType of Text.

EDIT: I just tried it, and the only way to get that error message without setting CommandType to StoredProcedure (which he did not do) is if CommandText is EXEC someStoredProcedure. Passing a null parameter gives a different error.

Answer 2

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

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

Answer 3

You will get this exception if the value of your 'userName' variable is null

If null is valid, then pass 'DBNull.Value' to the db instead:

command1.Parameters.Add(new SqlParameter("@UserName", (userName ?? DBNull.Value));   

Answer 4

Command1.CommandType = System.Data.CommandType.StoredProcedure

This will force the ExecuteReader to perform the exec instead of just trying it as a flat command.