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

DataReader

P: n/a
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.ConnectionStri ng)
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.CloseC onnection)
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

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 20 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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
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.ConnectionStri ng)
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.CloseC onnection)
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)

Nov 20 '05 #2

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

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 20 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.