INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Filter Gridview from MultiSelect Listbox

Filter Gridview from MultiSelect Listbox

(OP)
I am coding in VB

I have a listbox with multiselect enabled.

CODE -->

<asp:ListBox ID="ListBox1" runat="server" DataSourceID="SqlDataSource1" DataTextField="Requirements_Heading" 
            DataValueField="ID" AutoPostBack="true" SelectionMode="Multiple" OnSelectedIndexChanged="ListBox1_SelectedIndexChanged" Rows="8"></asp:ListBox>
        <asp:SqlDataSource runat="server" ID="SqlDataSource1" 

I am able to verify I have captured multiple values by displaying them in a label- the code behind is fired OnSelectedIndexChanged event in Listbox:

CODE -->

Dim stringBuilder = New StringBuilder()
        Dim delimiter = ", "
        For Each item As ListItem In ListBox1.Items
            If item.Selected Then
                stringBuilder.AppendFormat("{0}{1}", item, delimiter)
            End If
Next
        Label1.Text = "These are the requirements you have selected: " & stringBuilder.ToString() 

What I want to do is use the values from the Listbox in the WHERE statement (I am sure I will be able to use IN but don't know how to deliver the values) to filter a gridview. At the moment, it changes to give me only one of the selections from the listbox, not all of them:

CODE -->

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="ID" DataSourceID="SqlDataSource2">
            <Columns>
                <asp:BoundField DataField="Company Name" HeaderText="Company Name" SortExpression="Company Name"></asp:BoundField>
                <asp:BoundField DataField="ID" HeaderText="ID" ReadOnly="True" InsertVisible="False" SortExpression="ID"></asp:BoundField>
            </Columns>
        </asp:GridView>
        <asp:SqlDataSource runat="server" ID="SqlDataSource2" 
            ConnectionString='<%$ ConnectionStrings:HSCConvertedConnectionString %>' 
            SelectCommand="SELECT tblCompany.CompanyName, tblSupplierMapCodes.ID FROM ... WHERE (tblSupplierMapCodes.ID = @ID)">
            <SelectParameters>
                <asp:ControlParameter ControlID="ListBox1" PropertyName="SelectedValue" Name="ID"></asp:ControlParameter>
            </SelectParameters>
        </asp:SqlDataSource> 

RE: Filter Gridview from MultiSelect Listbox

(OP)
Just in case it helps, I have tried using vb from an Access database mixed with some asp elements to illustrate what I am after...

CODE -->

Protected Sub Illustration()
        Dim strSQLRowSource As String
        Dim strWhere As String
        Dim cmd As New SqlCommand

        'Create the sql statement
        strSQLRowSource = ("SELECT tblCompany.[Company Name], tblSupplierMapCodes.ID FROM ... WHERE ~") , con)

        'Insert the string into the where clause
        strWhere = " tblSupplierMapCodes.ID IN ("
        For Each item In ListBox1.Items
            strWhere = strWhere & item.value & ","
        Next
        Replace(strSQLRowSource, "~", strWhere)

        'Trim the last comma
        strWhere = Left(strWhere, Len(strWhere) - 1) & ") "

        'Execute the query
        cmd.ExecuteNonQuery()
        con.Close()
    End Sub 

RE: Filter Gridview from MultiSelect Listbox

My first suggestion would be to get rid of the SqlDataSource controls all together. They are only good for very simple querying and become cumbersome once the SQL you need to write gets even a little bit complex, like in your case. Also, you cannot debug using them.

I assume you are using a SQL Server database. If so, then if you know what stored procedures are, you should write and use them. If not, this is a good time to learn. It will be very useful in your career. You cannot depend on those datasource controls.

At the very least, generate the SQL yourself and execute it.
So you need to change this:

CODE

SELECT tblCompany.CompanyName, tblSupplierMapCodes.ID FROM ... WHERE (tblSupplierMapCodes.ID = @ID) 
To something like this:

CODE

You need to use the IN clause
SELECT tblCompany.CompanyName, tblSupplierMapCodes.ID FROM ... WHERE (tblSupplierMapCodes.ID IN (1,2,5,6)) 
This is assuming that the ID column is numeric if not, each value would have to be placed in quotes IN ('1', '2', '6')

You already have code to get each selected value from the listbox, you can use that while generating your SQL statement.

RE: Filter Gridview from MultiSelect Listbox

(OP)
Fixed it!

HTML has a ListBox and Gridview:

CODE -->

<div>
            <asp:ListBox ID="ListBox1" runat="server" AutoPostBack="true"
                DataSourceID="SqlDataSource1" DataTextField="Requirements_Heading" DataValueField="ID" SelectionMode="Multiple"
                Rows="8" OnSelectedIndexChanged="ListBox1_SelectedIndexChanged"></asp:ListBox>
            <asp:SqlDataSource runat="server" ID="SqlDataSource1"
                ConnectionString='<%$ ConnectionStrings:... %>'
                SelectCommand="SELECT ID, [Requirements Heading] AS Requirements_Heading FROM tblSupplierMapCodes"></asp:SqlDataSource>
            <br />
            <br />
            <asp:GridView ID="GridView1" runat="server" DataKeyNames="ID"></asp:GridView>
        </div> 

In code behind (I have coded in VB, and removed connection strings, table names etc to keep it as clean and simple to follow as possible):

CODE -->

Imports System.Collections.Generic
Imports System.Web
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Data.SqlClient
Imports System.Configuration
Imports System.Collections
Imports System.Data

Partial Class MultiSelectListBoxGridView
    Inherits System.Web.UI.Page
    Dim connection As New SqlConnection(ConfigurationManager.ConnectionStrings("...").ToString())
    Private SelectedMapId As New System.Text.StringBuilder()

    Protected Sub Page_Load(sender As Object, e As EventArgs)
        ListBox1.SelectionMode = ListSelectionMode.Multiple
    End Sub

    Protected Sub LoadCompanies()
        Using connection As New SqlConnection()
            connection.ConnectionString = ConfigurationManager _
                        .ConnectionStrings("...").ConnectionString
            Using command As New SqlCommand("SELECT  FROM ", connection)
                connection.Open()
                command.CommandText += " WHERE tbl123.ID IN (" + SelectedMapId.ToString() + ")"
                GridView1.DataSource = command.ExecuteReader()
                GridView1.DataBind()
            End Using
        End Using
    End Sub

    Protected Sub ListBox1_SelectedIndexChanged(sender As Object, e As EventArgs)
        For Each MapID As ListItem In ListBox1.Items
            If MapID.Selected Then
                SelectedMapId.AppendFormat("{0},", MapID.Value)
                'Build Comma-separated values
            End If
        Next
        SelectedMapId.Remove(SelectedMapId.Length - 1, 1)
        'Remove last comma
        LoadCompanies()

    End Sub

End Class 

Hope this helps anyone else who is looking to populate a gridview with multiple selections from a listbox.

Any and all improvements are most welcome.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close