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(ByVa l lStudentIDToUpd ate As Int32, ByVal lNewMarkIDToSto re As Int32)
Dim oConnection As OleDbConnection
Dim oCommand As OleDbCommand
Dim oRows() As DataRow
Dim oRow As DataRow
Dim lTeacherCourseI DToUpdate As Int32
Try
oRows = oStudentMarks.S elect("StudentI D=" & lStudentIDToUpd ate)
oRow = oRows(0)
lTeacherCourseI DToUpdate = oRow("TeacherCo urseID")
sConnectionStri ng = ConfigurationSe ttings.AppSetti ngs("Connection String")
oConnection = New OleDbConnection (sConnectionStr ing)
oConnection.Ope n()
oCommand = New OleDbCommand
oCommand.Connec tion = oConnection
oCommand.Comman dType = CommandType.Tex t
oCommand.Comman dText = "UPDATE StudentCourseMa p SET MarkID=" & lNewMarkIDToSto re.ToString & " WHERE StudentID=" & lStudentIDToUpd ate.ToString & " AND TeacherCourseID =" & lTeacherCourseI DToUpdate
oCommand.Execut eNonQuery()
bFirstDataLoadC omplete = False
LoadStudentMark s(lCourseID, lTeacherID)
bFirstDataLoadC omplete = True
lstClassList.Se lectedValue = lStudentIDToUpd ate
Catch ex As Exception
'MsgBox("Unfort unately Error:[" & ex.Message & "] has occured. Please close the form and try relaunching", MsgBoxStyle.OKO nly, "Unrecovera ble Error")
Finally
If Not (oConnection Is Nothing) Then
oConnection.Clo se()
End If
End Try
End Sub
Private Sub BindMarksList()
cbxMarks.DataSo urce = oMarks
cbxMarks.Displa yMember = "Prompt"
cbxMarks.ValueM ember = "MarkID"
lMarkID = cbxMarks.Select edValue
End Sub
Private Sub cbxCourses_Sele ctedIndexChange d(ByVal sender As System.Object, ByVal e As System.EventArg s) Handles cbxCourses.Sele ctedIndexChange d
If bFirstDataLoadC omplete Then
lCourseID = cbxCourses.Sele ctedValue
LoadStudentMark s(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
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(ByVa l lStudentIDToUpd ate As Int32, ByVal lNewMarkIDToSto re As Int32)
Dim oConnection As OleDbConnection
Dim oCommand As OleDbCommand
Dim oRows() As DataRow
Dim oRow As DataRow
Dim lTeacherCourseI DToUpdate As Int32
Try
oRows = oStudentMarks.S elect("StudentI D=" & lStudentIDToUpd ate)
oRow = oRows(0)
lTeacherCourseI DToUpdate = oRow("TeacherCo urseID")
sConnectionStri ng = ConfigurationSe ttings.AppSetti ngs("Connection String")
oConnection = New OleDbConnection (sConnectionStr ing)
oConnection.Ope n()
oCommand = New OleDbCommand
oCommand.Connec tion = oConnection
oCommand.Comman dType = CommandType.Tex t
oCommand.Comman dText = "UPDATE StudentCourseMa p SET MarkID=" & lNewMarkIDToSto re.ToString & " WHERE StudentID=" & lStudentIDToUpd ate.ToString & " AND TeacherCourseID =" & lTeacherCourseI DToUpdate
oCommand.Execut eNonQuery()
bFirstDataLoadC omplete = False
LoadStudentMark s(lCourseID, lTeacherID)
bFirstDataLoadC omplete = True
lstClassList.Se lectedValue = lStudentIDToUpd ate
Catch ex As Exception
'MsgBox("Unfort unately Error:[" & ex.Message & "] has occured. Please close the form and try relaunching", MsgBoxStyle.OKO nly, "Unrecovera ble Error")
Finally
If Not (oConnection Is Nothing) Then
oConnection.Clo se()
End If
End Try
End Sub
Private Sub BindMarksList()
cbxMarks.DataSo urce = oMarks
cbxMarks.Displa yMember = "Prompt"
cbxMarks.ValueM ember = "MarkID"
lMarkID = cbxMarks.Select edValue
End Sub
Private Sub cbxCourses_Sele ctedIndexChange d(ByVal sender As System.Object, ByVal e As System.EventArg s) Handles cbxCourses.Sele ctedIndexChange d
If bFirstDataLoadC omplete Then
lCourseID = cbxCourses.Sele ctedValue
LoadStudentMark s(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
Comment