locked
SqlDataSource and setting ControlParameters RRS feed

  • Question

  • User1487642518 posted

    Hi. I'm not a web developer. I'm a data warehouse dev but I've had this task dumped on me. My experience of asp.net and c# is very basic.

    I've got a data list view which is populated by a sql command and i'm trying to add a parameter so users can filter the list.

    I've managed to get the connection to the stored proc in sql but it refuses to recognise the parameter.

    Here's some code:

    The input box is defined like this

    <asp:TextBox ID="txtSearch" runat="server" Text="" />

    and then i've got this on the same page

    <asp:SqlDataSource 
                ID="SqlDataSource1" 
                runat="server" 
                ConnectionString="<%$ ConnectionStrings:ConnTechnicalForms %>" 
                SelectCommand="usp_SearchPI">
            <SelectParameters>
                <asp:ControlParameter ControlID="txtSearch" PropertyName="Text" DBType="String" DefaultValue="" Name="SearchTerm"  />
            </SelectParameters>
        </asp:SqlDataSource>

    The message I'm getting back is:

    Procedure or function 'usp_SearchPI' expects parameter '@SearchTerm', which was not supplied

    My stored proc has just one parameter, @SearchTerm which is a varchar(100).

    Th proc is working but the parameter isn't being passed. Can anybody help please?

    Thanks,

    Nick

    Tuesday, November 10, 2015 4:36 AM

Answers

  • User603616845 posted

    Hi,

    You need to add SelectCommandType="StoredProcedure" with your sqldatasournce and also pass the default value for this.

    <asp:SqlDataSource 
                ID="SqlDataSource1" 
                runat="server" 
                ConnectionString="<%$ ConnectionStrings:ConnTechnicalForms %>" 
                SelectCommand="usp_SearchPI" SelectCommandType="StoredProcedure">
            <SelectParameters>
                <asp:ControlParameter ControlID="txtSearch" PropertyName="Text" DBType="String" DefaultValue="Pass here default value" Name="SearchTerm"  />
            </SelectParameters>
        </asp:SqlDataSource>

    Hope this will help you.

    thanks

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, November 10, 2015 4:50 AM

All replies

  • User603616845 posted

    Hi,

    You need to add SelectCommandType="StoredProcedure" with your sqldatasournce and also pass the default value for this.

    <asp:SqlDataSource 
                ID="SqlDataSource1" 
                runat="server" 
                ConnectionString="<%$ ConnectionStrings:ConnTechnicalForms %>" 
                SelectCommand="usp_SearchPI" SelectCommandType="StoredProcedure">
            <SelectParameters>
                <asp:ControlParameter ControlID="txtSearch" PropertyName="Text" DBType="String" DefaultValue="Pass here default value" Name="SearchTerm"  />
            </SelectParameters>
        </asp:SqlDataSource>

    Hope this will help you.

    thanks

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, November 10, 2015 4:50 AM
  • User1487642518 posted

    Thank you very much. That has nailed it.

    Tuesday, November 10, 2015 5:21 AM