Adding ControlParameter to SqlDataSource prevents query and databinding?

Question

I have a SqlDataSource that calls a stored procedure and it works fine. If I add a <ControlParameter> tag to add an additional argument, then the query never fires and the databinding never occurs. Suggestions?

This works:

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:DefaultDB %>"
    SelectCommand="SP_WHATEVER" SelectCommandType="StoredProcedure" 
    UpdateCommand="SP_WHATEVER2" UpdateCommandType="StoredProcedure">
    <SelectParameters>
        <asp:SessionParameter DefaultValue="" Name="UserName" SessionField="RP_Program" Type="String" />
    </SelectParameters>
    <UpdateParameters>
        <snip...>
    </UpdateParameters>
</asp:SqlDataSource>

When I add the ControlParameter, the databinding no longer occurs:

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:DefaultDB %>"
    SelectCommand="SP_WHATEVER" SelectCommandType="StoredProcedure" 
    UpdateCommand="SP_WHATEVER2" UpdateCommandType="StoredProcedure">
    <SelectParameters>
        <asp:SessionParameter DefaultValue="" Name="UserName" SessionField="RP_Program" Type="String" />
        <asp:ControlParameter Name="SprocArgName" ControlID="ddlFilter" PropertyName="SelectedValue" Type="String" />
    </SelectParameters>
    <UpdateParameters>
        <snip...>
    </UpdateParameters>
</asp:SqlDataSource>

The ControlParameter refers to a valid object on the page. Any other suggestions?

amit_g · Accepted Answer · 2010-11-02 18:17:05Z

13

Most likely one of the parameter is empty or null. Add CancelSelectOnNullParameter="false" to the asp:SqlDataSource and ConvertEmptyStringToNull="true" to both parameters. Once it works, tweak the parameters so that SP gets what it expects.

answered Nov 2, 2010 at 18:17

amit_g

31.3k8 gold badges67 silver badges124 bronze badges

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

3 Comments

Seth Petry-Johnson Over a year ago

"CancelSelectOnNullParameter" was exactly what I was missing. Amazing that I searched Google 4 or 5 different ways and couldn't find that; hopefully this question will help the next person with the same issue!

Bat_Programmer Over a year ago

Thanks this worked for me as well. Wrecking my brains for past few days. Surprisingly it works without that attribute in some my previous aspx files but not on a new one I just created.

SeyoS Over a year ago

I spend one hour trying to solve this....

Collectives™ on Stack Overflow

Adding ControlParameter to SqlDataSource prevents query and databinding?

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related