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.

Did you try using DateTime.Parse(txtStartDate.Text).ToString("dd-MMM-yyyy")? — user572559
– user572559, Commented Jan 19, 2012 at 9:45

Fredrik Mörk · Accepted Answer · 2012-01-19 09:48:23Z

4

First, there are two things you should do:

Use DateTime.TryParse (or DateTime.TryParseExact). This will allow you to parse the string into a date, as well as getting a signal on whether the string was successfully parsed or not
Supply some sort of format for the parsing to use. Either a hard-coded one (if you require the user to enter the date in a specific format) or by passing a CultureInfo that can be used to determine the expected date format.

Secondly, you should change the SQL query to use parameters instead, as suggested by @H27Studio.

answered Jan 19, 2012 at 9:48

Fredrik Mörk

159k31 gold badges296 silver badges349 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Episodex Over a year ago

Conversion in code behind goes well, I checked it. I also checked values of e.Command.Parameters["@StartDate"] (and EndDate) and they were set ok. Error is coming from SQL Server.

H27studio Over a year ago

Thats because SQL Server is getting "{0}" and "{1}" as DateTimes instead of a real Date.

Fredrik Mörk Over a year ago

@Episodex: exactly, that's the parameter problem. Still, your parsing code is very fragile: what happens if I type "not a date" into one of the text boxes, for instance?

Episodex Over a year ago

You're right, and I gave you +1 too (while accepting other answer) for these suggestions which I appreciate :). In my particular application I'll be using jQuery calendar control with fixed format so I'm not sure if such testing is necessary here though.

H27studio · Accepted Answer · 2012-01-19 09:53:24Z

2

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 ;-)

edited Jan 19, 2012 at 9:53

answered Jan 19, 2012 at 9:47

H27studio

4671 gold badge4 silver badges12 bronze badges

2 Comments

Episodex Over a year ago

Thanks! There's no exception now :). But it seems that results are not filtered properly, I'll investigate it now. I used '{0}' format because FilterParameters use it and I didn't noticed that it is different for SelectParameters. Confusing...

Episodex Over a year ago

Ok, my mistake, now it's perfect.

Collectives™ on Stack Overflow

Passing DateTime SelectParameter to SqlDataSource

2 Answers 2

4 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related