2

I'm having trouble getting my SqlParameter to work in my SqlDataSource select command. I'm trying to run a query when a new calendar date on a calendar control is selected to filter the query by the date selected. Here's what I have on the C# end:

protected void Calendar1_SelectionChanged(object sender, EventArgs e)
{
    SqlParameter para1 = new SqlParameter("@mydate", SqlDbType.DateTime);
    para1.Value = Calendar1.SelectedDate;
    SqlDataSource1.SelectParameters.Add(para1);
}

I /almost/ found what I need via this thread: How can I set the sqldatasource parameter's value?

but the solution offered by the top answer gives me the following error for the SelectParameters.Add line: "The best overloaded match ... has some invalid arguments."

How can I fix this error and get the parameter to work in my select query?

Thanks.

Community
  • 1
  • 1
erstaples
  • 1,986
  • 16
  • 31

1 Answers1

1

Per the comment in regards to Jon Skeet's answer on this link: Setting DateTime as a SqlDataSource parameter for Gridview, try changing this line to

para1.Value = Calendar1.SelectedDate.ToString();

and see if that works.

Edit Per comment from jadarnel27, try the following:

SqlDataSource1.SelectParameters.Add("@mydate", SqlDbType.DateTime, Calendar1.SelectedDate.ToString());

As SQLParameter.Value is of type object, the overload may be doing some validation on the Calendar1.SelectedDate.ToString() to match with the SQLDbType

Community
  • 1
  • 1
jordanhill123
  • 4,142
  • 2
  • 31
  • 40
  • As much as I hate to even contemplate going against Jon Skeet... I cannot see the logic in turning a date object into a string representation. There's just too much to go wrong when converting back to a date object – freefaller Mar 06 '13 at 14:15
  • @freefaller Jon Skeet is saying *not* to convert from DateTime to string in the linked answer (so agreeing with what you say). jordanhill123 here is referring to the comments by the OP of the other question below Skeet's answer that say they had to convert to string, because the DateTime wasn't valid as a parameter to the method he was using. – Josh Darnell Mar 06 '13 at 14:19
  • @freefaller I don't see the logic either but not sure where else its going wrong. It worked for the OP but I don't like it either...is it possibly something with its use in asp.net or maybe the specific calendar implementation? Just wanted to test to see if it works – jordanhill123 Mar 06 '13 at 14:21
  • And the OP here might be better off using the overload that Jon used in the linked question: `SqlDataSource1.SelectParameters.Add("@mydate", SqlDbType.DateTime, Calendar1.SelectedDate.ToString());` – Josh Darnell Mar 06 '13 at 14:23
  • Fair enough guys (@jadarnel27) - didn't quite follow exactly what was going on. my only guess is that this calendar control isn't returning a .net standard DateTime object – freefaller Mar 06 '13 at 14:23
  • @jadarnel27 do you think the overload should make difference? It's possible it does some validation since SQLParameter.Value is of type object – jordanhill123 Mar 06 '13 at 14:28
  • @jordanhill123 Indeed, that should work perfectly. I posted another answer with a different way of doing it as well. The error is happening because he's passing an object of type `SqlParamter`, when the Add method only accepts an object of type `Parameter`. This method bypasses that problem by using a different overload. My method just initiliazes the para1 variable as the proper type of object. – Josh Darnell Mar 06 '13 at 14:39
  • @jadarnel27 Agreed either way will work. Its found in `System.Web.UI.WebControls` and not `System.Data.SqlClient` where `SQLParameter` class is found – jordanhill123 Mar 06 '13 at 14:45
  • At first, neither your solution nor @jadarnel27 worked. It brought up the same invalid argument error when instantiating the new parameter object. However, I visited the link you provided in this answer and found that they used DbType.DateTime instead of SqlDbType.DateTime and that eliminated the error. Thanks! – erstaples Mar 07 '13 at 00:51