datareader - traversing results from a query

  • Thread starter Thread starter lakshmi
  • Start date Start date
L

lakshmi

Hi all
I'm trying to traverse through the results from a query
that returns more than 1 row. The data reader reads only
the first row. The following code doesn't work. Let me
know what's wrong.
do
{
while(reader.read)
{
x += y;
reader.NextResult();
}
}while reader.NextResult();
I've tried a few different ways. Nothing seems to work.

Finally, after trying a few different things, now it
doesn't seem to read any of the rows. Thanks a lot for
your help.
 
Maybe I misunderstand the goal, but I think you can just use dr.Read and
kill the outer loop.

Looks like the inner loop is pushing the pointer up with the nextResult.

If you just want to increment x, you can get rid of the outer look and kill
the NextResult in the inner loop.

while(reader.Read()){

x+=y;
}

That will traverse the entire reader until it's done.

HTH,

Bill

If I misunderstood what you are doing, let me know and I'll see what I can
do.
 
Thus spake lakshmi:
do
{
while(reader.read)
{
x += y;
reader.NextResult();
}
}while reader.NextResult();
I've tried a few different ways. Nothing seems to work.
Click to expand...

NextResult moves to the next result set, not the next record. The Read
method automatically positions the cursor at the next row so there's no
need to manually move to the next record. Try this:

do
{
while (reader.Read())
{
x+= y; // Where is y coming from, anyway?
}
}
while reader.NextResult();
 
problem1:
thanks Frank and Bill.
sorry for not making my requirement clear.
my sql query is
sql = "SELECT Value from myTable";
cmd = new SqlCommand(sql);
SqlDataReader reader = cmd.ExecuteReader();
double myFinalValue = 0.0;
do
{
while(reader.Read)
{
myFinalValue = myFinalValue + reader.GetDouble(0);
}
}while (reader.NextResult();

my requirement is values from all different rows should be
summed and returned based on a few other business rules.
I'm not able to get the reader move past the first record.

problem2:
Now I have a new problem. I was trying hard to resolve the
above problem and apparently I broke another place.

I'm using datareader to read a 1 row result from a query.

sql = "Select name from myTable where id = 1";
SqlCommand cmd = new SqlCommand(sql);
SqlDataReader reader = cmd.ExecuteReader();
while(reader.Read())
{
if(!reader.IsDBNull(0))
myName = reader.GetString(0);
}
reader.Close();
cmd.Dispose();
This was working fine until I don't know what I broke.
Although this query returns rows from SQL query analyzer,
reader.HasRows property returns 0 and the while loop is
never executed.
Any thoughts? Thanks a lot.
 
Thus spake lakshmi:
my requirement is values from all different rows should be
summed and returned based on a few other business rules.
I'm not able to get the reader move past the first record.
Click to expand...

In that case, save yourself some trouble and use SELECT SUM in your
query. Feed that query to a command object and call ExecuteScalar.
You'll get the total in one shot.
 
thanks Frank. But it is not that simple. I have to
traverse through each row and based on the value returned
I would either add the row value to the sum or not. (and a
few other business rules.)
My problem is how do I get the reader to move to the next
row. I'll try all your suggestions. thanks.
 
Lakshmi:

Your first problem still seems to be the same. Your inner loop gets to the
end of the reader, so there's nothing left. It executes once b/c you have a
Do While loop. I would get out of the out loop totally, and perhaps load an
arraylist with the value of Dr(0). Then you can close the reader and be
done with it. You can walk the arraylist up down and backwards which you
can't do with a reader. Since you are only grabbing one value and summing
it, this is very easy to implement.

For count 1

For i as Integer = 0 to Arraylist.Count
If (al(i) 'Meets business rules Then Increment it

Next

On the second query, if you are just grabbing one value, why not use
ExecuteScalar?
 
Back
Top