An updateble datagridview in a a Winforms application

To Select/Insert/Delete/Update access database, please refer to the following code snippet.

for Access 2007, please refer to the connection string as below:

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myFolder\myAccess2007file.accdb;Persist Security Info=False;

 

Dim oda As System.Data.OleDb.OleDbDataAdapter = New System.Data.OleDb.OleDbDataAdapter("select * from table1", "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\mydatabase.mdb")
        Dim ds As System.Data.DataSet = New System.Data.DataSet()
        oda.Fill(ds)

        'insert
        Dim dr As System.Data.DataRow = ds.Tables(0).NewRow()
        dr(0) = 10
        oda.Update(ds)

        'delete
        ds.Tables(0).Rows(0).Delete()
        oda.Update(ds)

        'update
        ds.Tables(0).Rows(0)(0) = 10
        oda.Update(ds)

Select allOpen in new window

to bind to datagridview:

Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.jet.oledb.4.0;data source=D:\myDB.mdb")

        Dim cmd As OleDbCommand = New OleDbCommand("Select * FROM User", con)

        con.Open()

        Dim myDA As OleDbDataAdapter = New OleDbDataAdapter(cmd)

        Dim myDataSet As DataSet = New DataSet()

        myDA.Fill(myDataSet, "MyTable")

        DataGridView1.DataSource = myDataSet.Tables("MyTable").DefaultView

        con.Close()

        con = Nothing

Select allOpen in new window

I know how to fill a datagridview with an MS Access query. But when someone changes the datagrid: edits, deletes or adds something, I want these changes to be stored in the table.

Take a look at the sample code here...
http://www.daniweb.com/forums/thread129438.html

This is my code. I am new to Winforms, could you complete the SaveGridToTable?

Public Class Teksten

    Private Sub Teksten_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim strSQL As String
        strSQL = "SELECT Taal, Nummer, Tekst FROM Tekst ORDER BY Nummer, Taal;"
        Dim adp As New OleDb.OleDbDataAdapter(strSQL, GetConnection)
        Dim stPersoon As New DataSet
        adp.Fill(stPersoon, "Teksten")
        Me.gridTeksten.DataSource = stPersoon.Tables("Teksten")
        'me.gridTeksten.Columns("ID").Visible = False
    End Sub

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

    End Sub
End Class

Select allOpen in new window

Ok, this should do it for you...
(Have to make your dataadaptor and dataset global too)

Dim adp As OleDb.OleDbDataAdapter
    Dim stPersoon As DataSet
    Private Sub Teksten_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim strSQL As String
        strSQL = "SELECT Taal, Nummer, Tekst FROM Tekst ORDER BY Nummer, Taal;"
        adp = New OleDb.OleDbDataAdapter(strSQL, GetConnection)
        stPersoon = New DataSet
        adp.Fill(stPersoon, "Teksten")
        Me.gridTeksten.DataSource = stPersoon.Tables("Teksten")
        'me.gridTeksten.Columns("ID").Visible = False 
    End Sub

    Private Sub SaveGridToTable_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim cmdbuilder As New OleDb.OleDbCommandBuilder(adp)
        Dim i As Integer
        Try
            i = adp.Update(stPersoon, "trial1")
            MsgBox("Records Updated= " & i)
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

Select allOpen in new window

My bad, forgot to change a name...

Dim adp As OleDb.OleDbDataAdapter 
    Dim stPersoon As DataSet 
    Private Sub Teksten_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load 
        Dim strSQL As String 
        strSQL = "SELECT Taal, Nummer, Tekst FROM Tekst ORDER BY Nummer, Taal;" 
        adp = New OleDb.OleDbDataAdapter(strSQL, GetConnection) 
        stPersoon = New DataSet 
        adp.Fill(stPersoon, "Teksten") 
        Me.gridTeksten.DataSource = stPersoon.Tables("Teksten") 
        'me.gridTeksten.Columns("ID").Visible = False  
    End Sub 
 
    Private Sub SaveGridToTable_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click 
        Dim cmdbuilder As New OleDb.OleDbCommandBuilder(adp) 
        Dim i As Integer 
        Try 
            i = adp.Update(stPersoon, "Teksten") 
            MsgBox("Records Updated= " & i) 
        Catch ex As Exception 
            MsgBox(ex.Message) 
        End Try 
    End Sub

Select allOpen in new window

The adding of records works fine. But the update and the delete returns the following error:

"Dynamic SQL generation for the DeleteCommand is not supported against a SelectCommand that does not return any key column information."

I have a unique key, an autonumber field, but it is not in the SQL string

I have a primary key! It is ID, it is an autonumber.

I added the ID and everything works fine. Thanks a lot.

Thanks a lot. This solved my problem.