ASP.Net 中的动态查询(SQLDataSource 中的 WHERE 子句) [英] Dynamic query in ASP.Net (WHERE clause in SQLDataSource)

问题描述

我目前正在尝试遍历一个数组(两个值)并在循环内的查询中使用这两个值.请看下面的代码.现在我的代码不起作用.我正在尝试在 SQLDataSource 的SelectParameters"标签中动态填充appType"参数，但这不起作用.

I'm currently trying to loop through an array (two values) and use both values in a query inside the loop. Please see code below. Right now my code doesn't work. I'm trying to populate dynamically the "appType" parameter within the "SelectParameters" tags of the SQLDataSource, but this won't work.

有什么建议吗?

<%

    Dim appTypes() As String = {"Extranet", "Internet"}

    For Each appType As String In appTypes

%>

<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
    ProviderName = "<%$ ConnectionStrings:CMS.ProviderName %>"
    SelectCommand = "SELECT Applications.Name as AppName, Applications.Abbr as AppAbbr, Types.Name as TypeName, Managers.LastName as LastName, Managers.FirstName As FirstName, Managers.EDKeyEmpID as EDKeyID
        FROM Types INNER JOIN (Managers INNER JOIN Applications ON Managers.ID=Applications.Manager) ON Types.ID=Applications.Type
        WHERE (Types.Name = @appType)
           ORDER BY Types.Name, Applications.Name;"
    ConnectionString="<%$ ConnectionStrings:CMS %>">
    <SelectParameters>
        <asp:Parameter DefaultValue="<%=appType%>" Name="appType"  Type="String" />
    </SelectParameters>   
</asp:SqlDataSource>

<asp:Repeater ID="Repeater1" DataSourceID="SqlDataSource1" runat="server">
    <ItemTemplate>
        <%#Eval("AppName")%> (<%=appType%>)
    </ItemTemplate>
</asp:Repeater>


<% Next %>

推荐答案

修改你的 SqlDataSource 以拥有一个 OnSelecting 成员:

Modify your SqlDataSource to have an OnSelecting member:

<asp:SqlDataSource ID="SqlDataSource1" runat="server" OnSelecting="OnSelecting"

将您的 SelectParameters 重置为正常:

Reset your SelectParameters back to normal:

<SelectParameters>
    <asp:Parameter Name="appType" Type="String" />
</SelectParameters>

在您的代码隐藏中定义此方法.根据需要实现逻辑.在这里，我显示了来自您的中继器的虚拟值.由您来决定它的实际来源(SelectedItem 或类似的东西).

Define this method in your code-behind. Implement the logic as required. Here I've shown a dummy value from your Repeater. It'll be up to you to determine how/where that actually comes from (SelectedItem or something similar).

Protected Sub OnSelecting(sender As Object, e As SqlDataSourceSelectingEventArgs) Handles SqlDataSource1.Selecting
    e.Command.Parameters("@appType").Value = Repeater1.SomeValue
End Sub

这篇关于ASP.Net 中的动态查询(SQLDataSource 中的 WHERE 子句)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！