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()

I would recommend any call for connections be wrapped in "Using" statements, just a suggestion. — Trevor, Aug 29 '14 at 17:29
Would that fix this issue? Would that close the connection thus allowing me to edit that same record? — Derek Napoli, Aug 29 '14 at 17:29
Yes, it would dispose the resources used for those connections and release them. I don't see your code for the update, am I missing something here? — Trevor, Aug 29 '14 at 17:30
Also it would be great if you could show how your connecting... — Trevor, Aug 29 '14 at 17:31
Here's my answer explaining this exactly as well .... http://stackoverflow.com/questions/25036151/populate-column-values-from-ms-access-database-into-vb-net-combo-box-dropdown-v/25038469#25038469 — Trevor, Aug 29 '14 at 17:32
The issue isnt on the update, i suspect its on the initial save, since i can modify any other record in my db. — Derek Napoli, Aug 29 '14 at 17:33
"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" according to that statement you made, it doesnt :) — Trevor, Aug 29 '14 at 17:34
Initial to get the record saved, that code works. My statement remains true. I can only use the update button if i close and reload the application. Thats why i said I think the save button isnt properly closing the connection to allow it to then be available to update — Derek Napoli, Aug 29 '14 at 17:36
Your issue is the connection itself, provide that and maybe we can help... — Trevor, Aug 29 '14 at 17:36
If you can't provide it, look at my answer that I gave you a link too, it shows exactly what you need to do. — Trevor, Aug 29 '14 at 17:37
Exactly, your not disposing these correctly, see THE LINK I POSTED; it's already answered. — Trevor, Aug 29 '14 at 17:38
Also your loop is wrong, you keep looping and changing your variable, if you want it to execute you need to move the connection details inside your loop... — Trevor, Aug 29 '14 at 17:41
Not too familiar with using "using" and "end using" - wish i was. Any chance you could tell me where to begin and how to rewrite my connection code? — Derek Napoli, Aug 29 '14 at 17:50

score 2 · Answer 1 · answered Aug 29 '14 at 17:31

As Mr Codexer said, you would want to use a Using statement to dispose of your connection.

What I expect you actually want from your is to call ds.AcceptChanges() after a successful save. This lets the DataSet and the corresponding tables know that the records are no longer dirty.

vb.net dispose of DB Connection after saving record

1 Answers1