Domino Code Fragment

Code Name*
Determine the Number of Rows in a LS:DO Result Set in Notes R4		Date*
04/28/2024		Source (or email address if you prefer)*
Rlatulippe@romac.com
IP address:.3.19.30.232
Description*
Fetch all the rows using the LastRow method and then use the NumRows property to return the
number of rows in the result set.		Type*
LotusScript		Categories*
(Misc)
Implementation:
Required Client:
Server:
Limitations:
Comments:
Files/Graphics attachments (if applicable): Code:
Problem:
The Notes Release 4 Help database states the following for the NumRows property:

"You cannot determine the number of rows immediately after a query or while fetching records, (the
number of rows can be determined) only after the entire result set is fetched."

How then can you determine the number of rows in the result set?

Solution:
Fetch all the rows using the LastRow method and then use the NumRows property to return the
number of rows in the result set.

Example:

Dim con As New ODBCConnection
Dim qry As New ODBCQuery
Dim res As New ODBCResultSet
If Con.ConnectTo("salary")Then
Set Qry.Connection = Con
Qry.SQL = "select * from Salary"
Set Res.Query = Qry
Res.Execute
Res.LastRow
Rows = Res.NumRows
Messagebox Rows
Res.Close(DB_Close)
con.DisConnect
End If

Note that if you have set MaxRows property for the result set, the LastRow method moves you
to the last row of the result set as (potentially) limited by the number you have set for
MaxRows. For example, you may have a Select statement that would return 100 rows but have a
result set of only 50 rows because MaxRows has been set to 50. LastRow moves you to the last
row of this result set, row 50.

In this case, however, NumRows will not return 50, but will instead return a negative number
(-2), equal to the constant, DB_ROWSLIMITED. If Result.NumRows = DB_ROWSLIMITED, this
indicates that there would be more rows if MaxRows was not limiting the result set, and that
the number of rows in the result is equal to the number of rows specified by the MaxRows
property.

Example:

Dim con As New ODBCConnection
Dim qry As New ODBCQuery
Dim res As New ODBCResultSet
If Con.ConnectTo("salary")Then
Set Qry.Connection = Con
Qry.SQL = "select * from Salary"
Set Res.Query = Qry
Res.MaxRows = 50
Res.Execute
Res.LastRow
Rows = Res.NumRows
If Rows = DB_ROWSLIMITED Then
Messagebox "The number of rows in the result set is 50, the max that has been set with the
MaxRows property."
Else
Messagebox "The number of rows in the result set is" & Str(rows)
End If
Res.Close(DB_Close)
con.DisConnect
End If