How to add multiple SelectParameters to a SqlDataSource?

Question

I have been trying to add two SelectParameters to my SqlDataSource with no avail.

Below is my code in the aspx page

<table>
        <tr>
            <td colspan="2"><strong>Search By child</strong>
            </td>
        </tr>
        <tr>
            <td>Case Number:
            </td>
            <td>
                <asp:TextBox ID="txtCaseNumber" runat="server"></asp:TextBox>
            </td>
        </tr>
        <tr>
            <td>Last Name:
            </td>
            <td>
                <asp:TextBox ID="txtLastName" runat="server"></asp:TextBox>
            </td>
        </tr>
        <tr>
            <td>First Name:
            </td>
            <td>
                <asp:TextBox ID="txtFirstName" runat="server"></asp:TextBox>
            </td>
        </tr>
        <tr>
            <td colspan="2">
                <asp:LinkButton ID="lnkSearchChild" runat="server">Search</asp:LinkButton>
            </td>
        </tr>
    </table>

<asp:GridView ID="childListGrid" runat="server" AutoGenerateColumns="False" DataSourceID="SqlDataSource1" AllowPaging="True" AllowSorting="True" PageSize="20" BackColor="White" BorderColor="#CCCCCC" BorderStyle="None" BorderWidth="1px" CellPadding="4" ForeColor="Black" GridLines="Horizontal">
        <Columns>
            <asp:BoundField DataField="child_recordId" HeaderText="Child ID" InsertVisible="False" ReadOnly="True" SortExpression="child_recordId" />
            <asp:BoundField DataField="child_caseNumber" HeaderText="Case Number" SortExpression="child_caseNumber" />
            <asp:BoundField DataField="child_LastName" HeaderText="Last Name" SortExpression="child_LastName" />
            <asp:BoundField DataField="child_FirstName" HeaderText="First Name" SortExpression="child_FirstName" />
            <asp:TemplateField HeaderText=" " InsertVisible="False"
                SortExpression="child_recordId">
                <ItemTemplate>
                    &nbsp;&nbsp;
                        <asp:LinkButton ID="lnkViewForms" runat="server" CommandName="ViewForms" CommandArgument='<%# Bind("child_recordId") %>' OnClick="lnkViewForms_Click">View forms</asp:LinkButton>
                    &nbsp;&nbsp;
                </ItemTemplate>
            </asp:TemplateField>
        </Columns>
        <FooterStyle BackColor="#CCCC99" ForeColor="Black" />
        <HeaderStyle BackColor="#333333" Font-Bold="True" ForeColor="White" />
        <PagerStyle BackColor="White" ForeColor="Black" HorizontalAlign="Right" />
        <SelectedRowStyle BackColor="#CC3333" Font-Bold="True" ForeColor="White" />
        <SortedAscendingCellStyle BackColor="#F7F7F7" />
        <SortedAscendingHeaderStyle BackColor="#4B4B4B" />
        <SortedDescendingCellStyle BackColor="#E5E5E5" />
        <SortedDescendingHeaderStyle BackColor="#242121" />
    </asp:GridView>

<asp:SqlDataSource ID="SqlDataSource5" runat="server" ConnectionString="<%$ ConnectionStrings:eci_conn %>" SelectCommand="SELECT ... FROM ... WHERE [child_LastName] LIKE @lname + '%' AND [child_FirstName] LIKE @fname + '%' ORDER BY [child_LastName]">
        <SelectParameters>
            <asp:ControlParameter Name="lname" DbType="String" />
            <asp:ControlParameter Name="fname" DbType="String" />
        </SelectParameters>
    </asp:SqlDataSource>

And in my code behind I tried the following

    Dim lNameParam As New Parameter("@lname", DbType.String)
    Dim fNameParam As New Parameter("@fname", DbType.String)
    lNameParam.DefaultValue = txtLastName.Text.Trim()
    fNameParam.DefaultValue = txtFirstName.Text.Trim()
    SqlDataSource5.SelectParameters.Add(lNameParam)
    SqlDataSource5.SelectParameters.Add(fNameParam)
    childListGrid.DataSourceID = "SqlDataSource5"

and also this

SqlDataSource5.SelectParameters.Add("@lname", txtLastName.Text.Trim())
SqlDataSource5.SelectParameters.Add("@fname", txtFirstName.Text.Trim())
childListGrid.DataSourceID = "SqlDataSource5"

and this

SqlDataSource5.SelectParameters("lname").DefaultValue = txtLastName.Text.Trim()
SqlDataSource5.SelectParameters("fname").DefaultValue = txtFirstName.Text.Trim()
childListGrid.DataSourceID = "SqlDataSource5"

Nothing seems to be working, what else can I try?

thanks

Answer 1

You only need to do one or the other (markup or codebehind) when adding parameters to your SqlDataSource. Take a look at this:

<SelectParameters>
    <asp:ControlParameter Name="lname" ControlID="txtLastName" PropertyName="Text" />
    <asp:ControlParameter Name="fname" ControlID="txtFirstName" PropertyName="Text" />
</SelectParameters>

At this point, both of your ControlParameters have been added to your SqlDataSource. There is no need to add them again in codebehind.

Notice that I added the "ControlID" and "PropertyName" properties to the markup. I'm not sure how you were getting by without them, but they are necessary for the parameter to work correctly.

Since you are changing datasources during your postback, you need to make sure and call databind after making that change:

childListGrid.DataSourceID = "SqlDataSource5";
childListGrid.DataBind();

Answer 2

Try this

SqlDataSource5.SelectParameters("lname").DefaultValue = txtLastName.Text.Tostring
SqlDataSource5.SelectParameters("fname").DefaultValue = txtFirstName.Text.Tostring
childListGrid.DataSource = "SqlDataSource5"
childListGrid.Databind