Solved

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

Posted on 2011-02-28
6
2,203 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
ID: 35001863
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
ID: 35003148
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
ID: 35003231
-- 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

Author Comment

by:dejandejanovic
ID: 35011936
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
ID: 35013190
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
ID: 35020912
It works. Thanks.
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

In this Article, I will provide a few tips in problem and solution manner. Opening an ASPX page in Visual studio 2003 is very slow. To make it fast, please do follow below steps:   Open the Solution/Project. Right click the ASPX file to b…
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…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

777 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