DataReader

[DaveS]

Hi!

I am trying to create a DataReader in .Net 2003, retrieving data from an
Access backend db. In the code below, when I put a breakpoint within
the While...Read() loop, the datareader returns five records (which is
correct). However, when I simply run the program without the
breakpoint, only one record is being returned. Is there something I'm
missing???? The connection state is "Open", so I'm not sure why all
records aren't being returned.



Dim intUpdateEMGsCount As Integer = 0
Dim cnnUpdateEMGs As OleDbConnection = New
OleDbConnection(IMUX_Globals.connBE.ConnectionString)
Dim cmdUpdateEMGs As OleDbCommand = New OleDbCommand("SELECT " & _
"lngEMGId, " &
_

"IIf(IsNull([CountETB]),0, [CountETB]) AS TotalETB, " & _

"IIf(IsNull([CountEMRPS]),0, [CountEMRPS]) AS TotalEMRPS, " & _

"IIf(IsNull([CountRTT]),0, [CountRTT]) AS TotalRTT, " & _

"IIf(IsNull([CountDCH]),0, [CountDCH]) AS TotalDCH " & _
"FROM " & _
"(((tblOrig_EMG
LEFT JOIN qgrpCountETB ON tblOrig_EMG.strEMG = qgrpCountETB.strEMG) LEFT
JOIN qgrpCountDCH ON tblOrig_EMG.strEMG = qgrpCountDCH.strEMG) LEFT JOIN
qgrpCountEMRPS ON tblOrig_EMG.strEMG = qgrpCountEMRPS.strEMG) LEFT JOIN
qgrpCountRTT ON tblOrig_EMG.strEMG = qgrpCountRTT.strEMG", _
cnnUpdateEMGs)

cnnUpdateEMGs.Open()
odrUpdateEMGs =
cmdUpdateEMGs.ExecuteReader(CommandBehavior.CloseConnection)
Console.WriteLine("State: " & cnnUpdateEMGs.State.ToString)
If odrUpdateEMGs.HasRows Then
While odrUpdateEMGs.Read()
intNumRecs += 1
End While
End If
odrUpdateEMGs.Close()
Console.WriteLine("Num Records: " & intNumRecs.ToString)



TIA,

DaveS

 

[Cor]

Hi Dave,

Just a thougth, I was looking to that long SQL script you are using and that
needs knowledge of your database. (Besides that I think nobody here is
intrested to evaluate it, that is more for the ado.net newsgroup).

Maybe you can test your code with a simple Select of one item in one table.

I asume it is not the "Select" that is not working but the logic.

When it does work with that simple select, it is your "Select".

I hope this helps a little bit?

Cor

 

[DaveS]

Cor,

Thanks for your comments.

Just before creating the DataReader (whose Command is based on
tblOrig_EMG), I populated tblOrig_EMG with an INSERT query. My guess is
that this query hadn't completed by the time the DataReader was being
created -- when I moved the code for this DataReader to a different
spot, it worked just fine. Go figure...

TIA,

DaveS