473,289 Members | 2,091 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,289 software developers and data experts.

How to update access database rows 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 2015
Dököll
2,364 Expert 2GB
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.
Expand|Select|Wrap|Line Numbers
  1. Private Sub UpdateMark(ByVal lStudentIDToUpdate As Int32, ByVal lNewMarkIDToStore As Int32)
  2.  
  3.         Dim oConnection As OleDbConnection
  4.         Dim oCommand As OleDbCommand
  5.         Dim oRows() As DataRow
  6.         Dim oRow As DataRow
  7.         Dim lTeacherCourseIDToUpdate As Int32
  8.  
  9.         Try
  10.  
  11.             oRows = oStudentMarks.Select("StudentID=" & lStudentIDToUpdate)
  12.             oRow = oRows(0)
  13.             lTeacherCourseIDToUpdate = oRow("TeacherCourseID")
  14.  
  15.             sConnectionString = ConfigurationSettings.AppSettings("ConnectionString")
  16.  
  17.             oConnection = New OleDbConnection(sConnectionString)
  18.             oConnection.Open()
  19.  
  20.             oCommand = New OleDbCommand
  21.             oCommand.Connection = oConnection
  22.             oCommand.CommandType = CommandType.Text
  23.  
  24.             oCommand.CommandText = "UPDATE StudentCourseMap SET MarkID=" & lNewMarkIDToStore.ToString & " WHERE StudentID=" & lStudentIDToUpdate.ToString & " AND TeacherCourseID=" & lTeacherCourseIDToUpdate
  25.             oCommand.ExecuteNonQuery()
  26.  
  27.             bFirstDataLoadComplete = False
  28.             LoadStudentMarks(lCourseID, lTeacherID)
  29.             bFirstDataLoadComplete = True
  30.             lstClassList.SelectedValue = lStudentIDToUpdate
  31.  
  32.         Catch ex As Exception
  33.  
  34.             'MsgBox("Unfortunately Error:[" & ex.Message & "] has occured.  Please close the form and try relaunching", MsgBoxStyle.OKOnly, "Unrecoverable Error")
  35.  
  36.         Finally
  37.  
  38.             If Not (oConnection Is Nothing) Then
  39.                 oConnection.Close()
  40.             End If
  41.  
  42.         End Try
  43.  
  44.     End Sub
  45. Private Sub BindMarksList()
  46.  
  47.     cbxMarks.DataSource = oMarks
  48.     cbxMarks.DisplayMember = "Prompt"
  49.     cbxMarks.ValueMember = "MarkID"
  50.     lMarkID = cbxMarks.SelectedValue
  51.  
  52.   End Sub
  53.  
  54. Private Sub cbxCourses_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cbxCourses.SelectedIndexChanged
  55.  
  56.     If bFirstDataLoadComplete Then
  57.  
  58.       lCourseID = cbxCourses.SelectedValue
  59.       LoadStudentMarks(lCourseID, lTeacherID)
  60.  
  61.     End If
  62.  
  63.   End Sub
  64.  
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
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...
0
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...
11
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...
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,...
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...
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:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
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:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
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...
1
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: 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.