current community

your communities

more stack exchange communities

Stack Exchange Inbox Reputation and Badges
tour help
stack overflow careers
Sign up ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free.
up vote 0 down vote favorite

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>
share|improve this question

2 Answers 2

up vote 0 down vote

Could you do this in the code behind for the button click event? Or Make it a datetime rather than a string, then do the same thing.

  string startdate = startDate.text;
  //OR DateTime 
  employeesSqlDataSource.SelectParameters.Add("@SDate", startdate);
share|improve this answer
up vote 0 down vote accepted

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.