Insert, Delete, and Edit Data in DataGridView using single Update Button in Vb.net-CodePudding

I am trying to have a add, delete, and edit function in Vb.net by using one update button and changing the values in the datagridview manually then hitting update. However, I get an error stating "System.InvalidOperationException: 'Update requires a valid UpdateCommand when passed DataRow collection with modified"

Any Ideas? The error comes next to the da.Update(changes)

Also in the above code not shown in my code I have load_data() in the private form1_load sub.

Here is code:

Dim da As New SqlDataAdapter
Dim dt As New DataSet

Private Sub Button1_Click_1(sender As Object, e As EventArgs) Handles Button1.Click
    Dim cmd As New SqlCommandBuilder
    Dim changes As New DataSet
    changes = dt.GetChanges
    If changes IsNot Nothing Then
        da.Update(changes)
        da.Fill(dt)
        DataGridView1.DataSource = dt.Tables(0)
    End If
End Sub

Private Sub load_data()
    Using connection = New SqlConnection("Data Source=.\SQLEXPRESS;Initial Catalog=vbconnectionfinal;Integrated Security=True")

        da = New SqlDataAdapter("Select * From TrueTrack", connection)
        dt.Clear()
        da.Fill(dt)
        DataGridView1.DataSource = dt.Tables(0)

    End Using
End Sub

CodePudding user response：

You are creating a SqlCommandBuilder but not associating it with a SqlDataAdapter, so what's the point? The whole purpose of a command builder is to automatically generate action commands when you call Update on a data adapter. When you create your data adapter, create the command builder immediately after and associate the two, which you would do by passing the data adapter as an argument to the constructor of the command builder.

CodePudding user response：

The following code works for me.

Dim da As New SqlDataAdapter
Dim dt As New DataSet
Dim connection = New SqlConnection("connection string")
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    Dim cmd As New SqlCommandBuilder(da)
    Dim changes As New DataSet
    changes = dt.GetChanges
    If changes IsNot Nothing Then
        da.Update(changes)
        DataGridView1.DataSource = dt.Tables(0)
    End If
End Sub
Private Sub load_data()
    Dim cmd As SqlCommand = New SqlCommand("Select * From TrueTrack", connection)
    da = New SqlDataAdapter(cmd)
    dt.Clear()
    da.Fill(dt)
    DataGridView1.DataSource = dt.Tables(0)
End Sub

Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    load_data()
End Sub

Private Sub Form1_FormClosing(sender As Object, e As FormClosingEventArgs) Handles MyBase.FormClosing
    connection.Close()
End Sub