1 Replies - 662 Views - Last Post: 15 October 2012 - 06:23 AM Rate Topic: -----

#1 tendaimare  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 170
  • Joined: 04-November 10

Could not save; currently locked by another user. OledbException was u

Posted 15 October 2012 - 05:54 AM

I have a background worker thats doing some database work for me. The reason why I resorted to using using the background worker is because the database work is taking up a lot of time. So the code works the first time but as you continue to update the database then then you get "Could not save; currently locked by another user. OledbException was unhandled". Is the a way to go around this issue. This is my code

Private Sub DoSaveUsersAuditForUpdate_Click()
        Dim myConn As OleDbConnection = frmLogIn.Conn
        SyncLock myConn
            'Insert Previous data before the Update
            'myConn = frmLogIn.Conn
            Dim cmdInsert As OleDbCommand = New OleDbCommand("Insert INTO UsersAudit (Reference,Comment) VALUES (?,?)", myConn)
            cmdInsert.Parameters.Add("@Reference", OleDbType.WChar).Value = txtReference.Text
            cmdInsert.Parameters.Add("@Comment", OleDbType.WChar).Value = "Update Existing Users Details"
            myConn.Open()
            cmdInsert.ExecuteNonQuery()
            myConn.Close()

        End SyncLock

        Dim myKonn As OleDbConnection = frmLogIn.Conn
        SyncLock myKonn
            'Insert Current data after the Update
            'myKonn = frmLogIn.Conn
            Dim cmdInsertOriginal As OleDbCommand = New OleDbCommand("Insert INTO UsersAudit (Reference,Comment) VALUES (?,?)", myKonn)
            cmdInsertOriginal.Parameters.Add("@Reference", OleDbType.WChar).Value = txtReference.Text
            cmdInsertOriginal.Parameters.Add("@Comment", OleDbType.WChar).Value = "New User"
            myKonn.Open()
            cmdInsertOriginal.ExecuteNonQuery()
            myKonn.Close()
        End SyncLock
    End Sub



Here's How I am calling it
Private Sub BackgroundWorker1_DoWork(sender As System.Object, e As System.ComponentModel.DoWorkEventArgs) Handles BackgroundWorker1.DoWork
        DoSaveUsersAuditForUpdate_Click()
    End Sub

this where it all comes together
Private Sub btnUpdate_Click(sender As System.Object, e As System.EventArgs) Handles btnUpdate.Click
  BackgroundWorker1.RunWorkerAsync()

        'Dim workerThread As New Thread(AddressOf DoSaveUsersAuditForUpdate_Click)

        'workerThread.Start()

        DoUpdateUsers_Click(sender, e)

        UsersGridViewGridFormating()

End Sub

 Private Sub UsersGridViewGridFormating()
        Dim myConn As OleDbConnection = frmLogIn.Conn

        SyncLock myConn
            Dim iSql As String = "SELECT *   FROM  Users WHERE UserDeleted =@UserDeleted"

            Dim da As New OleDbDataAdapter(iSql, myConn)
            da.SelectCommand.Parameters.AddWithValue("@UserDeleted", False)
            dTable.Clear()
            da.Fill(dTable)
            myConn.Close()
            Dim dv As DataView = New DataView(dTable)
            dv.Sort = "UserName asc"
            dTable = dv.ToTable

            UsersMasterGridview.DataSource = dTable                                  

            UsersMasterGridview.Visible = True

            UsersMasterGridview.Focus()

            lblNumberofRecords.Text = dTable.Rows.Count & " Records"

        End SyncLock

    End Sub
Any Help this most the code I am using

Is This A Good Question/Topic? 0
  • +

Replies To: Could not save; currently locked by another user. OledbException was u

#2 lucky3  Icon User is offline

  • Friend lucky3 As IHelpable
  • member icon

Reputation: 231
  • View blog
  • Posts: 765
  • Joined: 19-October 11

Re: Could not save; currently locked by another user. OledbException was u

Posted 15 October 2012 - 06:23 AM

Try a little change. Create New connection each time you read or write to your DB. That way you won't have problems with locking it. Here you need to pay attention, if it is possible that 2 or more threads would write to the same datatable row. Perhaps not, but make sure it can't be done. Another thing would be doing it with Using myConn ... End Using, so it will be disposed properly.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1