Looping process with datareader

[Winanjaya]

Dear The Expert,
I don't understand .. why my looping process below (with level break condition) does not working ..I meant the "mTotalFreq" always has incorrect value.. it seems like the looping process has skipped one or some records, am I missing something?

DataReader = Cmd.ExecuteReader
While DataReader.Read
mStat = DataReader("Stat")
mHostCode = DataReader("HostCode")
While DataReader.Read = True And DataReader"HostCode") = mHostCode
mTotalFreq = mTotalFreq + DataReader("TotalCost")
End While
End While

DataReader.Close()
SQLTemp.Close()

I need advise .. many thanks in advance

Regards
Winanjaya

[mendhak]

Originally posted by Winanjaya
Dear The Expert,
I don't understand .. why my looping process below (with level break condition) does not working ..I meant the "mTotalFreq" always has incorrect value.. it seems like the looping process has skipped one or some records, am I missing something?

DataReader = Cmd.ExecuteReader
While DataReader.Read
mStat = DataReader("Stat")
mHostCode = DataReader("HostCode")
While DataReader.Read = True And DataReader"HostCode") = mHostCode
mTotalFreq = mTotalFreq + DataReader("TotalCost")
End While
End While

DataReader.Close()
SQLTemp.Close()

I need advise .. many thanks in advance

Regards
Winanjaya

Yes you're missing something. Everytime you do the DataReader.Read, it moves to the next record. So you are getting an incorrect value.

[CyberHawke]

In other words, using nested Reads of the same reader results in more than one record getting used during a process.

[nemaroller]

And use .HasRows to check if there are any rows in the datareader to be read...

[Winanjaya]

Could any body please give me a simple code example about this? .. sorry I am very new in Vb.NET?

thanks a lot in advance

[nemaroller]

Dim dr As System.Data.SqlClient.SqlDataReader
dr = SqlCommand1.ExecuteReader(CommandBehavior.CloseConnection)
 
If dr.HasRows Then
 While dr.Read
      'this code will run once for each record returned
      If dr("HostCode") Then mTotalFreq = mTotalFreq + dr("TotalCost")
      'alternative statement if you know which column values are
      'this runs faster
      If dr(1) Then mTotalFreq = mTotalFreq + dr(2)
      
 End While
End If
 
dr.Close()

[Winanjaya]

Thanks, but how to group it?

Dim dr As System.Data.SqlClient.SqlDataReader
dr = SqlCommand1.ExecuteReader(CommandBehavior.CloseConnection)

If dr.HasRows Then
While dr.Read
mhostcode=dr("hostcode")
mtotalfreq=0
while dr.read and mhostcode=dr("hostcode")
mtotalfreq=mtotalfreq+dr("totalcost")
end while
' I STILL GET THE INCORRECT MTOTALFREQ here ..
SOME RECORDS SKIPPED HERE !
End While
End If

dr.Close()

[nemaroller]

Explain exactly what your goal is, in layman terms...

mhostcode=dr("hostcode")
mtotalfreq=0
'this following line does not make sense
'you cannot get mhostcode from the
'datareader unless you do a dr.read
'to begin with
while dr.read and mhostcode=dr("hostcode")

[Winanjaya]

I want to get totalfreq of each hostcode

while dr.read
mhostcode=dr("hostcode") ' Store hostcode to variable
mtotalfreq=0 ' Set 0 to mtotalfreq


while dr.read and mhostcode=dr("hostcode")
' sum the totalcost to mtotalfreq
mtotalfreq=mtotalfreq+dr("totalcost")
end while

msgbox mtotalfreq

end while

any idea?

[nemaroller]

You cannot use a While dr.Read and mhostcode=...

Otherwise, the reader will never advance to the next record.

Additionally, you can only have one datareader on an open connection at one time.

What format is the record coming as? By that I mean what columns are in the row (record)?

[Winanjaya]

So what should I use? is there another way for doing this? I meant by do that looping process I will get the value of mtotalfreq of each hostcode.. I need advise ...thanks

[brucevde]

What about changing the query of the DataReader. Makes your loop much simpler and the database creates the totals.

Select HostCode, Sum(TotalCost)
From
Group By HostCode
 
While dr.Read
'do something
End While

[nemaroller]

You need to state how the data is being presented to you.

For instance, if a SQLPROC named 'GetHostElements' looks like this:

Proc GetHostElements

Select mHostCode, mtotalfreq From HostElements

Then the returning data will have two columns, mHostCode, mTotalFreq.

Our code to read that would be:

While dr.Read()
   somevariable = dr(0)
   someothervariable = dr(1)
End While

Now obviously, if we have more than 1 row being returned from the stored procedure... then we need to put those values somewhere other than assigning them to the same variables (somevariable and someothervariable). Because on each loop, those two variables will get reassigned the contents of the datareader at that particular record.

So you either need an array or a datatable to store these values. If you use a datatable:

we adjust our code to this:

Dim dt As New DataTable
dt.Columns.Add(New DataColumn("mHostCode", GetType(String)))
dt.Columns.Add(New DataColumn("mTotalFreq", GetType(Integer)))
 
...do your sql connection and command decleration...
 
dr = SqlCommand1.ExecuteReader(CommandBehavior.CloseConnection)
Dim datar As DataRow
While dr.Read
  datar = dt.NewRow
  datar(0) = dr(0)
  datar(1) = dr(1)
  dt.Rows.Add(datar)
End While
dr.Close
 
'we now have a table reflecting all the records returned from our proc

Now I used this as an example, because I don't have any idea what your database is supposed to return, because you didn't state that.