473,288 Members | 1,710 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,288 software developers and data experts.

How to update access database from datatable memory

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
May 22 '07 #1
1 1614
Dököll
2,364 Expert 2GB
The user has to update 3 fields per student and can have up to 25 students.

Tks for any and all help.

Taz
Closely related, to:

http://www.thescripts.com/forum/thread650712.html
Jun 2 '07 #2

Sign in to post your reply or Sign up for a free account.

Similar topics

8
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.
3
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...
8
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. ...
4
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,...
2
by: Scotty | last post by:
I get stuck to write an update, insert and delete command, i am looking for some help to start Whats the best way to update 2 tables toe the database (Access) below my code used to load my...
3
by: John Cosmas | last post by:
I have a DATATABLE which I have populated in my application, and I need it written out to a particular table I specify in my ACCESS database. My code works to the point of the MERGE and UPDATE,...
1
by: tazdiver | last post by:
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...
4
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?
13
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...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
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...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
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...
0
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 using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.