datareader - traversing results from a query

[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.

[William Ryan]

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.
"lakshmi" <[e-mail]> wrote in message
news:c8****************************@phx.gbl...

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.

[Frank Oquendo]

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.

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();

--
There are 10 kinds of people. Those who understand binary and those who
don't.

http://code.acadx.com

[lakshmi]

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.

-----Original Message-----
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.

.

[Frank Oquendo]

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.

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.

--
There are 10 kinds of people. Those who understand binary and those who
don't.

http://code.acadx.com

[lakshmi]

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.

-----Original Message-----
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.
In that case, save yourself some trouble and use SELECT SUM in yourquery. Feed that query to a command object and call ExecuteScalar.You'll get the total in one shot.

--
There are 10 kinds of people. Those who understand binary and those whodon't.

http://code.acadx.com
.

[William Ryan]

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?
"lakshmi" <[e-mail]> wrote in message
news:c9****************************@phx.gbl...

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.

-----Original Message-----
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.

.