How to update/modify an existing row in an Access Database using Visual Basic

Question

I am having difficulty updating an existing row in an Access Database using VB. I want to be able to make changes to fields in an already populated existing row in my access table.

The code I have relates to adding a new record at the bottom of the table rather than the above.

Public Class Form1
    Dim objConnection As New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; Data Source = StudentDatabase.accdb")
    Dim objStudentDA As New OleDb.OleDbDataAdapter("Select * FROM Student", objConnection)
    Dim objStudentCB As New OleDb.OleDbCommandBuilder(objStudentDA)
    Dim objDs As New DataSet()

 Private Sub btnUpdate_Click(sender As Object, e As EventArgs) Handles btnUpdate.Click

        If txtStudentNum.Text <> "" And txtSurname.Text <> "" And txtAttendance.Text <> "" And txtCA1.Text <> "" And txtCA2.Text <> "" And txtFinalExamResult.Text <> "" Then


            Dim objRow3 = objDs.Tables("Student").Rows.Find(txtUpdateStudentID.Text.ToString)

            'Editing each field value based on textboxes

            objRow3.Item("FName") = txtUpdateFName.Text
            objRow3.Item("SName") = txtUpdateSName.Text
            objRow3.Item("Attendance") = txtUpdateAttendance.Text
            objRow3.Item("CA1") = txtUpdateCA1.Text
            objRow3.Item("CA2") = txtUpdateCA2.Text
            objRow3.Item("FinalExam") = txtUpdateFinalExam.Text
            objRow3.Item("OverallResult") = txtUpdateOverallGrade.Text


**'ERROR HERE STATING THIS ROW ALREADY BELONGS TO A TABLE**

            objDs.Tables("Student").Rows.Add(objRow3)
            objStudentDA.Update(objDs, "Student")
            MsgBox("Record has been added to the IS2215 Database!")
            Retrieve()
        Else
            MsgBox("Error: You must not leave any fields blank!")
        End If
    End Sub

Public Sub Retrieve()

        objDs.Clear()
        objStudentDA.FillSchema(objDs, SchemaType.Source, "Student")
        objStudentDA.Fill(objDs, "Student")
        cmbStudentFind.Items.Clear()
        Dim i As Integer, strCurrentID As String
        For i = 1 To objDs.Tables("Student").Rows.Count
            strCurrentID = objDs.Tables("Student").Rows(i - 1).Item("ID")
            cmbStudentFind.Items.Add(strCurrentID)
            cmbUpdateStudentID.Items.Add(strCurrentID)
        Next
        cmbStudentFind.SelectedIndex = 0
        cmbUpdateStudentID.SelectedIndex = 0

        FillUpdateDetails()


    End Sub

Public Sub FillUpdateDetails()
        Dim objRow2 As DataRow
        objRow2 = objDs.Tables("Student").Rows.Find(cmbUpdateStudentID.SelectedItem.ToString)
        txtUpdateStudentID.Text = objRow2.Item("ID")
        txtUpdateFName.Text = objRow2.Item("FName")
        txtUpdateSName.Text = objRow2.Item("SName")
        txtUpdateAttendance.Text = objRow2.Item("Attendance")
        txtUpdateCA1.Text = objRow2.Item("CA1")
        txtUpdateCA2.Text = objRow2.Item("CA2")
        txtUpdateFinalExam.Text = objRow2.Item("FinalExam")
        txtUpdateOverallGrade.Text = objRow2.Item("OverallResult")


    End Sub

End Class

You need to operate on the row outside of the Table...get a copy, modify it, and then update the Table. Had a similar issue with a DataTable going against Sybase and MS-SQL — GoldBishop
– GoldBishop, Commented Apr 17, 2018 at 12:03
Also, Access tends to Table Lock when data is being read from it...therefore, I would suggest you connect when you need data and disconnect when you need to operate on that data. It is a PITA but will alleviate a lot of problems with Access CRUD operations. — GoldBishop
– GoldBishop, Commented Apr 17, 2018 at 12:14

GoldBishop · Accepted Answer · 2018-04-17 12:21:01Z

0

You need to execute CRUD against your Database. You will have a lot of problems trying to perform those operations against Access, as it tends to lock Tables when a Table is being accessed.

I would suggest you build a CRUD framework to "machine gun" the Insert, Update, and/or Delete operations into Access as you need.

Outside of that, you will loose a lot of hair in the process of trying to make it work.

answered Apr 17, 2018 at 12:21

GoldBishop

2,8794 gold badges54 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ASH · Accepted Answer · 2018-05-01 02:23:38Z

For making inserts, you can stick with your current TextBox setup. For making updates, I would think a DataGridView would be a better way to go.

Something like this should work for you.

Imports System.Data.OleDb
Public Class Form1
    Dim connetionString As String
    Dim connection As OleDbConnection
    Dim oledbAdapter As OleDbDataAdapter
    Dim oledbCmdBuilder As OleDbCommandBuilder
    Dim ds As New DataSet
    Dim changes As DataSet
    Dim i As Integer
    Dim sql As String

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        connetionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Your mdb filename;"
        connection = New OleDbConnection(connetionString)
        Sql = "select * from tblUsers"
        Try
            connection.Open()
            oledbAdapter = New OleDbDataAdapter(Sql, connection)
            oledbAdapter.Fill(ds)
            DataGridView1.Data Source= ds.Tables(0)
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Try
            oledbCmdBuilder = New OleDbCommandBuilder(oledbAdapter)
            changes = ds.GetChanges()
            If changes IsNot Nothing Then
                oledbAdapter.Update(ds.Tables(0))
            End If
            ds.AcceptChanges()
            MsgBox("Save changes")
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Sub
End Class

Or, you can stick with the TextBox concept, and change your setup slightly, as such.

str = "Journals SET JournalTitle=?, JournalText=? WHERE JournalDate=?"
cmd = New OleDbCommand(str, myConnection) 
cmd.Parameters.AddWithValue("@jounalTitle", MyJournalTitle )
cmd.Parameters.AddWithValue("@journalText", MyJournalText)
cmd.Parameters.AddWithValue("@journalDate", DatePicked)
cmd.ExecuteNonQuery()

That methodology is much safer than the way you are doing it now. There are more details here.

How to update MS Access Database (vb.net)

Collectives™ on Stack Overflow

How to update/modify an existing row in an Access Database using Visual Basic

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related