• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 367
  • Last Modified:

Build SQL String from selectedItems in ListBox in VB.net

VB.Net 2003

Multi-Selection List Box
Datatype String    Domain Name  e.g.  aol.com,hotmail.com
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
1
rmartin15
Asked:
rmartin15
  • 2
  • 2
1 Solution
 
ZeonFlashCommented:
   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)) & "',"
            Next

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

        End If

        'View the finished string
        MsgBox(strSQL)

    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.
0
 
rmartin15Author Commented:
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.
0
 
ZeonFlashCommented:
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.
0
 
rmartin15Author Commented:
Thanks
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now