Error passing DateTime variable to Stored Procedure RSS

• • Reply
  

  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:

  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.

  Any ideas as to what else I could try?

  Thanks,

  John
  
  

• • Reply
  

  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

  David Winchester
  
  Please mark as ANSWER if this is the solution.
• • Reply
  

  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.

  Mani
  
  Please Mark as Answer If this is helpful.
• • Reply
  

  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:

  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

  
  
  
  

• • Reply
  

  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.

  -Tab Alleman
• • Reply
  

  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.

• • Reply
  

  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.

  -Tab Alleman
• • Reply
  

  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!

• • Reply
  

  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:

  exec sp_executesql N'dt_GetNumberOfIntDeliveryMonths',N'@dateAdded datetime,@companyId int,@grainId int,@grainTypeId int',@dateAdded='2013-01-16 00:00:00',@companyId=14,@grainId=1,@grainTypeId=1

  This shows that the @dateAdded variable is being passed to the stored procedure. 

  All of this means that I am very confused!?!?!?!
  
  

• • Reply
  

  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!   : )

  -Tab Alleman