When you think ASP, think...
Recent Articles
All Articles
ASP.NET Articles
Message Board
Related Web Technologies
User Tips!
Coding Tips

Sample Chapters
Commonly Asked Message Board Questions
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Stump the SQL Guru!
XML Info
Author an Article
ASP ASP.NET ASP FAQs Message Board Feedback
Print this page.
Published: Wednesday, September 23, 1998

Determining if a Recordset is Empty

In Active Server Pages, once you run a query, you often want to be able to tell if a record set is empty or not. If the recordset is empty, you want to display a message of sorts informing the user that the query returned no data, and if the recordset is not empty you want to post the data. There are multiple ways to determine if a recordset is empty or not. Let us examine a posting to the Active Server Pages mailing list.

What is the code I need to display a message if no records are returned from a query? For instance, if a user submits something and nothing in the database matches, right now my code just displays an empty line. What can I do to make it say "No records matching that request exist."

- continued -

The answer given to this question is correct, but doesn't fully explain itself:

Depending upon the cursor type you are using, you can say:

If rst.RecordCount = 0 Then 'no records found
      ...do something...
End If

or, you can go cursor independent:

If rst.BOF _
   And rst.EOF Then 'BOF & EOF indicates an empty recordset
      ...do something...
End If

The confusion may come to an inexperienced ADO / ASP programmer, who, like most new ASP / ADO programmers, opens up a recordset as follows:

      Dim rs, conn
      Set conn = Server.CreateObject("ADODB.Connection")
      conn.Open ... Connection String ...

      Set rs = conn.Execute("SELECT * FROM authors")

This is perfectally legal syntax, which implicitly creates an instantiation of a recordset object and assigns it to the object variable rs. What the novice programmer may not realize is that when you implicitly define recordset objects, the default cursor-type is forward-only. This is fine for a large percentage of the time. However, if you wish to employ the RecordCount property, your recordset must allow backwards row fetching.

In an attempt to clarify the mess I've undoubtedly made, let me turn to a brief example. Say that a coder used the snippet above, and then tried the line below:
      Response.Write rs.RecordCount

you will get -1 returned. You may think that you have 0 records, but that is not necessarily the case. It simply means that ADO could not determine the true number of records in your recordset. This is because the RecordCount property counts the records in a recordset by literally iterating through the entire recordset, counting all the records, then returning to the current postition. So, if your cursor type is not explicitly stated to be either dynamic or static, this property won't return correct values.

You can always use the .EOF property, as the answer to the question above shows. Just remember that to obtain a count of the true number of records in your recordset, you must use a cursor which allows backward row fetching, or do something clever, like:
      Dim rsCount
      Set rsCount = conn.Execute("SELECT Count(*) FROM pubs")
      Response.Write rsCount(0)

In conclusion, Happy Programming!

ASP.NET [1.x] [2.0] | ASPFAQs.com | Advertise | Feedback | Author an Article