vb.net 如何将多个 SelectParameters 添加到 SqlDataSource？

How to add multiple SelectParameters to a SqlDataSource?

提问by esausilva

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

我一直在尝试将两个 SelectParameters 添加到我的 SqlDataSource 中，但无济于事。

Below is my code in the aspx page

下面是我在 aspx 页面中的代码

<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

谢谢

回答by Josh Darnell

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

将参数添加到 SqlDataSource 时，您只需要执行其中一项（标记或代码隐藏）。看看这个：

<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.

此时，您的两个 ControlParameters 都已添加到您的 SqlDataSource。无需在代码隐藏中再次添加它们。

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.

请注意，我在标记中添加了“ControlID”和“PropertyName”属性。我不确定没有它们你是如何度过的，但它们是参数正常工作所必需的。

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

由于您在回发期间更改数据源，因此您需要确保在进行更改后调用 databind：

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

回答by Ishey4

Try this

尝试这个

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