SqlDataReader advancing on each line of executable code within a loop

  • Thread starter Thread starter Tim Reinier
  • Start date Start date
T

Tim Reinier

I have the following code (this is only a small snippet of the code)
compiled into a VB.Net exe file. The SqlDataReader (myReader) is
populated via an SqlCommand.ExecuteReader() method call where the
CommandType is a stored procedure on SQLServer2000.

1: myReader = myCommand.ExecuteReader()
2: Do While myReader.Read()
3: Select Case myReader.GetInt32(0)
4: Case 1
5: ' do one thing
6: Case 2
7: ' do something else
8: Case 3
9: ' do yet another thing
10: End Select
11: ' more processing of the first record
Loop

According to the SqlDataReader documentation it is supposed to advance
through the records on each call of the Read method and it is supposed
to be positioned just prior to the first record right after the
ExecuteReader method fills it.

The issue I am experincing is that the reader is positioned on the
first record after the ExecuteReader method (line 1). Consequently,
after the Raed method is called on line 2, the value of the Select
Case expression is now that of the second record. Also, after each
Case evaluation (lines 4,6, and 8) the reader is advancing. Therefore,
by the time it loops back to the Read method (Line 2) it is impossible
to determine where the reader will be positioned.

Line 1 executes: reader positioned on record 1
Line 2 executes: reader positioned on record 2
Line 3 executes: reader positioned on record 3
Line 4 executes: reader positioned on record 4
Line 6 executes: reader positioned on record 5
Line 8 executes: reader positioned on record 6
Line 9 executes: reader positioned on record 7
Line 11 through N executes: the reader advances for each executable
line of code.

Given that this is an exe file, there is no web.config file, so I'm
wondering if there is some other location that a particular setting
may be causing this behavior. I know I could put the initial data from
the stored procedure int a DataSet and then iterate through the Rows
collection, but I'd rather keep it simple with the Reader.Read
methodology. Any help or insight into the cause of the observed
behavior will be appreciated.

Thanks,
Tim
 
Hi Tim,

How do you know where reader is positioned?
I guess you are getting wrong info on record postion.
 
I know where the reader is positioned because I am using VS.NET as the
development environmment and I am stepping through the code in Debug
mode. Therefore, in the Watch window I am able to monitor the values of
my variables.
 
Tim van der Baan said:
I know where the reader is positioned because I am using VS.NET as the
development environmment and I am stepping through the code in Debug
mode. Therefore, in the Watch window I am able to monitor the values of
my variables.
Click to expand...

That all sounds very odd. Do you have a short but complete program
which demonstrates the problem? Does it happen outside the debugger?
 
Back
Top