Build SQL String from selectedItems in ListBox in

Posted on 2006-05-28
1 Endorsement
Last Modified: 2012-05-05
VB.Net 2003

Multi-Selection List Box
Datatype String    Domain Name  e.g.,
lstDomainName  is the name of the control

I have a list box with different domain names, and also a selection of the text "All"
I'd like to generate an email list based on the domains the user selects, or based on all domains if the user selects "All"

The resulting SQL statment should end up something like this  

Select EmailAddress,Created   ' If the customer selects a domain or multiple domains
From tblUsers
Where Domain = Whats in lstDomainName

or Select EmailAddress,Created  ' If the customer selects All
From tbl Users

Thanks in advance
Question by:rmartin15
    LVL 17

    Accepted Solution

       Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            'Build the base SQL string
            Dim strSQL As String = "SELECT EmailAddress,Created FROM tblUsers "
            Dim selIndices As ListBox.SelectedIndexCollection

            'Get the selected indices
            selIndices = lstDomainName.SelectedIndices

            'If there are no items selected, exit
            If selIndices.Count = 0 Then Exit Sub

            'If the number of items selected is less than the total number of items, build a per-item list
            If selIndices.Count <> lstDomainName.Items.Count Then
                strSQL &= "WHERE Domain IN ("

                'Loop through each item and add it to the string
                For i As Integer = 0 To selIndices.Count - 1
                    strSQL &= "'" & lstDomainName.Items.Item(selIndices(i)) & "',"

                'Remove the trailing comma and finish the string with a paren
                strSQL = strSQL.Remove(strSQL.Length - 1, 1) & ")"

            End If

            'View the finished string

        End Sub

    Notes:  this assumes that at some point you will click a button to get a list of users.  If not, just use this code in the listbox's SelectedIndexChanged event.  This also uses an IN statement...which may or may not be the best course of action, depending upon the number of items in the listbox, and how many users you have.

    Author Comment

    How do I test if "All" was selected in lstDomainName ?   It is the first item in the list, and is also the assigned default value when the form is loaded, or is reset.
    LVL 17

    Expert Comment

    In that case, you could simply check if selIndices(0) = 0.  Since "All" is the first item, it will have index 0...and since the index collection is in order, you'll be able to look in slot 0 every time to see if it is chosen.

    Author Comment


    Featured Post

    Courses: Start Training Online With Pros, Today

    Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

    Join & Write a Comment

    I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
    Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now