Solved

ASP.Net: Create SELECT from ListBox with multiple selections

Posted on 2011-03-17
8
423 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
8 Comments
 
LVL 4

Author Comment

by:jamesbcox1980
ID: 35158399
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 34

Accepted Solution

by:
Paul MacDonald earned 500 total points
ID: 35158562
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
ID: 35160277
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 34

Expert Comment

by:Paul MacDonald
ID: 35160525
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
 
LVL 4

Author Comment

by:jamesbcox1980
ID: 35165936
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 34

Expert Comment

by:Paul MacDonald
ID: 35167292
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
ID: 35169538
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
ID: 35196683
See my code below.
0

Featured Post

Transaction Monitoring Vs. Real User Monitoring

Synthetic Transaction Monitoring Vs. Real User Monitoring: When To Use Each Approach? In this article, we will discuss two major monitoring approaches: Synthetic Transaction and Real User Monitoring.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

707 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