Solved

How to filter gridview with checkboxlist if select 2 or more checkboxes?!

Posted on 2011-02-28
6
2,153 Views
Last Modified: 2013-11-05
Hello,

I need help.
I'm a begginer, a student, and still learning asp.net with vb.net. I have stucked at filtering gridview with ComboBoxList as multiple checkboxes, if selected more then 2.
I have add a gridview with all columns from database (id, firstname, secondname), and assigned Comboboxlist control to gridview. It is working, but only if one checkbox is selected. But I want to retrieve, also rows if second, or third checkbox is selected.

SELECT * FROM [main] WHERE ([FirstName] = ?)

Open in new window


Thank you in advance for help to anyone.
printscren1.jpg
printscren2.jpg
0
Comment
Question by:dejandejanovic
  • 3
  • 2
6 Comments
 
LVL 9

Assisted Solution

by:joshbula
joshbula earned 150 total points
Comment Utility
In the checkboxlist, add AutoPostBack="true" then in the code-behind create the OnItemChecked event.  There, you can do a For Each to check if each list item is checked or not, and add it to the Where clause"


Dim valueList as String = ""
Dim li As ListItem
For Each li in CheckBoxList1.Items
   If li.Selected Then
      valueList += li.Text & ", "
   End If
Next

Dim valueListLength as Integer = Len(valueList)
If valueListLength > 1 Then
valueList = LEFT(valueList, valueListLength - 2)      '(removes the ", " from the last one)
End If

SqlDataSource.SelectCommand = "SELECT * FROM NameTable WHERE Name IN(" & valueList & ")"
GridView1.DataBind()

0
 
LVL 8

Expert Comment

by:sriggumma
Comment Utility
A code like below will work for you in case of multiple items selected.

CODE EXAMPLE
==============
Dim mySqlQuery as String = "SELECT * FROM [main] WHERE [FirstName] IN "

Dim strchklist As String = ""
     Dim li As ListItem
     For Each li In CheckBoxList1.Items
          If li.Selected Then
               strchklist += li.Text + " "
          End If
     Next

     If strchklist = "" Then
          Response.Write("No item Selected")
     Else
          mySqlQuery = mySqlQuery + " (" + strchklist + ")"
          ' EXECUTE YOUR QUERY HERE.
     End If


Hope this helps.
0
 
LVL 8

Expert Comment

by:sriggumma
Comment Utility
-- UPDATED VERSION of solution.

A code like below will work for you in case of multiple items selected.

CODE EXAMPLE
==============
      Dim csvOfSelectedItems As String = ""
      Dim li As ListItem
      For Each li In CheckBoxList1.Items
            If li.Selected Then
                  csvOfSelectedItems += li.Text + ","
            End If
      Next

      If csvOfSelectedItems = "" Then
            Response.Write("No item Selected")
      Else
            ' Trim the last comma char
            csvOfSelectedItems = csvOfSelectedItems.Substring(1, csvOfSelectedItems.Length - 1)

            ' Prepare Query string
            Dim sqlQuery as String = "SELECT * FROM [main] WHERE [FirstName] IN (" + csvOfSelectedItems + ")"

            ' EXECUTE sqlQuery HERE and use the results.
      End If


Hope this helps.
Dim csvOfSelectedItems As String = ""
	Dim li As ListItem
	For Each li In CheckBoxList1.Items
		If li.Selected Then
			csvOfSelectedItems += li.Text + ","
		End If
	Next

	If csvOfSelectedItems = "" Then
		Response.Write("No item Selected")
	Else
		' Trim the last comma char
		csvOfSelectedItems = csvOfSelectedItems.Substring(1, csvOfSelectedItems.Length - 1)

		' Prepare Query string
		Dim sqlQuery as String = "SELECT * FROM [main] WHERE [FirstName] IN (" + csvOfSelectedItems + ")"

		' EXECUTE sqlQuery HERE and use the results.
	End If

Open in new window

0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:dejandejanovic
Comment Utility
Thank you for replies, but none code is working for me.

I have add a zip file. I hope someone could check it, and fix it.
thank....
Training.zip
0
 
LVL 8

Accepted Solution

by:
sriggumma earned 350 total points
Comment Utility
Change the code like below in Default.aspx.vb
It would work.

Protected Sub CheckBoxList1_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs) Handles CheckBoxList1.SelectedIndexChanged
        Dim csvOfSelectedItems As String = ""
        Dim li As ListItem
        For Each li In CheckBoxList1.Items
            If li.Selected Then
                csvOfSelectedItems += "'" + li.Text + "',"
            End If
        Next

        If csvOfSelectedItems = "" Then
            Response.Write("No item Selected")
        Else
            ' Trim the last comma char
            csvOfSelectedItems = csvOfSelectedItems.Substring(0, csvOfSelectedItems.Length - 1)

            ' Prepare Query string
            Dim sqlQuery As String = "SELECT * FROM [main] WHERE [FirstName] IN (" + csvOfSelectedItems + ")"

            ' EXECUTE sqlQuery HERE and use the results.
            PersonSqlDataSource.SelectCommand = sqlQuery
        End If
    End Sub

Open in new window


Hope this helps.
0
 

Author Closing Comment

by:dejandejanovic
Comment Utility
It works. Thanks.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

I have developed many web applications with asp & asp.net and to add and use a dropdownlist was always a very simple task, but with the new asp.net, setting the value is a bit tricky and its not similar to the old traditional method. So in this a…
One of the pain points with developing AJAX, JavaScript, JQuery, and other client-side behaviors is that JavaScript doesn’t allow for cross domain request for pulling content. For example, JavaScript code on www.johnchapman.name could not pull conte…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

763 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

8 Experts available now in Live!

Get 1:1 Help Now