Passing DateTime SelectParameter to SqlDataSource

Question

I have a GridView and SqlDataSource for it. SelectCommand is something like

SELECT * FROM Transfers WHERE Timestamp >= '{0}' AND Timestamp <= '{1}'

Parameters of SqlDataSource:

<SelectParameters>
    <asp:Parameter Name="StartDate" Type="DateTime" />
    <asp:Parameter Name="EndDate" Type="DateTime" />
</SelectParameters>

Then in code behind I set this parameters:

protected void gvTransfers_Selecting(object sender, SqlDataSourceSelectingEventArgs e)
{
    e.Command.Parameters["@StartDate"].Value = DateTime.Parse(txtStartDate.Text);
    e.Command.Parameters["@EndDate"].Value = DateTime.Parse(txtEndDate.Text);
}

I debugged the code above and it runs and sets good DateTime values. But when GridView is being databound it throws exception

Conversion failed when converting date and/or time from character string.

I spent hours on looking how to pass DateTime in a proper way. What am I doing wrong? Should I change SQL, markup or code behind?

I also tried FilterParameters and getting dates from textboxes in various formats but without success.

Answer 1

You have to change

SELECT * FROM Transfers WHERE Timestamp >= '{0}' AND Timestamp <= '{1}'

for

SELECT * FROM Transfers WHERE Timestamp >= @StartDate AND Timestamp <= @EndDate

For more info you can check this MSDN post: http://msdn.microsoft.com/en-us/library/z72eefad.aspx ;-)