Error on Select method on DataTable

  • Thread starter Thread starter fniles
  • Start date Start date
F

fniles

I am using VB.NET 2005 and Access database.
My program uses a timer that kicks in every 1 min to read from a database
and copy the dataset table to a datatable.
This database is in a class called clsStat.vb.
Then I pass this class to a thread.
Each thread will call a function inside the clsStat.vb to do a "Select" of
the datatable.
Every day around the same time this function that does the select of the
databale gets errors like so:
There is no row at position 563.
There is no row at position 571.
There is no row at position 601.
Eventually, this function gets the error "DataTable internal index is
corrupted: '5'. "

I read that "The DataTable is not designed to be thread safe for
modifications for performance reasons.
Modifications include selecting rows using Select method on DataTable
because this can modify the datatable by creating a new index on it.
The article says to "resolve this you need to use the lock statement around
all modifications to DataTable."
What is a lock statement and how can I use it ?

What causes the errors "There is no row at position 601." and "DataTable
internal index is corrupted: '5'. " ,
and how to fix it ? Thank you


The timer code in the main program:
Private Sub TimerStat_Tick(ByVal sender As Object, ByVal e As
System.EventArgs) Handles TimerStat.Tick
dim m_da As OleDb.OleDbDataAdapter
dim m_cmd As OleDb.OleDbCommand
Dim m_ds As DataSet

m_cmd = New OleDb.OleDbCommand
With m_cmd
.Connection = adoConOLE
.CommandText = "select a,b from stat"
End With
m_da = New OleDb.OleDbDataAdapter
m_ds = New DataSet
m_da.Fill(m_ds)
m_clsStat = New clsStat
m_clsStat.CopyStatTable(m_ds)
If Not (BestSession) Is Nothing Then 'this is my thread
BestSession.oclsStat = m_clsStat
End If
rs.CloseRS()
rs = Nothing
end sub

This is the clsStat.vb code:
Dim m_dtStat As DataTable

Public Sub CopyStatTable(ByVal m_ds As dataset)
m_dtStat = m_ds.Tables(0).Copy()
end sub

Public Function SelectStat(ByVal Account As String, ByVal sCol As String) As
DataRow()
SelectStat = m_dtStat.Select(sCol & "='" & Trim(Account) & "'") '--> error
here
end sub
 
Greetings,

I don't know your project, but if you are using MS Access as part of the
project - it is unlikely that you need to perform multi-threading
operations. On your timer just call the function and place

Application.DoEvents

just before the function call.

I would write a function like this:

Private Function xyz(someArg As Integer) As DataRow()

Dim da As New OleDBDataAdapter, ds As New Dataset
da.SelectCommand = New OleDBCommand
da.SelectCommand.Connection = yourOleDBConn
da.SelectCommand.CommandText = "Select * From tblx Where IDfield = "
& someArg.ToString
da.Fill(ds, tbl1)
Return ds.Tables("tbl1").Rows(0)

End Function



Rich
 
Thanks
The reason I do multi threading is because there are many request/messages
coming to my program, and I would like to process each request in its own
thread.
 
fniles,

I don't see this in your code, but this occurs as you delete rows from a
datatable or whatever collection using a for loop.

You have to do this using the ZA methode as AZ once showed me. It is in my
idea almost impossible that it is your select.

\\\
For i = dt.rows.count-1 to 0 step -1
-- remove
Next
///

Is this somewhere in a thread, multitrhreading stops mostly on the places
you don't expect it, therefore simple run it without threads before.

You know by the way that the dataview has after version 2003 a
dataview.totable which creates a new table.

Cor
 
Thank you.
In my program I do not delete anything from the datatable, I do not even do
any update to the database.
Other programs do update the database and deletes from the the query "stat"
that I read from.
If other programs deletes from the query "stat", will it effect my program ?
 
Back
Top