none
MS Access Database Doesn't Update from VB.NET RRS feed

  • Question

  • All,

     

    I have a program that loads a table into a datagridview so the user can edit it.  It works great except the changes do not get saved to the MS Access database.  It doesn't throw an error, it just doesn't save the changes.

     

    Here I set up the dataset and bind the control:

     

    dgvBoard is the data gridviewcontrol

    daBoard and dsBoard are the DataAdapter / DataSet

     

    Code Snippet

    connBoard.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _ dbStrBoard

    connBoard.Open()

    Dim cmdSelBoard As New OleDb.OleDbCommand("SELECT * FROM tblBoard;", connBoard)

    daBoard.SelectCommand = cmdSelBoard

    dsBoard = New DataSet

    daBoard.Fill(dsBoard, "tblBoard")

     

    dgvBoard.EditMode = DataGridViewEditMode.EditOnEnter

    dgvBoard.AutoGenerateColumns = True

    dgvBoard.DataSource = dsBoard

    dgvBoard.DataMember = "tblBoard"

    dgvBoard.Columns("ID").Visible = False

     

     

    I want to save all changes in the datagridview to the database when a button is pressed:

     

    Code Snippet

    Private Sub cmdSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdSave.Click

    dgvBoard.EndEdit()

    dsBoard.AcceptChanges()

    daBoard.Update(dsBoard, "tblBoard")

    End Sub

     

     

    If I trap at the end, the updated data looks like it is in the dataSet, but it doesn't save in the Access Database.

    My Access database security settings are Admin and User have all access, and there is no password on the database.

     

    Any help you can give me will be appreciated!

     

    Thanks,

    Max

     

     

     

    Tuesday, July 29, 2008 8:50 PM

Answers

  • No, you actually need to generate approproate UPDATE/INSERT/DELETE commands, so when DataAdapter executes Update method it calls appropriate SQL command depending on status of each row. You could try to generate those commands using CommandBuilder class, or you could create them explicitely from your code. Here is link with explanation about how it works

     

    http://www.radsoftware.com.au/articles/usingcommandbuilder.aspx

     

    Thursday, July 31, 2008 9:47 AM
    Moderator

All replies

  • You need to call AcceptChanges method AFTER you call Update. This is because AcceptChanges method resets all the states of rows inside of DataTable to unchanged and when you call Update after that, DataAdapter will not have any actions against database. Your Save function should be like

     

     

    Private Sub cmdSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdSave.Click

    dgvBoard.EndEdit()

    daBoard.Update(dsBoard, "tblBoard")

    dsBoard.AcceptChanges()

    End Sub

    Wednesday, July 30, 2008 9:52 AM
    Moderator
  • VMazur,

     

    Thanks for the reply.

     

    When I do it that way I get an error on the daBoard.Update line:

     

    "Update requires a valid UpdateCommand when passed DataRow collection with modified rows."

     

    Do I need to manually run some kind of Update command?  I thought all I had to do was databind the DataGridView control and run the update command.

     

    Thanks,

    Max

    Wednesday, July 30, 2008 5:46 PM
  • No, you actually need to generate approproate UPDATE/INSERT/DELETE commands, so when DataAdapter executes Update method it calls appropriate SQL command depending on status of each row. You could try to generate those commands using CommandBuilder class, or you could create them explicitely from your code. Here is link with explanation about how it works

     

    http://www.radsoftware.com.au/articles/usingcommandbuilder.aspx

     

    Thursday, July 31, 2008 9:47 AM
    Moderator
  • VMazur,

     

    Thanks for the help.  All I had to do was add a command.  I didn't even have to set the command to do anything.  Your earlier suggestion about putting AcceptChanges after the update was also nessesary. 

     

    This is the final code that worked:

     

    Code Snippet

    Private Sub cmdSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdSave.Click

    dgvBoard.EndEdit()

    Dim cb As New OleDb.OleDbCommandBuilder(daBoard)

    daBoard.Update(dsBoard, "tblBoard")

    dsBoard.AcceptChanges()

    End Sub

     

     

     

    Thanks again for all your help.

     

    Regards,

    Mac

    Thursday, July 31, 2008 10:33 PM