SqlDataSource requery on button click

Question

I've got an issue with asp:SqlDataSource. I'm trying to pull some data based on a start and end date. I want this data to pull the last 24 hours on load but right now I'm just trying to pull it all back for testing. I have two asp:TextBox controls, a start date and an end date.

Nothing special, should be simple...

My problem is binding the Stored Procedure param to an asp:TextBox. The TextBox puts the text I type into the text box into a 'Text' attribute. Seems to make sense but the problem is that asp converts this control into an input tag and then puts the text entered into a value attribute.

Now, when configuring the SqlDataSource it wants to use controlName.Text which on the actual page doesn't exist. It's controlName.value.

If I try to bind the asp:ControlParameter to controlName.value I get an error message that value is not a property of TextBox. That's right... It's a property of input that this is going to be... So it won't let me do that.

If I just have a straight sql query I get data back. When I test the stored procedure with all the default values I get data back. As soon as I tie in the control I get nothing. I do handle nulls and empty strings passed into the date fields so I don't think it's that.

In short, I'm lost. HELP!

Initial HTML:

<asp:TextBox ID="startDate" Width="10em" runat="server" AutoPostBack="true"></asp:TextBox>

Generated HTML:

<input type="text" style="width:10em;" id="startDate" onkeypress="if (WebForm_TextBoxKeyHandler(event) == false) return false;" onchange="javascript:setTimeout('__doPostBack(\'startDate\',\'\')', 0)" value="2000/07/13 00:00" name="startDate">

Fails:

        <asp:SqlDataSource ID="employeesSqlDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:DbConnectionString %>" SelectCommand="SPRT_GetRecords" SelectCommandType="StoredProcedure">
            <SelectParameters>
                <asp:ControlParameter ControlID="startDate" Name="StartDate" PropertyName="Text" Type="DateTime" />
                <asp:ControlParameter ControlID="endDate" Name="EndDate" PropertyName="Text" Type="DateTime" />
                <asp:Parameter Name="GetLogs" Type="Boolean" />
                <asp:Parameter Name="LogType" Type="Decimal" />
            </SelectParameters>
        </asp:SqlDataSource>

Fails:

       <asp:SqlDataSource ID="employeesSqlDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:DbConnectionString %>" SelectCommand="SPRT_GetRecords" SelectCommandType="StoredProcedure">
            <SelectParameters>
                <asp:Parameter Name="StartDate" Type="DateTime" />
                <asp:Parameter Name="EndDate" Type="DateTime" />
                <asp:Parameter Name="GetLogs" Type="Boolean" />
                <asp:Parameter Name="LogType" Type="Decimal" />
            </SelectParameters>
        </asp:SqlDataSource>

Works but doesn't pull in the control input:

        <asp:SqlDataSource ID="employeesSqlDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:DbConnectionString %>" SelectCommand="declare @SDate datetime
            set @SDate = DateAdd(dd, -100, GetDate())
            EXEC SPRT_GetRecords @StartDate = @SDate, @EndDate = null, @GetLogs = 0, @LogType = 0"></asp:SqlDataSource>

Answer 1

Ok, after talking it over with someone else and not coming up with much I was able to take a look with a different perspective and figure out the answer.

I assumed that since I was setting a default value in my Stored Procedure that i didn't have to specify a value for the optional parameters. This was not true...

The solution to my problem was to add the default values. Note the lower two asp:parameters now have default values. I wish this would have thrown an error and let it bubble up to me instead of silently blowing up and not returning any results.

        <asp:SqlDataSource ID="employeesSqlDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:DbConnectionString %>" SelectCommand="SPRT_GetRecords" SelectCommandType="StoredProcedure">
            <SelectParameters>
                <asp:ControlParameter ControlID="startDate" Name="StartDate" PropertyName="Text" Type="DateTime" />
                <asp:ControlParameter ControlID="endDate" Name="EndDate" PropertyName="Text" Type="DateTime" />
                <asp:Parameter DefaultValue="false" Name="GetLogs" Type="Boolean" />
                <asp:Parameter DefaultValue="0" Name="LogType" Type="Decimal" />
            </SelectParameters>

In addition I'm setting default values for the start/end dates as follows in the Page_Load method:

    if (!IsPostBack)
    {
        // Default the grid to the last 24 hours worth of data
        employeesSqlDataSource.SelectParameters["startDate"].DefaultValue = DateTime.Now.AddDays(-1).ToString();
        employeesSqlDataSource.SelectParameters["endDate"].DefaultValue = DateTime.Now.ToString();
        ...
        ...
    }

To get the button to work I simply am doing some validation and then calling DataBind() on the SqlDataSource in the onclick.

protected void searchButton_Click(object sender, EventArgs e)
{
    CheckDates();

    if (string.IsNullOrEmpty(startDate.Text)) employeesSqlDataSource.SelectParameters["startDate"].DefaultValue = SqlDateTime.MinValue.ToString();
    if (string.IsNullOrEmpty(endDate.Text)) employeesSqlDataSource.SelectParameters["endDate"].DefaultValue = SqlDateTime.MaxValue.ToString();

    employeesSqlDataSource.DataBind();
}

The important bit is the last line.

Hope this helps someone else in need.