rs.ResultSet and rs.NextRow

[Tbrondolo]

I have been using the following syntax for looping through a result set

rs = cn.ResultSet

while  (rs.NextRow())

do something

end while

I then had a result set with a single row of data and found that rs.NextRow() would be False and the loop would not fire.

So, I went to the following.....

dim flag1 as l = .t.

while flag1

do something

flag1 = rs.NextRow()
	
end while

While this works it will always look to process the first row. Also, it is not how the function is shown in the help tables or in other posts that i have seen.

Which is the correct way to do this?

Thanks
Tom

[agillbb]

Re: rs.ResultSet and rs.NextRow

The "normal" way (to me, at least) is....

dim flagResult = rs.nextRow()
while flagResult
'do something
flagResult = rs.nextRow()
end while

[Tbrondolo]

Re: rs.ResultSet and rs.NextRow

Under that approach if you have a single row of data would you expect flagResult to be T or F?

If it's checking the next row it would be False.

[agillbb]

Re: rs.ResultSet and rs.NextRow

The rs.nextRow() tries to retrieve the next row of data and place it in the rs.data array.
If it succeeds, then it returns .T. but if there is no 'next row' of data, then returns .F.
So the very first (initial) call to rs.nextRow() will return .T. if there is only one record, and the second one within the while loop will then return .f.

[DaveF]

Re: rs.ResultSet and rs.NextRow

I am having the same problem. I am using SQL Server 2008 and have a table with 2 rows. I am using the following code:

result = cn.execute("select * from WorkQueue")

rs = cn.ResultSet

while rs.NextRow()

    id = rs.data("id")

end while

It only returns 1 record. I have also tried the following variation:

rs = cn.ResultSet

flag = rs.NextRow()

while flag = .t.

    flag = rs.NextRow()

end while

I still only get 1 row. I then changed the query to see what would happen:

result = cn.execute("select top 1 * from WorkQueue")

rs = cn.ResultSet

flag = rs.NextRow()

while flag = .t.

    flag = rs.NextRow()

end while

flag comes back as .F. after the first call to rs.NextRow() and I get zero rows. If I use the same loop above and change the select statement to "select top 2 * from WorkQueue" then I get 1 record.

Any ideas?


Edit: I made a typo in my last 2 examples which I corrected.

I closed Alpha Five and came back in and now it's working. Has anyone had this issue before?

[glenschild]

Re: rs.ResultSet and rs.NextRow

Try using this for the loop

dim FlagResult as L = rs.NextRow()
while FlagResult


FlagResult = rs.nextRow()
end while

[DaveF]

Re: rs.ResultSet and rs.NextRow

Thanks for catching that Glen. I corrected my last 2 examples. I mistakenly pasted the wrong line of code.

Out of desperation I thought I would try closing A5 and starting it back up. Now I am getting the 2 rows as I should have been getting all along using my original code:

rs = cn.ResultSet

while rs.NextRow()

id = rs.data("id")

end while

[workaholic06]

Re: rs.ResultSet and rs.NextRow

If you were debugging this, and you had the 'rs' as a watch field, the debugger automatically does a get NextRow... and you miss the first record..

Were you in debug mode by chance?

Thanks,

Earl Allin
REA Inc.

[DaveF]

Re: rs.ResultSet and rs.NextRow

You're a genius! That was it. I can reproduce it every time by adding rs as a watch field. When I remove it, the problem goes away. I actually encountered this problem about 6 months ago and it mysteriously went away. I'm glad I now know why it's doing this.

Thanks

[workaholic06]

Re: rs.ResultSet and rs.NextRow

Glad I was able to help out.

I've learned to put a breakpoint after the nextrow, or the debug(1) after the nextrow()

Earl Allin
REA Inc.

[Tbrondolo]

Re: rs.ResultSet and rs.NextRow

Hi Dave,
I've been traveling across country and tried to do a post a few days ago from my driod while on a plane but it looks like it did not work. Glad to see you got the Debug() answer. I know I spent many hours trying to figure out what was going on.

tom

[DaveF]

Re: rs.ResultSet and rs.NextRow

Thanks Tom.

[son04littleelm]

Re: rs.ResultSet and rs.NextRow

hi everyone.
does anyone know what a5 syntax is for a character variable to be continue on the second or third line ??
ex:

dim xmsg as c

xmsg = "this is just an example to make my point" +
"here is the second portion of the string" +
"and here is the last string portion of this variable"

thank you in advance for your help...
Oct 18th 2013

[Tbrondolo]

Re: rs.ResultSet and rs.NextRow

If you mean just wrapping text to a variable then something like this will work

sql= <<%a%
SELECT cycleid, weekbegdate, weekenddate
FROM weeklytimeperiods
WHERE cycleid =:a_cycleid
%a%


If you want to inject line breaks then "+crlf()" will give you a line feed.

If it is HTML then "<br>" will give you a break


Tom

[son04littleelm]

Re: rs.ResultSet and rs.NextRow

hi tbrondolo,
I am not referring to html .. I am referring to xbasic coding in the function..
how would someone assign a very long text string to a variable without keeping
on the same line..

ex: would be a sql statement that pulls out over ten fields out of twenty available fields..
with Where clause..
thanks ..
Oct 18th, 2013