• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 551
  • Last Modified:

An updateble datagridview in a a Winforms application

I want to make a datagridview on a form in a winforms application. This datagridview has to be linked to a MS Access table, and it has to be updatable. So I need the operations Add, Edit and Delete.
0
Frans_Truyens
Asked:
Frans_Truyens
  • 6
  • 4
  • 2
1 Solution
 
Meir RivkinFull stack Software EngineerCommented:
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)

Open in new window

0
 
Meir RivkinFull stack Software EngineerCommented:
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

Open in new window

0
 
Frans_TruyensAuthor Commented:
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.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
me655321Commented:
Take a look at the sample code here...
http://www.daniweb.com/forums/thread129438.html
0
 
Frans_TruyensAuthor Commented:
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

Open in new window

0
 
me655321Commented:
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

Open in new window

0
 
me655321Commented:
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

Open in new window

0
 
Frans_TruyensAuthor Commented:
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
0
 
me655321Commented:
You must use a primary key for it to work.
0
 
Frans_TruyensAuthor Commented:
I have a primary key! It is ID, it is an autonumber.
0
 
Frans_TruyensAuthor Commented:
I added the ID and everything works fine. Thanks a lot.
0
 
Frans_TruyensAuthor Commented:
Thanks a lot. This solved my problem.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 6
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now