SQLDataReader keeps closing itself !

  • Thread starter Thread starter Cip
  • Start date Start date
C

Cip

Hi,

I have created a small app used to transfer data from MSDE to my own
DB.

I am having some serious problems with SQLDataReader. I use it to
loop through records in order to build my own "INSERT INTO"
statements.

After a random number of records, I get the exception: "Invalid
attempt to read data when reader is closed."

I dont close the reader anywhere!!! It seems like it is closing
itself whenever it doesnt feel like doing anymore work. sometimes
after 40,000 rows, sometimes after 140,000 rows, sometimes even after
10,000 rows.

I wish *I* could close down whenever i didnt feel like working...

any ideas? if the code were to run properly, around 175,000 rows would
be inserted in my destination DB.

Is this a bug?




Here is the code:

Dim srcConn As New SqlClient.SqlConnection
Dim srcCommand As New SqlClient.SqlCommand
Dim srcAdapter As New SqlClient.SqlDataAdapter
Dim srcReader As SqlClient.SqlDataReader
Dim srcStrSql As String

srcConn.ConnectionString = LOCALDB_CONNECTIONSTRING
srcCommand.Connection = srcConn

srcStrSql = "Select ID, col1, col2, col3 from Table where source=0"

srcCommand.CommandText = srcStrSql
srcConn.Open()

srcReader = srcCommand.ExecuteReader

Dim rowCount As Integer = 0
While srcReader.Read()
rowCount = rowCount + 1

strSQL = "INSERT INTO NewTable values(" +
srcReader.getValue(0).ToString 'This is the line that always throws
the exception!! when the exception is thrown, rowCount is always a
different number, anywhere from 10,000 to 140,000

For i As Integer = 1 To srcReader.FieldCount - 1
strSQL += "," + srcReader.GetValue(i).ToString
Next i

strSQL += strSQL + ")"
command.CommandText = strSQL
command.ExecuteNonQuery()
End While
 
Hi,

You are using another connection for command instance, right.
Is it possible that you are experiencing network problems?
 
I would not use ADO.NET to move data from place-to-place until they add BCP
to it (which is the case in v 2.0). Until then, I suggest using DTS or BCP
to move bulk data.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
Miha Markic said:
Hi,

You are using another connection for command instance, right.
Is it possible that you are experiencing network problems?
Click to expand...

Umm... i seem to have found some type of fix to my problem.

I have absolutely no idea why this would work but I set the
CommandTimeout for the SQLCommandObject to 600 (10 minutes), and
everything works fine!

SrcCommand.CommandTimeout = 600

I have confirmed these results several times! A default
CommandTimeout produces an exception at SQLDataReader.read() !

I thought CommandTimeout would only affect SQLReader =
SQLCommand.ExecuteReader

It seems that every SQLReader.read() is re-executing the command or
something like that.

This still makes no sense since each read() takes a subsecond to
perform, i dont see why the default commandtimeout of 30 seconds would
not work.

Is there some kind of 'lifetime' for the SQLDataReader object? does it
just close after a certain amount of time, based on the
CommandTimeout?

I would be very interested to know what is going on.... its not as
urgent as before, since I got everything working, but I would still
like to advance my learning on ADO.NET objects :)
 
Back
Top