CODEWITHSUNDEEP
asp.netdata-bindingsqldatasourcecontrolparameter
Seth Petry-Johnson
Seth Petry-Johnson

Reputation: 12085

Adding ControlParameter to SqlDataSource prevents query and databinding?

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?

Upvotes: 5

Views: 6547

Answers (1)

amit_g
amit_g

Reputation: 31250

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.

Upvotes: 13

Related Questions