3
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
