Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

ASP.Net: Create SELECT from ListBox with multiple selections

Posted on 2011-03-17
8
Medium Priority
?
426 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
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 2000 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
Industry Leaders: 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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

972 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