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.
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
A SqlCommand object allows you to query and send commands to a database. It has methods that are specialized for different commands. The ExecuteReader method returns a SqlDataReader object for viewing the results of a select query. For insert, update, and delete SQL commands, you use the ExecuteNonQuery method.
Command objects use parameters to pass values to SQL statements or stored procedures, providing type checking and validation. Unlike command text, parameter input is treated as a literal value, not as executable code.
AddWithValue replaces the SqlParameterCollection. Add method that takes a String and an Object. The overload of Add that takes a string and an object was deprecated because of possible ambiguity with the SqlParameterCollection.
You have to check the value returned by ExecuteScalar() for null and only call Convert. ToInt32() if it is not null.
Try making sure that the command type is set to stored procedure.
mycommand.CommandType = System.Data.CommandType.StoredProcedure;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With