Error on Select method on DataTable

[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.EventArg s) Handles TimerStat.Tick
dim m_da As OleDb.OleDbData Adapter
dim m_cmd As OleDb.OleDbComm and
Dim m_ds As DataSet

m_cmd = New OleDb.OleDbComm and
With m_cmd
.Connection = adoConOLE
.CommandText = "select a,b from stat"
End With
m_da = New OleDb.OleDbData Adapter
m_ds = New DataSet
m_da.Fill(m_ds)
m_clsStat = New clsStat
m_clsStat.CopyS tatTable(m_ds)
If Not (BestSession) Is Nothing Then 'this is my thread
BestSession.ocl sStat = m_clsStat
End If
rs.CloseRS()
rs = Nothing
end sub

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

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

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

[Armin Zingler]

Re: Error on Select method on DataTable

"fniles" <fniles@pfmail. comschrieb

What is a lock statement and how can I use it ?

SyncLock Object
'access object
End SyncLock

Every thread that accesses the object must use Synclock. There are other
possibilities but it's a good start. Multi threading is a broad topic,
so you should read the documentation about synchronizaton etc.

http://msdn.microsoft.com/en-us/library/3e8s7xdd.aspx

http://msdn.microsoft.com/en-us/library/eed6swsx.aspx

Armin

[Rich P]

Re: Error on Select method on DataTable

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.DoE vents

just before the function call.

I would write a function like this:

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

Dim da As New OleDBDataAdapte r, ds As New Dataset
da.SelectComman d = New OleDBCommand
da.SelectComman d.Connection = yourOleDBConn
da.SelectComman d.CommandText = "Select * From tblx Where IDfield = "
& someArg.ToStrin g
da.Fill(ds, tbl1)
Return ds.Tables("tbl1 ").Rows(0)

End Function



Rich

*** Sent via Developersdex http://www.developersdex.com ***

[fniles]

Re: Error on Select method on DataTable

Thank you, I will try that

"Armin Zingler" <az.nospam@free net.dewrote in message
news:uArS8h3tIH A.748@TK2MSFTNG P05.phx.gbl...

"fniles" <fniles@pfmail. comschrieb

>What is a lock statement and how can I use it ?

>
SyncLock Object
'access object
End SyncLock
>
Every thread that accesses the object must use Synclock. There are other
possibilities but it's a good start. Multi threading is a broad topic, so
you should read the documentation about synchronizaton etc.
>

http://msdn.microsoft.com/en-us/library/3e8s7xdd.aspx

http://msdn.microsoft.com/en-us/library/eed6swsx.aspx

>
>
>
Armin

[fniles]

Re: Error on Select method on DataTable

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.

"Rich P" <rpng123@aol.co mwrote in message
news:uNip5B4tIH A.748@TK2MSFTNG P05.phx.gbl...

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.DoE vents
>
just before the function call.
>
I would write a function like this:
>
Private Function xyz(someArg As Integer) As DataRow()
>
Dim da As New OleDBDataAdapte r, ds As New Dataset
da.SelectComman d = New OleDBCommand
da.SelectComman d.Connection = yourOleDBConn
da.SelectComman d.CommandText = "Select * From tblx Where IDfield = "
& someArg.ToStrin g
da.Fill(ds, tbl1)
Return ds.Tables("tbl1 ").Rows(0)
>
End Function
>
>
>
Rich
>
*** Sent via Developersdex http://www.developersdex.com ***

[Cor Ligthert[MVP]]

Re: Error on Select method on DataTable

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.totabl e which creates a new table.

Cor

"fniles" <fniles@pfmail. comschreef in bericht
news:uPp0L02tIH A.576@TK2MSFTNG P05.phx.gbl...

>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.EventArg s) Handles TimerStat.Tick
dim m_da As OleDb.OleDbData Adapter
dim m_cmd As OleDb.OleDbComm and
Dim m_ds As DataSet
>
m_cmd = New OleDb.OleDbComm and
With m_cmd
.Connection = adoConOLE
.CommandText = "select a,b from stat"
End With
m_da = New OleDb.OleDbData Adapter
m_ds = New DataSet
m_da.Fill(m_ds)
m_clsStat = New clsStat
m_clsStat.CopyS tatTable(m_ds)
If Not (BestSession) Is Nothing Then 'this is my thread
BestSession.ocl sStat = m_clsStat
End If
rs.CloseRS()
rs = Nothing
end sub
>
This is the clsStat.vb code:
Dim m_dtStat As DataTable
>
Public Sub CopyStatTable(B yVal m_ds As dataset)
m_dtStat = m_ds.Tables(0). Copy()
end sub
>
Public Function SelectStat(ByVa l Account As String, ByVal sCol As String)
As DataRow()
SelectStat = m_dtStat.Select (sCol & "='" & Trim(Account) & "'") '--error
here
end sub
>
>
>

[fniles]

Re: Error on Select method on DataTable

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 ?



"Cor Ligthert[MVP]" <notmyfirstname @planet.nlwrote in message
news:9A48FD37-79C5-4564-9647-9308CEB429E4@mi crosoft.com...

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.totabl e which creates a new table.
>
Cor
>
"fniles" <fniles@pfmail. comschreef in bericht
news:uPp0L02tIH A.576@TK2MSFTNG P05.phx.gbl...

>>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
>modification s for performance reasons.
>Modification s 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.EventAr gs) Handles TimerStat.Tick
>dim m_da As OleDb.OleDbData Adapter
>dim m_cmd As OleDb.OleDbComm and
>Dim m_ds As DataSet
>>
>m_cmd = New OleDb.OleDbComm and
>With m_cmd
> .Connection = adoConOLE
> .CommandText = "select a,b from stat"
>End With
> m_da = New OleDb.OleDbData Adapter
> m_ds = New DataSet
> m_da.Fill(m_ds)
> m_clsStat = New clsStat
> m_clsStat.CopyS tatTable(m_ds)
> If Not (BestSession) Is Nothing Then 'this is my thread
> BestSession.ocl sStat = m_clsStat
> End If
> rs.CloseRS()
> rs = Nothing
>end sub
>>
>This is the clsStat.vb code:
>Dim m_dtStat As DataTable
>>
>Public Sub CopyStatTable(B yVal m_ds As dataset)
>m_dtStat = m_ds.Tables(0). Copy()
>end sub
>>
>Public Function SelectStat(ByVa l Account As String, ByVal sCol As String)
>As DataRow()
>SelectStat = m_dtStat.Select (sCol & "='" & Trim(Account) & "'") '-->
>error here
>end sub
>>
>>
>>

>