Question Update database using DGV

[dfenton21]

Hi,

I'm currently learning VB.Net, and I have an database application with Access 97 as its backend. Unfortunately, I'm currently restricted to using Access 97 and can't upgrade. So far, when updating records, all the values have come from text boxes or comboboxes, and that has worked fine.

I now need to populate a DGV on a form. When the user clicks a command button on the form, the database will update with the new information the user as changed using the DGV. I've used jmcilhinney's extraordinarily helpful post on CodeBank regarding this subject.

I have taken his multiple records example and made the following changes only:
- Replaced all SQL code with Oledb code
- Removed all code relating to DELETE and INSERT because I'm only concerned with UPDATE
- Changed the SQL expressions and connection string to suit my DB

To test, I made a sample application and a sample database with one table called "Names". Names has two fields, "ID" (primary key), FirstName.

The GetData routine works fine, and populates the DGV with the table.
However, when I call the SaveData I get the following exception:
"Update requires a valid UpdateCommand when passed DataRow collection with modified rows."

I've spend hours tried to find a a way to resolve this as I try to post questions as a last resort. Sadly, I've not been successful, so I'd very much appreciate any assistance because I feel I'm missing something very obvious

My code is below.

Thanks very much for your help.

Imports System.Data.OleDb

Public Class Form1


     Private connection As New  OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data  Source=C:\Documents and Settings\Dfenton\Desktop\TestDB.mdb;User  Id=admin;Password=;")

    Private adapter As New OleDbDataAdapter("SELECT * FROM Names", connection)

    Private table As New DataTable


    Private Sub InitialiseDataAdapter()


        Dim update As New OleDbCommand("UPDATE Names SET FirstName = @Name WHERE ID = @ID", Me.connection)


        update.Parameters.Add("@Name", SqlDbType.VarChar, 100, "FirstName")

        update.Parameters.Add("@ID", SqlDbType.Int, 4,  "ID")


        Me.adapter.UpdateCommand = update

        Me.adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey

    End Sub

    Private Sub GetData()

        'Retrieve the data.

        Me.adapter.Fill(Me.table)

        DataGridView1.DataSource = table

    End Sub

    Private Sub SaveData()

        Me.adapter.Update(Me.table)

    End  Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        GetData()

    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click

        SaveData()


    End Sub
End Class

 

[jmcilhinney]

The UpdateCommand of the DataAdapter is created in the InitialiseDataAdapter method but you never call that method, so that command is never created. You would most likely call that method in the Load event handler of the form.

 

[dfenton21]

Ahh, the solution is so obvious.

Thanks jmcihinney. I really appreciate your help