Clear a column in datagridview

[philiop]

Hi,

I am working on a program that looks at a load of sql tables containing client information.

I have a databound table called FileDatagridview that contains all of the client information. Due to the nature of the work we do we have a lot of need to search, filter and select multiple client. I found that selecting clients using ctrl+click wasnt working because if a person then tried to filter to find the next client then the selection was lost. So I added an integer column called IsSelected to the sql table and shown it as a checkbox column on the FileDataGridView. This allows people to select and filter clients and then sort by selected or not.

How ever I need this column cleared before the table updates and saves otherwise the selections will pass to other users.

I've tried a number of ways and currently i have this. Pardon the poor coding but was just rough to see if it worked.

Code sorts the clients by IsSelected, then trys to change all the values from the bottom up to 0 as the sort puts all the "1" or Selected clients to the bottom.

        Me.FileBindingSource.Sort = "IsSelected"
        Dim iR, tR As Integer
  
        Dim rT As Integer = FileDataGridView.Rows.Count - 1
        For iR = 0 To 99999
            Try
                If FileDataGridView.Rows(rT).Cells(5).Value = 1 Then
                    FileDataGridView.Rows(rT).Cells(5).Value = 0
                    FileDataGridView.CurrentCell = FileDataGridView.Rows(rT - 1).Cells(5)
                Else
                    FileDataGridView.CurrentCell = FileDataGridView.Rows(rT - 1).Cells(5)
                    Exit For
                End If
            Catch
            End Try
        Next

This almost works except that the last selected appears as unselected how ever when i refilter or click off that client the selection shows up as still being there.

Can anyone think of a better way to clear this column or a better solution to the selection problem.

Thanks

 

[Budius]

hi,

I'm not sure if I proper understood the problem, but if I did the solution to it is much simpler:

        For Each myRow As DataGridViewRow In DataGridView1.Rows
            myRow.Cells("IsSelected").Value = 0
        Next

 

[philiop]

Would this be suitable for a large database?

Currently we have 26,000 clients and we are expected to rise to around 80,000 by the end of this year. So it needs to be able to run quickly on a large database

 

[Budius]

honest answer: I don't know, I don't think I've ever timed something like that, and if you're selecting or updating 26.000 entries it will take a lot of time no matter what.
But on the other hand, I'm assuming it will be running in a specific selection on the database so it shouldn't take long.

but there always other alternatives:

1.
include a IF myRow.Cells("IsSelected").Value = 1 THEN in my previous code, which I don't think it would speed up that much almost nothing

2.
lock the database
update the table the way it is, then run an:

UPDATE myTable SET IsSelected = 0 WHERE IsSelected = 1

unlock the database. This way the server will be handling the data, not the client.

3. You also can simply completely remove that column from your database, in your application you do like this when querying the DB:

            Me.Table.DataSource = DS.Tables("Main")
            If Not Me.Table.Columns.Contains("IsSelected") Then
                Me.Table.Columns.Insert(0, New DataGridViewCheckBoxColumn)
                Me.Table.Columns(0).Name = "IsSelected"
                Me.Table.Columns(0).ReadOnly = False
            End If

and before updating back to your DB you just

Me.Table.Columns.RemoveAt(0)

what do u think?

 

[philiop]

Thank you for the indepth reply and its the information I am after.

I will just go with your original suggestion and let the terminals do the processing rather than the server as the server is not exactly the best in the world and the management refuse to spend money on upgrading it.

Thanks again for the advice.

 

[Budius]

you're welcome.

although I reckon you should dig option 3. a bit. It might simplify the whole thing.

 

[philiop]

I think I am definately going to have to explore option 3.

I tried using your code from before

For Each myRow As DataGridViewRow In FileDataGridView.Rows
myRow.Cells("IsSelected").Value = 0
Next

which is attached to a button click event. Next to that button is another button that allows you to filter selected by

If iSel = 1 Then
            iSel = 0
Else
            iSel = 1
End If

If iSel = 0 Then
            ToolStripButton9.Text = "Filter Selected"
            SearchFilter()
Else
            ToolStripButton9.Text = "Unfilter Selected"
            Me.FileBindingSource.Filter = "(IsSelected = " & iSel & ")"

            Try
                FileDataGridView.CurrentCell = FileDataGridView.Rows(0).Cells(1)
            Catch
                MsgBox("No Clients Selected, please select atleast one client and try again.")
                ToolStripButton9.Text = "Filter Selected"
                SearchFilter()
                iSel = 0
            End Try
End If

Now when i select a few clients and use the unselect all button without ever doing a filter it works fine. How ever as soon as i do a filter the unselect all button will change everything except the very first selected client. Although when you look at it the tick box is unticked how ever refiltering still shows it as a selected item, if you know what i mean.

Otherwise it actually runs farely decently. Even on an old machine changing the 26,000 records to 0 only takes around 2-3 seconds which for a short term fix is acceptable.

 

[Budius]

I'm not sure if what I'm going to say now is relevant or completely bullcrap, read it at your own risk

I reckon it might have something to do with this line: Me.FileBindingSource.Filter = "(IsSelected = " & iSel & ")"
to be honest I don't even know what object that is.... but those 'bindings' might be tricky.

remember that you have 2 objects: the gridview and a binded source.
Sometimes is a matter of sticking an update() (or similar) somewhere to make sure the data on both objects are really the same.

 

[philiop]

Well I can only give it a go. Maybe if i run an update on the table before and after just to test to see if that is the problem.

 

[Budius]

let me know the result

 

[philiop]

Right just incase i am doing this wrong,

I've tried

me.filedatagridview.update

and i've tried

me.validate
me.FileDatagridview.endedit
me.filetableadapter.update(me.dataset.File)

Unless I am doing this wrong then this is having no effect on the outcome except that after a second attempt to update the program comes up with a failure to update records 0 of 1 then closes.

 

[Budius]

read the 1st line of my previous post:

I'm not sure if what I'm going to say now is relevant or completely bullcrap, read it at your own risk

from now on I can only say: good luck.
hopefully someone else will show up to help.

 

[philiop]

No worries. Was well worth a try. I have no idea why it is doing it. There doesnt seem to be any reason why it would do it.

Thank you for trying and I definately think i will dig into your suggestion of making a selection column at run time which is just removed and added to clear it.