5

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?

Brian Koser
  • 439
  • 1
  • 12
  • 28
Seth Petry-Johnson
  • 11,845
  • 7
  • 49
  • 69

1 Answers1

12

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.

amit_g
  • 30,880
  • 8
  • 61
  • 118
  • 3
    "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! – Seth Petry-Johnson Nov 03 '10 at 16:24
  • 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. – Bat_Programmer Oct 18 '15 at 22:03
  • I spend one hour trying to solve this.... – SeyoS Mar 15 '23 at 21:36