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."
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:or, you can go cursor independent:
If rst.RecordCount = 0 Then 'no records found
If rst.BOF _
And rst.EOF Then 'BOF & EOF indicates an empty recordset
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:
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:
Set rsCount = conn.Execute("SELECT Count(*) FROM pubs")
In conclusion, Happy Programming!