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

ASP.Net: Create SELECT from ListBox with multiple selections

<asp:ListBox ID="selSearchProduct" CssClass="narrow_listbox" Font-Name="monospace" Font-Size="8" AppendDataBoundItems="true" runat="server" SelectionMode="Multiple" Rows="5">
</asp:ListBox>

Open in new window


I'm trying to modify an existing search script in a VB file that concatenates the WHERE portion of a SELECT statement based on the values of fields on the search form.  Currently, the product field is a text box, but the users want the ability to search for multiple products.

The previous code looked like this:

		If (Me.selSearchApplicationType.SelectedValue.Length > 0) Then
			If (sWhere.Length > 0) Then sWhere &= " AND "
			sWhere &= "application like @application"
			Me.sqlGridSource.SelectParameters.Add("application", "%" & Me.selSearchApplicationType.SelectedValue & "%")
		End If
		If (Me.selSearchProduct.SelectedValue.Length > 0) Then
			If (sWhere.Length > 0) Then sWhere &= " AND "
			sWhere &= "product_id like @product"
			Me.sqlGridSource.SelectParameters.Add("product", "%" & Me.selSearchProduct_id.SelectedValue & "%")
		End If

Open in new window


I need to change this code so I can check for MULTIPLE selected items from a ListBox control (simple select tag: <select id="selSearchProduct" multiple="multiple">), however, I'm having difficulty looping through the collection to create the statement:

		If (Me.selSearchApplicationType.SelectedValue.Length > 0) Then
			If (sWhere.Length > 0) Then sWhere &= " AND "
			sWhere &= "application like @application"
			Me.sqlGridSource.SelectParameters.Add("application", "%" & Me.selSearchApplicationType.SelectedValue & "%")
		End If
		If (Me.selSearchProduct.SelectedValue.Length > 0) Then
			If (sWhere.Length > 0) Then sWhere &= " AND "
			sWhere &= "("
			For Each ProductSelected in Me.selSearchProduct.Items
				sWhere &= "product_id = @product" & ProductSelected.Index
				If (ProductSelected.Index < Me.selSearchProduct.Items.Count) Then
					sWhere &= " OR "
				End If
				Me.sqlGridSource.SelectParameters.Add("product" & ProductSelected.Index, CInt(ProductSelected.Value))
			Next
			sWhere &= ")"
		End If

Open in new window


It's coming up with no results.  I want the above script to append the WHERE statement in an way that will look like this:

sWhere &= " AND (product_id = @product1 OR product_id = @product2 OR product_id = @product3)"

Open in new window


so that the end result will be:

sWhere &= " AND (product_id = 13 OR product_id = 52 OR product_id = 20)"

Open in new window


I can't see anything wrong with the loop above that would be causing it not to get results.  I know the data is there, because I can get results by creating the statement manually.

NOTE: The reason I left "If (Me.selSearchProduct.SelectedValue.Length > 0) Then"  in the statement is because Me.selSearchProduct.Items > 0 causes an error if none were selected.
0
jamesbcox1980
Asked:
jamesbcox1980
  • 5
  • 3
2 Solutions
 
jamesbcox1980Author Commented:
I think my problem is with ProductSelected.Index.  I was just going over the ListBox class at microsoft's library and I don't think Index is a property of the items.  I think I just need to create a running index in my loop.

Is there an easy way to determine how many iterations have gone by without creating a ItemIndex+=1 statement?
0
 
Paul MacDonaldDirector, Information SystemsCommented:
I think you want something like:

...
If (Me.selSearchProduct.SelectedValue.Length > 0) Then
  If (sWhere.Length > 0) Then sWhere &= " AND "
    sWhere &= "("
    For Each ProductItem in Me.selSearchProduct.Items
       If ProductItem.Selected Then
        sWhere &= "product_id = @product" & ProductSelected.Index
        If (ProductSelected.Index < Me.selSearchProduct.Items.Count) Then
          sWhere &= " OR "
        End If
        Me.sqlGridSource.SelectParameters.Add("product" & ProductSelected.Index, CInt(ProductSelected.Value))
      End If
    Next
    sWhere &= ")"
End If
...
0
 
jamesbcox1980Author Commented:
That didn't work, but I found my solution.  Thanks to you letting me know about the Selected property, I ended up having to create a separate Array to store the values, then looped through those items to create the where statement.

		If (Me.selSearchProduct.SelectedValue.Length > 0) Then
			If (sWhere.Length > 0) Then sWhere &= " AND "
			sWhere &= " ("
			For ItemsIndex = 0 To (Me.selSearchProduct.Items.Count - 1) Step 1
				If (Me.selSearchProduct.Items(ItemsIndex).Selected) Then
					ReDim Preserve SelectedProducts(UBound(SelectedProducts) + 1)
					SelectedProducts(UBound(SelectedProducts)) = Me.selSearchProduct.Items(ItemsIndex).Value
				End If
			Next
			For SelectedItemsIndex = 0 To UBound(SelectedProducts)
				sWhere &= "product_id = @product" & SelectedItemsIndex
				If (SelectedItemsIndex < UBound(SelectedProducts))
					sWhere &= " OR "
				End If
				Me.sqlGridSource.SelectParameters.Add("product" & SelectedItemsIndex, CInt(SelectedProducts(SelectedItemsIndex)))
			Next
			sWhere &= ") "
		End If

Open in new window

0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
Paul MacDonaldDirector, Information SystemsCommented:
Pushing the stuff into an array shouldn't be necessary, but I'm glad you found a solution.  I'd give you better code, but I'm not near my VS2010 system right now.
0
 
jamesbcox1980Author Commented:
Well the reason I had to use the array was so that I didn't end up with the extra "OR" on the end. I had to keep a separate count of the selected items. Nevertheless, your post gave me the answer I needed. I didnt realize I needed to differentiate between selected and non selected after the submission. Thanks a bunch.
0
 
Paul MacDonaldDirector, Information SystemsCommented:
No qualms, but I believe you intended to assign points,but have closed the question out without doing so.  If you meant to, please do so or, if necessary, flag it so an admin can get involved.

Thanks!
0
 
jamesbcox1980Author Commented:
I assigned points, but I had to attach my comment as well as part of the solution (0 points), and it just takes time for them to close it in that case. Points will be given on 3/22.
0
 
jamesbcox1980Author Commented:
See my code below.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

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