Update Database from DataGridView

[Jonny1409]

Hello,

I wonder if someone can help me please?

I currently have a form which is reading Names from a DB into a DatagridView.
Once a user selects one of the names, another DataGridView is populated with entries belonging to the chosen name. (Each Name can have 0-5 entries)

I then want to make changes to the data in this 2nd DataGridView and update this to the DB.

It all should be straightforward, but it's not working so I'm assuming I'm doing it wrong. (I have done very little with ADO etc so I'm more than likely missing something)

Anyway - my code to populate the 2nd DataGridView is here:

Private Sub PopulateDataGridView(GUID)
        Dim DataAdapter As New SqlDataAdapter("SELECT FIELD_A FROM TABLE_A WHERE GUID = '" & GUID & "'", connectionString)
        Dim DataSet As New DataSet
        Dim commandBuilder As New SqlCommandBuilder(DataAdapter)

        Try
            DataAdapter.Fill(DataSet, "ds")
            dgv1.DataSource = DataSet.Tables("ds")
			
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
End Sub

As you can see, when this sub is called, the person's GUID is passed in and the dataset is filled with the relevant rows.
This works fine and populates the DataGridView fine with 0-5 rows.

Assuming I amend one of the rows in the DataGridView or Add / Remove a row, I would like to just press my 'Update' button which would then write the new entries back to the DB.

This is the part I'm having trouble with.
How would I do this?

Thank you in advance.

[jmcilhinney]

Would it be practical to retrieve all the child data upfront, at the same time as you retrieve the parent data? It would simply things somewhat because you can simply let the data-binding filter the data automatically so you don't have to do anything when the user selects a parent record?

If that's not practical then you can still do the binding once and once only and then simply fill an existing DataTable when the selection changes. It should be just three lines of code: clear the current data, set the parameter value and retrieve the new data.

[Jonny1409]

Hi jmcilhinney, it wouldn't unfortunately because each person's child data is different.

So the table would have something like the following:

Name - Role
Joe - 1
Joe - 2
Joe - 3
Jim - 1
Bob - 1
Paul - 2
Paul - 3
Peter - 0
Peter - 1

This said, how would I go about setting up the binding as per your 2nd paragraph?

[jmcilhinney]

The fact that each parent's child data is different is not a problem and in fact is to be expected. As I said, you can set up the binding so that the filtering occurs automatically. That way, you just bind the child table to the child grid and then its contents is filtered such that you only see those records that are related to the selected parent. If you want to see how that's done, follow the CodeBank link in my signature below and check out my thread on Master/Detail Data-binding.

The issue to consider is how much child data there is in total. If you want to do it the way I have in that CodeBank thread then you have to retrieve all the child data in a single batch upfront. If there's a lot then that will slow the initial loading of the form. If only a small subset of that data will be used then it's also wasteful. In that case, it's worth retrieving only the child data that you need as you need it. Even then though, you should still use a filter on the child data so that you can keep the data you have already retrieved and use it again later if required. Here's an example of what you might do:

Imports System.Data.SqlClient
 
Public Class Form1
 
    Private data As New DataSet
    Private connection As New SqlConnection("connection string here")
    Private parentAdapter As New SqlDataAdapter("SELECT * FROM Parent", connection)
    Private childAdapter As New SqlDataAdapter("SELECT * FROM Child WHERE ParentGuid = @ParentGuid", connection)
    Private parentBuilder As New SqlCommandBuilder(Me.parentAdapter)
    Private childBuilder As New SqlCommandBuilder(Me.childAdapter)
 
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Me.childAdapter.SelectCommand.Parameters.Add("@ParentGuid", SqlDbType.UniqueIdentifier).Value = DBNull.Value
 
        Me.connection.Open()
 
        'Get the parent data.
        Me.parentAdapter.Fill(Me.data, "Parent")
 
        'Build the child schema but this will retrieve no data because the parameter is NULL.
        Me.childAdapter.Fill(Me.data, "Child")
 
        Me.connection.Close()
 
        Me.childBindingSource.DataSource = Me.data.Tables("Child")
        Me.childDataGridView.DataSource = Me.childBindingSource
 
        Me.parentBindingSource.DataSource = Me.data.Tables("Parent")
        Me.parentDataGridView.DataSource = Me.parentBindingSource
    End Sub
 
    Private Sub parentBindingSource_CurrentChanged(sender As Object, e As EventArgs) Handles parentBindingSource.CurrentChanged
        'A new parent row has been selected.
        Dim parentRow = DirectCast(Me.parentBindingSource.Current, DataRowView)
        Dim parentGuid = DirectCast(parentRow("Guid"), Guid)
 
        Dim filter = String.Format("ParentGuid = '{0}'", parentGuid)
        Dim childCount = CInt(Me.data.Tables("Child").Compute("COUNT(ParentGuid)", filter))
 
        If childCount = 0 Then
            'The child data has not yet been retrieved for the current parent.
            Me.childAdapter.SelectCommand.Parameters("@ParentGuid").Value = parentGuid
            Me.childAdapter.Fill(Me.data, "Child")
        End If
 
        'Filter the child data to exclude all but that for the current parent.
        Me.childBindingSource.Filter = filter
    End Sub
 
End Class

That code is untested so it may not be perfect but hopefully it clearly illustrates the principles.