Need help on cell in datagrid

[loulou296]

Hi - I have a datagrid bound to a sql query and the first field is a checkbox field 8 is a amount. As they check the box I want to keep a running tally on field outside the grid to tell me how much I've checked. Unfortunately, it doesn't save the checkbox until you move off that field so the value is not -1 until then. How can I read the value before it is saved so that I can get a proper tally while the focus is still on that field. I've tried these events - cellbeginedit, cellendedit, cellvaluechanged, cellchanged. Again with message boxes I have learned the value of that cell doesn't change until the focus moves to another field. Perhaps I am checking the wrong value? Here is an example of the code I used in each of those events. Any insight would be appreciated.

        Dim NumberOfRows As Integer
        Dim x As Integer
        Dim Reversed As Double

        Reversed = 0

        NumberOfRows = ClaimsDataViewGrid.RowCount - 1
        x = 0
        While x <> NumberOfRows

            MessageBox.Show(CStr(x) & "-" & ClaimsDataViewGrid.Rows(x).Cells(0).Value)


            If ClaimsDataViewGrid.Rows(x).Cells(0).Value = -1 Then
                Reversed = Reversed + CDbl(ClaimsDataViewGrid.Rows(x).Cells(8).Value)

                MessageBox.Show("ee-" & ClaimsDataViewGrid.Rows(x).Cells(8).Value)
            End If

            x = x + 1
        End While
        MessageBox.Show("ff" & Reversed)
        'Me.TotalTextBox.value = str(Reversed)
        Me.TotalTextBox.Text = Reversed
        Me.TotalTextBox.Refresh()

[Shaggy Hiker]

Welcome to the forums. I edited your post to add [CODE][/CODE] tags. You can do this by pressing the # button and pasting code between the tags.

As to the question, in a way, you have already answered it. After all, you have determined that the value doesn't get updated early enough for it to be useful to you, so you need to take that into account and realize that the value simply won't be of much use.

Therefore, I would suggest that you look into the Click events of the cell. This is a bit risky. I don't have any experience with this case, so I don't know whether it is possible to click on a cell that has a checkbox without also checking the checkbox. If it is possible to click without checking, then the click event alone will not suffice. Another issue to the click is that a click could be checking or unchecking. So, if the click is sufficient, then you'd have to look at the current value and assume that the click switched the state from whatever it is to the other state. So, for any click, you'd be assuming that the new value became the opposite of what it currently is.

Of course, this can also fail, because a click on a cell followed by a click on the same cell would return it back to the original state, and that would get tricky. Still, checking into what you can do in the Click event might offer some kind of solution.

The Validating event is also one to consider, but that's another one that most likely won't be raised in time.

The general issue is that for anything other than a checkbox, the cell is working just the way you need it to. You sure don't want to be writing out values for every keystroke if the cell held a number. For example, if the cell had to have a valid year, and a person started typing 2018, you wouldn't want it to write the 2 once that had been typed, because it would fail validation. You wouldn't want to write the 20, or the 201, either. You'd only want to write once the user had finished typing, because that's the only time you'd want to validate, and you can only tell the user is finished when they leave the cell. So, it works as it should for anything other than a checkbox, because a checkbox is the only case where the click is all the entry that is possible. That makes for a tough problem for you. The click might work for you, but quite likely it would not, because any solution would have to work for one click, two clicks, three clicks, and so on.

[loulou296]

Thank you for your response.

Could I perhaps make it lose focus and then bring the focus back to force a save?

[Shaggy Hiker]

I've never tried that, but give it a shot. Set the focus to a button, then back again. I doubt it will work if you do that in two lines like:

someButton.SetFocus
ThatCell.SetFocus

I would guess that each of those would only queue up events, but they wouldn't get handled, and focus would be right back where it started. It's worth a try, cause it's easy, I'm just not optimistic.

Beyond that, what you might try is to add a control of some sort that is not on the screen. For example, you might add a button, but set the Top property to -200. You could then just set focus to that button, and in the GotFocus event for that button, you could try setting it back to the cell.

[kpmc]

here is a hint

    Private Sub DataGridView1_CurrentCellDirtyStateChanged(sender As Object, e As EventArgs) Handles DataGridView1.CurrentCellDirtyStateChanged
        DataGridView1.CommitEdit(DataGridViewDataErrorContexts.Commit)
    End Sub

[Shaggy Hiker]

That's a good hint, but it has the same caveats as the Click event that I mentioned before: Multiple clicks on the same cell may well cause you issues, because every even number of clicks will return the state back to what it was in the underlying data source.

[kpmc]

That's a good hint, but it has the same caveats as the Click event that I mentioned before: Multiple clicks on the same cell may well cause you issues, because every even number of clicks will return the state back to what it was in the underlying data source.

IMO the Datatable should contain the boolean, the dt should be a datasource of a bindingsource and the bindingsource should be the object being iterated for the value making the number of click irrelevant

        Dim Sum As Double = 0
        For Each DRV As DataRowView In BS
            If Convert.ToBoolean(DRV("Selectcolumn")) Then
                Sum += Convert.ToDouble(DRV("ValueToSum"))
            End If
        Next

This can be done in a like manor using the rows collection in the DGV

        For Each DGVRow As DataGridViewRow In DataGridView1.Rows
            If Convert.ToBoolean(DGVRow.Cells("BoolCol").Value) Then 'conversion maynot be nesseccary
                Sum += Convert.ToDouble(DGVRow.Cells("ValToSum").Value)
            End If
        Next

[loulou296]

Thank you. The Currentcelldirtystatechanged event worked - I just used my own code on the inside. Thank you both.

[kpmc]

Thank you. The Currentcelldirtystatechanged event worked - I just used my own code on the inside. Thank you both.

Can you show me the entire procedure. If you put your routine in that event I am not sure if we can still be friends /sarcasm

[loulou296]

Private Sub ClaimsDataViewGrid_CurrentCellDirtyStateChanged(sender As Object, e As EventArgs) Handles ClaimsDataViewGrid.CurrentCellDirtyStateChanged
        ClaimsDataViewGrid.CommitEdit(DataGridViewDataErrorContexts.Commit)

        Dim NumberOfRows As Integer
        Dim x As Integer
        Dim Reversed As Double

        Reversed = 0


        NumberOfRows = ClaimsDataViewGrid.RowCount
        x = 0
        While x <> NumberOfRows

            'MessageBox.Show(CStr(x) & "-" & ClaimsDataViewGrid.Rows(x).Cells(0).Value)


            If ClaimsDataViewGrid.Rows(x).Cells(0).Value = -1 Then
                Reversed += CDbl(ClaimsDataViewGrid.Rows(x).Cells(8).Value)

                'MessageBox.Show("zz-" & ClaimsDataViewGrid.Rows(x).Cells(8).Value)
            End If

            x = x + 1
        End While
    
        Me.TotalTextBox.Text = Reversed
        Me.TotalTextBox.Refresh()


    End Sub

I have worked in ms-access vba for 25 years and sql and many other languages for 32 years. I can always update and fix the code but this is my first jump into starting from scratch and creating windows forms and vb.net stuff. Much is very similar but some makes you do way more work than you should have to or maybe that's my inexperience.

[kpmc]

You are most definitely working to hard.
First when you're declaring your vars you can initialize them at the same time

        Dim NumberOfRows As Integer = DataGridView1.RowCount
        Dim x As Integer = 0
        Dim Reversed As Double = 0

But in your case you only need the one:

        Dim Reversed As Double = 0

Now remove everything from that Sub but this line:

ClaimsDataViewGrid.CommitEdit(DataGridViewDataErrorContexts.Commit)

Then start fresh with this routine on the CellCOntentClick Event

    Private Sub DataGridView1_CellContentClick(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView1.CellContentClick
        Dim Reversed As Double =0
        For Each DGVRow As DataGridViewRow In DataGridView1.Rows
            If Convert.ToBoolean(DGVRow.Cells("YourCheckBoxColumnName").Value) Then
                Reversed += Convert.ToDouble(DGVRow.Cells("YourCheckBoxColumnName").Value)
            End If
        Next
        TotalTextBox.Text = Reversed
    End Sub

Also Line #1 (Before your Imports) should read like this:

Option Strict On

[kpmc]

And if you want to prevent the sub from running when you click any other cell value you can use a condtion like this:

    Private Sub DataGridView1_CellContentClick(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView1.CellContentClick
        Dim Reversed As Double =0

        For Each DGVRow As DataGridViewRow In DataGridView1.Rows
            If DataGridView1.CurrentCell.OwningColumn.Name = "YourCheckBoxColumnName" Then

                If Convert.ToBoolean(DGVRow.Cells("YourCheckBoxColumnName").Value) Then
                    Reversed += Convert.ToDouble(DGVRow.Cells("YourCheckBoxColumnName").Value)
                End If

            End If
        Next

    End Sub