Basic DataGrid Example

Dim table As New DataTable
Dim adapter As New OleDbDataAdapter("<command text goes here>", "<connection string goes here")
adapter.Fill(table)
Me.DataGrid1.DataSource = table

Bob

That has certainly populated the grid but when I exit the changes are not saved

Save changes:

   adapter.Update(table)

Bob

nope that doesn't seem to work. I put adapter.Update(table) in the click event for my save button and nothing happened.

1)   adapter will need to be declared with scope that covers both the .Fill and the .Update.
2)   Somewhere after .Fill but before .Update you will need

            Dim cb As New OleDbCommandBuilder(adapter)

3)   Make sure that, when you've edited one row in the datagrid, its changes get back to the datatable by clicking on a different row before trying the .Update.

Roger

And I should have added that the commandbuilder will only work properly if the table has a PrimaryKey

Roger

You can try the following....make sure that the DataSet is in overall scope.

================================================

Imports System.Data
Imports System.Data.OleDb


Public Class Form1
    Inherits System.Windows.Forms.Form
    ' create a connection string

    Dim connString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Northwind.mdb"
    Dim myConnection As OleDbConnection = New OleDbConnection
    ' create a new dataset
    Dim ds As DataSet = New DataSet


   ' The form load will populate the datagrid with data from Access table

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        Dim da As New OleDbDataAdapter
        Dim selectCmd As New OleDbCommand

        myConnection.ConnectionString = connString

        ' define the select command
        With selectCmd
            .Connection = myConnection
            .CommandText = "Select * From Customers"
            .CommandType = CommandType.Text
        End With

        ' fill dataset
        With da
            .SelectCommand = selectCmd
            .Fill(ds, "Customers")
        End With

        ' display in the grid
        DataGrid1.DataSource = ds.Tables("Customers")

    End Sub

    ' the button1 click will take the grid information and post back to the database

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

        Dim da As New OleDbDataAdapter
        Dim updCmd As New OleDbCommand

        ' create update command - example here only updates the company name
        With updCmd
            .Connection = myConnection
            .CommandText = "Update Customers Set CompanyName = @CompanyName Where CustomerID = @CustomerID"
            .CommandType = CommandType.Text
            .Parameters.Add(New OleDbParameter("@CompanyName", OleDbType.Char, 40, "CompanyName"))
            .Parameters.Add(New OleDbParameter("@CustomerID", OleDbType.Char, 5, ParameterDirection.Input, False, CType(0, System.Byte), CType(0, System.Byte), "CustomerID", DataRowVersion.Original, Nothing))
        End With

        ' need to now do an insert command and a delete command if you are adding and deleting data

        ' update the database
        With da
            .UpdateCommand = updCmd  ' specifiy what update command to use
            .Update(ds, "Customers")
        End With

        Me.Close

    End Sub

===========================================

HTH :)

Thanks cubixSoftware

That seems to work quite nicely. Can you provide me with the code for adding a new row (by the way my ACcess database uses Autonumber for its primary key field) and then I will avoid you the points.

Sorry meant to say award not avoid ;-)

Absolutely Perfect! Thanks CubixSoftware