Solved

ASP.Net: Create SELECT from ListBox with multiple selections

Posted on 2011-03-17
8
418 Views
Last Modified: 2012-05-11
<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
Comment
Question by:jamesbcox1980
  • 5
  • 3
8 Comments
 
LVL 4

Author Comment

by:jamesbcox1980
Comment Utility
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
 
LVL 33

Accepted Solution

by:
paulmacd earned 500 total points
Comment Utility
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
 
LVL 4

Assisted Solution

by:jamesbcox1980
jamesbcox1980 earned 0 total points
Comment Utility
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
 
LVL 33

Expert Comment

by:paulmacd
Comment Utility
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 4

Author Comment

by:jamesbcox1980
Comment Utility
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
 
LVL 33

Expert Comment

by:paulmacd
Comment Utility
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
 
LVL 4

Author Comment

by:jamesbcox1980
Comment Utility
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
 
LVL 4

Author Closing Comment

by:jamesbcox1980
Comment Utility
See my code below.
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

728 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

12 Experts available now in Live!

Get 1:1 Help Now