Issues with access database locking in VB windows app

[tazdiver]

Hi All,

I am having an issue with an windows application made from VB.NET 2003 which is using an access database.
Basically the problem is that when the user updates a field on the app from a drop down, it tries to update the access db. This causes the db to lock itself and not allow anyother updates.

What I would like to do is be able to update just the in memory datatable for each field then use an update button to update everything at once, hopefully getting around access's locking issues.

This is a sample of the code that is being used sofar.

Private Sub UpdateMark(ByVal lStudentIDToUpdate As Int32, ByVal lNewMarkIDToStore As Int32)

Dim oConnection As OleDbConnection
Dim oCommand As OleDbCommand
Dim oRows() As DataRow
Dim oRow As DataRow
Dim lTeacherCourseIDToUpdate As Int32

Try

oRows = oStudentMarks.Select("StudentID=" & lStudentIDToUpdate)
oRow = oRows(0)
lTeacherCourseIDToUpdate = oRow("TeacherCourseID")

sConnectionString = ConfigurationSettings.AppSettings("ConnectionStrin g")

oConnection = New OleDbConnection(sConnectionString)
oConnection.Open()

oCommand = New OleDbCommand
oCommand.Connection = oConnection
oCommand.CommandType = CommandType.Text

oCommand.CommandText = "UPDATE StudentCourseMap SET MarkID=" & lNewMarkIDToStore.ToString & " WHERE StudentID=" & lStudentIDToUpdate.ToString & " AND TeacherCourseID=" & lTeacherCourseIDToUpdate
oCommand.ExecuteNonQuery()

bFirstDataLoadComplete = False
LoadStudentMarks(lCourseID, lTeacherID)
bFirstDataLoadComplete = True
lstClassList.SelectedValue = lStudentIDToUpdate

Catch ex As Exception

'MsgBox("Unfortunately Error:[" & ex.Message & "] has occured. Please close the form and try relaunching", MsgBoxStyle.OKOnly, "Unrecoverable Error")

Finally

If Not (oConnection Is Nothing) Then
oConnection.Close()
End If

End Try

End Sub
Private Sub BindMarksList()

cbxMarks.DataSource = oMarks
cbxMarks.DisplayMember = "Prompt"
cbxMarks.ValueMember = "MarkID"
lMarkID = cbxMarks.SelectedValue

End Sub

Private Sub cbxCourses_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cbxCourses.SelectedIndexChanged

If bFirstDataLoadComplete Then

lCourseID = cbxCourses.SelectedValue
LoadStudentMarks(lCourseID, lTeacherID)

End If

End Sub

The user has to update 3 fields per student and can have up to 25 students.

Tks for any and all help.

Taz

[Dököll]

Hi All,

I am having an issue with an windows application made from VB.NET 2003 which is using an access database.
Basically the problem is that when the user updates a field on the app from a drop down, it tries to update the access db. This causes the db to lock itself and not allow anyother updates.

What I would like to do is be able to update just the in memory datatable for each field then use an update button to update everything at once, hopefully getting around access's locking issues.

This is a sample of the code that is being used sofar.

Private Sub UpdateMark(ByVal lStudentIDToUpdate As Int32, ByVal lNewMarkIDToStore As Int32)
 
        Dim oConnection As OleDbConnection
        Dim oCommand As OleDbCommand
        Dim oRows() As DataRow
        Dim oRow As DataRow
        Dim lTeacherCourseIDToUpdate As Int32
 
        Try
 
            oRows = oStudentMarks.Select("StudentID=" & lStudentIDToUpdate)
            oRow = oRows(0)
            lTeacherCourseIDToUpdate = oRow("TeacherCourseID")
 
            sConnectionString = ConfigurationSettings.AppSettings("ConnectionString")
 
            oConnection = New OleDbConnection(sConnectionString)
            oConnection.Open()
 
            oCommand = New OleDbCommand
            oCommand.Connection = oConnection
            oCommand.CommandType = CommandType.Text
 
            oCommand.CommandText = "UPDATE StudentCourseMap SET MarkID=" & lNewMarkIDToStore.ToString & " WHERE StudentID=" & lStudentIDToUpdate.ToString & " AND TeacherCourseID=" & lTeacherCourseIDToUpdate
            oCommand.ExecuteNonQuery()
 
            bFirstDataLoadComplete = False
            LoadStudentMarks(lCourseID, lTeacherID)
            bFirstDataLoadComplete = True
            lstClassList.SelectedValue = lStudentIDToUpdate
 
        Catch ex As Exception
 
            'MsgBox("Unfortunately Error:[" & ex.Message & "] has occured.  Please close the form and try relaunching", MsgBoxStyle.OKOnly, "Unrecoverable Error")
 
        Finally
 
            If Not (oConnection Is Nothing) Then
                oConnection.Close()
            End If
 
        End Try
 
    End Sub
Private Sub BindMarksList()
 
    cbxMarks.DataSource = oMarks
    cbxMarks.DisplayMember = "Prompt"
    cbxMarks.ValueMember = "MarkID"
    lMarkID = cbxMarks.SelectedValue
 
  End Sub
 
Private Sub cbxCourses_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cbxCourses.SelectedIndexChanged
 
    If bFirstDataLoadComplete Then
 
      lCourseID = cbxCourses.SelectedValue
      LoadStudentMarks(lCourseID, lTeacherID)
 
    End If
 
  End Sub
 
 

The user has to update 3 fields per student and can have up to 25 students.

Tks for any and all help.

Taz

A desperate attempt to gain you some hits, tazdiver :-)