Validate dates before executing sql in asp.net

Question

I have a data grid bound to a data source in the typical way (simplified for brevity):

<asp:SqlDataSource ID="ds" runat="server" ConnectionString="xxx"
     ProviderName="yyy" SelectCommand="SELECT "a from A where date > ?">
    <SelectParameters>
        <asp:ControlParameter ControlID="txtDateFrom" Name="fromDate" PropertyName="Value" Type="DateTime"/>
    </SelectParameters>

...

I also have a custom validator on txtDateFrom

<asp:CustomValidator ID="fromDateCorrectVal" runat="server" ControlToValidate="txtDateFrom"  ErrorMessage="From date is not valid" OnServerValidate="fromDateCorrectVal_ServerValidate" Display="None"/>

Where the code behind is similar to this:

protected void fromDateCorrectVal_ServerValidate(object source, ServerValidateEventArgs args)
{
    DateTime parsedDate;
    if (!DateTime.TryParse(tryDate, out parsedDate))
    {
            args.IsValid = false;
            fromDateCorrectVal.ErrorMessage = "Invalid from date";
    }

}

But this doesn't seem to work! If I enter garbage into the date field, I get an ASP error - parsing invalid date tokens.

How can I stop the SQL attempting to fire, if the validation fails? (I suspect I need to check for the page isValid at some point, but my efforts to try this in the datasource_Selecting event doesn't seem to work)

Thanks in advance for any help

Ryan

Answer 1

You can handle this with the SqlDataSource.Selecting event and call Cancel.

By adding an event handler delegate to handle the Selecting event, you can perform any additional preprocessing required or cancel the database query entirely. Because the SqlDataSourceSelectingEventArgs class is derived from the SqlDataSourceCommandEventArgs class, you can cancel a pending SqlDataSource database query by setting the Cancel property to true. You can examine and manipulate the CommandText, Parameters collection, and other database query properties prior to running the query by accessing the DbCommand object exposed by the Command property. You can also examine the DataSourceSelectArguments object that is passed to the Select method by accessing the Arguments property.

http://msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.sqldatasourceselectingeventargs.aspx