vb.net dispose of DB Connection after saving record

Question

I have a front end view of a backend Access DB. I have a Save and an Update button. Whenever i use the below Save Code, it works and adds the record to my database and displays it correctly in datagridview, if i hit the update button on that newly created record it seems like the record is still in "EDIT" mode in access thus giving me a concurrency error when i try to make an update to the record. Do i have to dispose of the connection after i save below, if so can you show me how to dispose of that connection or close the record in the access db? The only fix that has this working is to add application.restart() to the end of my save button, WHICH i really do not want to do.

Private Sub btnSave_Click(sender As Object, e As EventArgs) Handles btnSave.Click
        If tbAEU.Text = "" Or cbIA.Text = "" Or cbCI.Text = "" Or tbS.Text = "" Or tbD.Text = "" Then
            MessageBox.Show("Please do not leave any field blank", "Validation")
            Return
        End If
        cbAdded.Text = "No"
        If intInc <> -1 Then

            Dim cb As New OleDb.OleDbCommandBuilder(da)
            Dim dsNewRow As DataRow

            dsNewRow = ds.Tables("SDMDB").NewRow()

            dsNewRow.Item("AffectedEndUser") = tbAEU.Text
            dsNewRow.Item("IncidentArea") = cbIA.Text
            dsNewRow.Item("ConfigItem") = cbCI.Text
            dsNewRow.Item("Summary") = tbS.Text
            dsNewRow.Item("Description") = tbD.Text
            dsNewRow.Item("ActivityLog") = tbAL.Text
            dsNewRow.Item("AddedtoSDM") = cbAdded.Text

            ds.Tables("SDMDB").Rows.Add(dsNewRow)

            da.Update(ds, "SDMDB")

            MessageBox.Show("New Record added to the Database", "Save")

            btnSave.Enabled = False
            btnAdd.Enabled = True
            btnDelete.Enabled = False
            btnPWReset.Enabled = True
            btnUnlock.Enabled = True
            btnEdit.Enabled = True
            btnCancel.Enabled = False
            cbIA.Enabled = False
            cbCI.Enabled = False
            SetAllAsReadOnly()
            UpdateDatabase()
            Me.Refresh()
            moveforward()
            movebackward()
        End If
    End Sub

Connection Code:

strDbProvider = "PROVIDER=Microsoft.Jet.OLEDB.4.0;"
            Dim sDrive As String, sDrives() As String
            sDrives = ListAllDrives()
            For Each sDrive In sDrives
                Dim strTemp As String = sDrive & "\AppData\SDMDB\SDMDB.mdb"
                If File.Exists(strTemp) Then
                    strDbSource = "Data Source = " & strTemp
                End If
            Next
            con.ConnectionString = strDbProvider & strDbSource

            con.Open()

            strSQL = "SELECT * FROM SDM_Details"
            da = New OleDb.OleDbDataAdapter(strSQL, con)
            da.Fill(ds, "SDMDB")

            con.Close()

vb.net dispose of DB Connection after saving record

Answers (1)

Related Questions