How do I maintain datagridview checkbox column checkstate while filtering VB.NET

Question

0.00/5 (No votes)

See more:

I have a dgvw.It loads data from an sql database.Now, i added a checkbox column to the dgvw.My winform also has a textbox that is used to search in/filter the datagridview. Here is my code:

VB

Private Sub Contact_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    filterdata("")
Private Sub filterdata(valuetosearch As String)
    con.Open()
    Dim cmd As New SqlCommand("Select * from Contacts where CONCAT([Unique id],Prefix,[First name],[Last name],Gender,Title,Company,Phone,Mobile,Fax,[b.email],[p.email],Reference,Address,[Address 2],Country,City,Zip,Facebook,GooglePlus,Instagram,Twitter,Website,Salary,Currency,[Group],[Id/Status],Note,[Added by]) like '%" & valuetosearch & "%' ", con)
    Dim reader As SqlDataReader = cmd.ExecuteReader
    Dim dt As New DataTable
    dt.Load(reader)
    userdatagrid.AutoGenerateColumns = True
    userdatagrid.DataSource = dt
    userdatagrid.Refresh()
    con.Close()
End Sub
Private Sub searcgcon_TextChanged(sender As Object, e As EventArgs) Handles searcgcon.TextChanged
    If searcgcon.Text = "" Then
        filterdata("")
        entrylabel.Text = "There are/is " & userdatagrid.Rows.Count & " contact entries"
    Else
        filterdata(searcgcon.Text)
        entrylabel.Text = "There are/is " & userdatagrid.Rows.Count & " contact entries that contain your query :" & searcgcon.Text
    End If
End Sub

The problem with this code is, suppose i have checked a row's checkbox then i try to type something in the textbox/filter using the textbox.But as soon as i start typing the checkbox that was checked earlier becomes unchecked.I know i am wrong some where.I got this code from youtube and stackoverflow.So please help me..How do i maintain the checkstate while filtering ?

What I have tried:

On other forums, i was told that, my mistake is that i keep on loading data from the actual database but not from the filtered one. I clearly have no idea what to do or what to change in the code.Any help would be appreciated!

Posted 28-Oct-17 2:18am

Member 13336882

Updated 28-Oct-17 22:28pm

Add a Solution

Comments

A_Griffin 28-Oct-17 10:41am

Not sure why you feel the need for the If condition in the searcgcon.TextChanged event code, but anyway... when you filter, the datagrid is repopulated after a new search, so of course any checkboxes will return to their default state, unless you somehow save it first. You could, if this is a single user application, add a column in your database table corresponding to the checked state of each row and save it there. Or have an accessible array or list where you can save the ID values of checked rows, then compare against that when re-binding (DataRowBound event)

Member 13336882 28-Oct-17 12:53pm

is there no other way sir?Other forums suggested me to study Dataview,defaultview and rowfilter.They said after studying these,i will be able to achieve my goal...Can u please explain how these three are related to my problem ?

A_Griffin 28-Oct-17 14:33pm

That won't help with your problem, though it could make your code more efficient. Make your DataTable globally available, then in the searcgcon.TextChanged event you can, instead of calling the database again, you could bind to a filtered DataView of the original DataSet. However, rebinding the gridview would still clear the checkboxes. You need to somehow save their state before re-binding to your filtered search results.

Or.... rather than search the database again, loop through each cell in the datagridview and check its contents against the search criteria - and set the rows visibility = whether or not a match is found. But this could be somewhat time consuming on a large dataset and not work well on every keystroke. If you take that approach you'd be better to have a separate search button they must click rather than do it on every TextChanged event.

Member 13336882 29-Oct-17 0:21am

oke...so now i'm using search button.Can you please assist me with some code ? and what happens when the user is done with searching and wants to go back to the main datagridview/datatable ? how to i cunfigure a button the clear the textbox as well as keep the checkbox's state and give me the original datagridview/dataview?(i mean the one that is not filtered/the one on startup)??

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

A_Griffin · Answer 1 · 2017-10-28T22:28:00

Solution 1

OK - well, if you haven't already, you must first set

VB

userdatagrid.AllowUserToAddRows = False

either in the form designer, or in the form load event.
The put this in your new search button's Click event:

VB

Dim r As Integer, c As Integer
Dim bViz As Boolean = False
userdatagrid.CurrentCell = Nothing
For r = 0 To userdatagrid.Rows.Count - 1
   bViz = False
   For c = 0 To userdatagrid.Columns.Count - 1
      If userdatagrid.Rows(r).Cells(c).Value Like "*" & searcgcon.Text & "*" Then
         bViz = True
         Exit For
      End If
   Next
   userdatagrid.Rows(r).Visible = bViz
Next

Users can get back to the original dataset by simply clearing the textbox and searching again - or you could provide a separate button to do that.

Of course, doing it this way, you can simplify your original databinding, removing the unnecessary filtering clause form that.

Posted 28-Oct-17 22:28pm

A_Griffin

Updated 29-Oct-17 3:06am

v3

Comments

Member 13336882 31-Oct-17 10:38am

getting this error : Operator 'Like' is not defined for type 'Byte()' and string "*a*".

I forgot to mention that the dgvw has an image column

A_Griffin 31-Oct-17 11:09am

Well you'll need to filter the columns you search on then to only include those with string values
For c = 0 To userdatagrid.Columns.Count - 1
Select Case c
Case 2,4,7 ' whatever - the column indexes of those with string values
If userdatagrid.Rows(r).Cells(c).Value Like "*" & searcgcon.Text & "*" Then
bViz = True
Exit For
End If
Case Else
' ignore these columns
Next

Member 13336882 31-Oct-17 11:17am

this is the new code..not working :

Dim r As Integer, c As Integer
Dim bViz As Boolean = False
For c = 0 To userdatagrid.Columns.Count - 1
Select Case c
Case 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30 ' whatever - the column indexes of those with string values
If userdatagrid.Rows(r).Cells(c).Value Like "*" & searcgcon.Text & "*" Then
bViz = True
Exit For
End If
Case Else

End Select
' ignore these columns

Next

A_Griffin 31-Oct-17 13:16pm

well you need to debug and find out why, or where, at least.