By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,851 Members | 983 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,851 IT Pros & Developers. It's quick & easy.

Error on Select method on DataTable

P: n/a
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

Jun 27 '08 #1
Share this Question
Share on Google+
6 Replies


P: n/a
"fniles" <fn****@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

Jun 27 '08 #2

P: n/a
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

*** Sent via Developersdex http://www.developersdex.com ***
Jun 27 '08 #3

P: n/a
Thank you, I will try that

"Armin Zingler" <az*******@freenet.dewrote in message
news:uA*************@TK2MSFTNGP05.phx.gbl...
"fniles" <fn****@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

Jun 27 '08 #4

P: n/a
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" <rp*****@aol.comwrote in message
news:uN*************@TK2MSFTNGP05.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.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

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

Jun 27 '08 #5

P: n/a
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

"fniles" <fn****@pfmail.comschreef in bericht
news:uP*************@TK2MSFTNGP05.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.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
Jun 27 '08 #6

P: n/a
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]" <no************@planet.nlwrote in message
news:9A**********************************@microsof t.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.totable which creates a new table.

Cor

"fniles" <fn****@pfmail.comschreef in bericht
news:uP*************@TK2MSFTNGP05.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.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

Jun 27 '08 #7

This discussion thread is closed

Replies have been disabled for this discussion.