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
1 2016
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
Read through see if this springs any sort of hits: http://www.codeproject.com/vb/net/Wo...taset_Grid.asp Sign in to post your reply or Sign up for a free account.
Similar topics
by: ZeroVisio |
last post by:
Hi,
I want to know if there is an easy way to do update a column of a row in
DataTable.
|
by: Solel Software |
last post by:
Hello,
I have a basic question. I have a DataTable of information without a
database store (it's only in memory). I am looking to somehow query the
DataTable to find out which row(s) satisfy...
|
by: M. David Johnson |
last post by:
I cannot get my OleDbDataAdapter to update my database
table from my local dataset table. The Knowledge Base
doesn't seem to help - see item 10 below.
I have a Microsoft Access 2000 database...
|
by: Siv |
last post by:
Hi,
I seem to be having a problem with a DataAdapter against an Access database.
My app deletes 3 records runs a da.update(dt) where dt is a data.Datatable.
I then proceed to update a list to...
|
by: Zorpiedoman |
last post by:
I keep getting a concurrency exception the second time I make a change and
attempt to update a dataadapter. It appears this is by design, so there must
be something I can do to avoid it.
...
|
by: George |
last post by:
Hi all,
I am having trouble with updating my data in an Access database. here is my
code:
Imports System.Data.OleDb
Dim AppPath As String = Mid(Application.ExecutablePath, 1,...
|
by: tbo |
last post by:
The code below works fine to update my database by adding rows of data to my dataTable.
However when I use the same commands (changing the names of database, datatable and rows) with another...
|
by: =?Utf-8?B?UGV0ZXI=?= |
last post by:
A datatable is returned from a .NET method. Since there is no dataadapter
in this case, how can I update a column for all the rows in that datatable?
|
by: Terry Olsen |
last post by:
I'm using OleDb to connect with an Access Database. I have anywhere from
10 to over 100 records that I need to either INSERT if the PK doesn't
exist or UPDATE if the PK does exist, all in a single...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
by: Shćllîpôpď 09 |
last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
|
by: af34tf |
last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
| |