Dynamic query in ASP.Net (WHERE clause in SQLDataSource)

Question

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.

Any suggestion?

<%

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

Suggestion #1: Accept answers on more of your past questions. — StriplingWarrior, May 11 '12 at 20:51

score 2 · Answer 1 · answered May 11 '12 at 20:56

Modify your SqlDataSource to have an OnSelecting member:

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

Reset your SelectParameters back to normal:

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

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

Thanks for the help. The value "appType" is coming from an array that I'm looping through. I've updated the code (in my original post) with the array declaration and the loop. How can I access that value from the loop (appType) in the sub ? Thanks again — Mark Marina, May 14 '12 at 16:00

Dynamic query in ASP.Net (WHERE clause in SQLDataSource)

1 Answers1

Linked