I'm having a bit of trouble with a stored procedure in my application.
I keep getting this error:
Procedure or function 'dt_GetNumberOfIntDeliveryMonths' expects parameter '@dateAdded', which was not supplied.
Now, this sounds straight forward enough, but the @dateAdded parameter is added (confirmed numerous times via debugging), it is in the correct format (it's a DateTime object) and the object is being populated with the relevant date. I've also tested the
stored procedure in SQL Management Studio and that works too.
private SqlParameter[] LoadParameters(deliveryType _deliveryType)
{
SqlParameter[] retParam = new SqlParameter[]
{
new SqlParameter { ParameterName = "@dateAdded", Value = _dateAdded },
new SqlParameter { ParameterName = "@companyId", Value = int.Parse(CompanyDropDown.SelectedValue) },
new SqlParameter { ParameterName = "@grainId", Value = int.Parse(grainDropDown.SelectedValue) },
new SqlParameter { ParameterName = "@grainTypeId", Value = int.Parse(grainTypeDropDown.SelectedValue) }//,
//new SqlParameter { ParameterName = "@deliveredMonth", Value = int.Parse(DeliveredMonthDropdown.SelectedValue) }
};
return retParam;
}
Adding the parameters to the command object:
SqlParameter[] param = LoadParameters(_deliveryType);
foreach (SqlParameter p in param)
{
cmd.Parameters.Add(p);
}
And finally, the stored procedure:
ALTER PROCEDURE dbo.dt_GetNumberOfIntDeliveryMonths
(
@dateAdded DateTime,
@companyId INT,
@grainId INT,
@grainTypeId INT
)
AS
/* SET NOCOUNT ON */
SELECT
id
FROM
tblDelIntEuPrice
WHERE
dateAdded = @dateAdded
AND
companyId = @companyId
AND
grainId = @grainId
AND
grainTypeId = @grainTypeId
RETURN
You didn't post this part, but go to the part of your code where you actually execute the Command object. Put a breakpoint on that line, and when you get to it, look at the values of your parameters. I bet they are not set. Could be an order of operations,
maybe you are executing the command BEFORE you populate the parameters.
Yep, you are definitely passing the parameter with a value to the stored proc. Did you try grabbing that text from profiler and executing it in SSMS? Does it return expected results or an error?
If it returns expected results, then I got nothing. Nothing except to say there there's gotta be SOMEthing in your code somewhere that you're not seeing (and therefore not mentioning). That, and good luck! : )
donpisci
Member
74 Points
178 Posts
Error passing DateTime variable to Stored Procedure
Jan 16, 2013 10:43 AM|LINK
Hi Guys,
I'm having a bit of trouble with a stored procedure in my application.
I keep getting this error:
Now, this sounds straight forward enough, but the @dateAdded parameter is added (confirmed numerous times via debugging), it is in the correct format (it's a DateTime object) and the object is being populated with the relevant date. I've also tested the stored procedure in SQL Management Studio and that works too.
Any ideas as to what else I could try?
Thanks,
John
Dave_Winches...
Contributor
3051 Points
716 Posts
Re: Error passing DateTime variable to Stored Procedure
Jan 16, 2013 10:47 AM|LINK
Hi
Please post the code. Is the date possibly MinDate or MasDate, are you doing Convert.ToDateTime.
Possibly the SQL Server is expecting American/British date format and you date does not comply with this?
Hope this helps.
/D
Please mark as ANSWER if this is the solution.
g_mani
Contributor
2055 Points
586 Posts
Re: Error passing DateTime variable to Stored Procedure
Jan 16, 2013 11:33 AM|LINK
Hi,
Please post your code with SP if possible.
Please Mark as Answer If this is helpful.
donpisci
Member
74 Points
178 Posts
Re: Error passing DateTime variable to Stored Procedure
Jan 16, 2013 12:43 PM|LINK
Hi Guys,
Here's the code:
Here's where I set the _dateAdded DateTime variable
protected void Page_Load(object sender, EventArgs e) { if (Page.IsPostBack) { //Setting _dateAdded if (!string.IsNullOrEmpty(Session["dateAdded"].ToString())) { _dateAdded = Convert.ToDateTime(Session["dateAdded"].ToString()); } } }Where I add the parameter:
private SqlParameter[] LoadParameters(deliveryType _deliveryType) { SqlParameter[] retParam = new SqlParameter[] { new SqlParameter { ParameterName = "@dateAdded", Value = _dateAdded }, new SqlParameter { ParameterName = "@companyId", Value = int.Parse(CompanyDropDown.SelectedValue) }, new SqlParameter { ParameterName = "@grainId", Value = int.Parse(grainDropDown.SelectedValue) }, new SqlParameter { ParameterName = "@grainTypeId", Value = int.Parse(grainTypeDropDown.SelectedValue) }//, //new SqlParameter { ParameterName = "@deliveredMonth", Value = int.Parse(DeliveredMonthDropdown.SelectedValue) } }; return retParam; }Adding the parameters to the command object:
SqlParameter[] param = LoadParameters(_deliveryType); foreach (SqlParameter p in param) { cmd.Parameters.Add(p); }And finally, the stored procedure:
TabAlleman
All-Star
15557 Points
2698 Posts
Re: Error passing DateTime variable to Stored Procedure
Jan 16, 2013 01:44 PM|LINK
You didn't post this part, but go to the part of your code where you actually execute the Command object. Put a breakpoint on that line, and when you get to it, look at the values of your parameters. I bet they are not set. Could be an order of operations, maybe you are executing the command BEFORE you populate the parameters.
donpisci
Member
74 Points
178 Posts
Re: Error passing DateTime variable to Stored Procedure
Jan 16, 2013 03:33 PM|LINK
Yep, the command object has all of the parameters and is executed AFTER the parameters have been populated.
TabAlleman
All-Star
15557 Points
2698 Posts
Re: Error passing DateTime variable to Stored Procedure
Jan 16, 2013 03:38 PM|LINK
Only thing left to try is run a SQL Profiler trace, execute your .net code, and look at what is actually being passed to SQL from .net.
donpisci
Member
74 Points
178 Posts
Re: Error passing DateTime variable to Stored Procedure
Jan 16, 2013 03:45 PM|LINK
Ok then- cheers Tab.
I'll report back with how I get on.
Thanks!
donpisci
Member
74 Points
178 Posts
Re: Error passing DateTime variable to Stored Procedure
Jan 16, 2013 03:51 PM|LINK
Right, just ran the Sql Profiler and this is the part for my stored procedure:
This shows that the @dateAdded variable is being passed to the stored procedure.
All of this means that I am very confused!?!?!?!
TabAlleman
All-Star
15557 Points
2698 Posts
Re: Error passing DateTime variable to Stored Procedure
Jan 16, 2013 05:25 PM|LINK
Yep, you are definitely passing the parameter with a value to the stored proc. Did you try grabbing that text from profiler and executing it in SSMS? Does it return expected results or an error?
If it returns expected results, then I got nothing. Nothing except to say there there's gotta be SOMEthing in your code somewhere that you're not seeing (and therefore not mentioning). That, and good luck! : )