Question Changing a DGV's contents via code

[Oddism]

I am using two forms, one form contains a DGV, the other contains some textboxes.

I take each textbox.text from the second form and store it in an array, I then insert each string in this array into a different column in the DGV (the row inserted into is static) on the first form.

This all works fine and the DGV alters the fields which I changed and displays updated information. The problem occurs when it comes to saving these changes back to my Access Database (using a CommandBuilder). When I save the changes, the changes I made are completely ignored and upon repoulating the DGV from scratch it displays the old information. However, if after applying the array to the DGV and I enter any of the cells, then save, then repopulate, everything is exactly as it should be.

I've tried a lot of things to fix this, all of which have been unsuccessful so far, hence, I am posting!

Thanks in advance

 

[jmcilhinney]

Is the grid bound? Show us all and only the relevant code.

 

[Oddism]

I'm using a DataAdapter to fill my Dataset with the information stoored in my Database.
I'm using a BindingSource to connect the DataSet to my DataGridView, code examples as follows:

Public Class formEdit
    Dim ds As New DataSet
    Dim da As OleDb.OleDbDataAdapter
    Dim bs As New BindingSource
    Dim dbPath As String = "C:\...\...\...\Database.accdb"
    Dim constring As String = "PROVIDER=Microsoft.ACE.OLEDB.12.0;Data Source =" & dbPath
    Dim con As New OleDb.OleDbConnection

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim sql As String = "SELECT * FROM tblInfo"

        con.ConnectionString = constring

        Try
            ' Open Connection
            con.Open()

            da = New OleDb.OleDbDataAdapter(sql, con)

            ' Fill Dataset
            da.Fill(ds, "Associates Candidate Network")

            With bs
                .DataSource = ds
                .DataMember = "Associates Candidate Network"
            End With

            ' Fill Datagrid
            dgInfo.DataSource = bs

        Catch ex As Exception
            MessageBox.Show(ex.Message)
        Finally
            con.Close()
        End Try
    End Sub
End Class

This is all the code relevant to how the DGV is displaying information - this then saves back to the Database using the da.Update() method.

Thanks

 

[cjard]

Dont edit data via the DGV, edit the datatable that holds the data directly

No:
myDGV.Cells(0,0).Value = "hello"

Yes:
myDataTable.Rows(0)(0) = "hello"

Note that if your dgv is sorted, 0,0 in its cells wont correspond to 0,0 in the table. In that case, use the bindingsource instead, as that performs the sorting on behalf of the DGV. The DGV doesnt contain data, it only views the data elsewhere and edits it from a UI perspective. Don't attempt to use its data editing function programmatically

 

[Oddism]

Well, I'm using the DataGridView.CurrentRow.Index to get the index of the row which I'm altering and as you said, if I apply the change to the DataTable, if the DGV's contents have been sorted (ascending/descending fields) during run-time, then this number won't correspond to the correct row index of my DataTable. This is the reason why I'm editing the DGV's contents opposed to my DataTable's contents.

So, I guess the question I'm asking is how can I get the correct row index of my DataTable if the contents of the DGV have been sorted or, is there a way for the changes made to the DGV's contents to be saved without having to enter a cell first?

Thanks for the responses so far and I apologise for my lack of understanding!

 

[jmcilhinney]

You should be binding your DataTable to a BindingSource and then binding that to the grid. You then get the current record from the Current property of the BindingSource. You also use the BindingSource for filtering and sorting in code. The Current property returns a DataRowView, which you can use as is in most cases. If you really need the underlying DataRow then you can get that from the Row property of the DataRowView.

 

[Oddism]

That seems to have done it!

As per usual, thanks jmcilhinney - I honestly do try to keep my questions to a minimum!
I really don't want to get in a habit of relying on you for help, but I know I'll recieve the information on what I need to research instead of just being fed with code which is always good..

Also, thanks for your input cjard

 

[cjard]

In my forms where I have: (bound countrols) -> BindingSource ->MyDataTable
I usually make a property:

ReadOnly Property CurrentXYZRow As MyDataTableRow
  Get
    If MyBindingSource.CUrrent Is Nothing Then Return Nothing
    Return DirectCast(DirectCast(MyBindingSource.Current, DataRowView).Row, MyDataTableRow)
  End Get
End Property

This gives me an easy way to use the typed datatable:

If CurrentXYZRow.Name = "John" Then ...

 

[cjard]

is there a way for the changes made to the DGV's contents to be saved without having to enter a cell first?

If there is a need to perform some operation on a grid that the user may have edited in UI, but the changes haven't been committed yet, you should call dgv.EndEdit()
e.g. you asked the user to add a new row and then click OK to save.. part of your save routine should endedit on the grid and the bindingsource before committed the Added row (in the datatable) to db. you should not programmatically enter a cell, edit it and endedit the grid.. let all editing of a grid be from the user using the ui only

 

[Oddism]

In my forms where I have: (bound countrols) -> BindingSource ->MyDataTable
I usually make a property:

ReadOnly Property CurrentXYZRow As MyDataTableRow
  Get
    If MyBindingSource.CUrrent Is Nothing Then Return Nothing
    Return DirectCast(DirectCast(MyBindingSource.Current, DataRowView).Row, MyDataTableRow)
  End Get
End Property

This gives me an easy way to use the typed datatable:

If CurrentXYZRow.Name = "John" Then ...

This is a great, I'm going to implement something similar into my code now!

Also, regarding the EndEdit() method, that's what I was originally looking for when I made this thread. However, as you suggested, I'm no longer editing the DGV cells directly.

Thanks for your help