dcsimg
LinkedIn
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


advertisement
 

Our Top 10 Tips for Classic ASP : Page 10

Tidy up your server-side programming techniques with these 10 great tricks for classic ASP.


advertisement
Keeping Up to Data with Recordsets

As you start using recordsets within your ASP page, you will frequently run into this problem. You want to access the data within your recordset, but you need to make sure it has data before you access it. Remember that, if the recordset has no data, you will get an ugly, run time error message. You can use the code in this tip to solve your problem.

Question:
I have several years of experience with VB but am brand new to ASP (VB Script). Right now, I'm trying to open an access database, count the number of records and display the information on the Web page. I know that my database contains a table (people) called 'sean.mdb' which has three records. However, when I run the script it says that there are -1 records in the table.

Can you tell me what I'm doing wrong in my code:

<% 

Set objConn = Server.CreateObject("ADODB.Connection")
Set objRst = Server.CreateObject("ADODB.Recordset")

objConn.Open("DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=
" & Server.Mappath("\seannewell\db\sean.mdb"))

strSQL = "SELECT * FROM people"

objRst.Open strSQL, objConn 

Response.write( "<P>" & strSQL & "</P>" )
Response.write("<H2>There are " & objRst.RecordCount & 
" People in the database</H2>")

If objRst.RecordCount > 0 Then
	objRst.MoveFirst
	Do While Not objRst.EOF
		Response.write( "Name = " & objRst.fields(0) )
		objRst.MoveNext
	Loop
else
	Response.write( "It's EMPTY!" )
End If

objRst.Close
Set objRst = Nothing
objConn.Close
Set objConn = Nothing
%>

Answer:
The RecordCount property returns -1 in older versions of MDAC. Try to upgrade the MDAC files on your server to the latest ones, available at www.microsoft.com/data.

If you cannot do that because your Web server is hosted by your ISP and you do not have control over it, change your code.

Instead of using:


   If objRst.RecordCount > 0 Then ...
to check if there are records in your Recordset, use the following:

   If objRst.BOF and objRst.EOF Then
      ' Recordset is Empty
   Else
      Do While not objRst.EOF
         'Process the recordset
         objRst.MoveNext
      Loop
   End If

Update 6/30/00: Daryl Egarr from New Zealand made this observation:

The page implies that the code in question is okay but that "The RecordCount property returns -1 in older versions of MDAC." While this may be true, the author should not make that assumption based on the code in question, as there is nothing in the code that suggests an older version MDAC.

The author missed the whole point, which is that not all properties and/or methods are supported by all cursor types (regardless of database type). The real reason the code failed is that when using the default cursor location(which the code does):


Recordset.CursorLocation = adUseServer
the RecordCount property is only available if the Recordset uses CursorType 1 or 3 (adOpenKeyset or adOpenStatic). The code doesn't specify a CursorType, so therefore 0 (adOpenForwardOnly—the fastest cursortype) is used, and any RecordCount call will return -1.

The Solution is to simply change the line ...


objRst.Open strSQL, objConn 
to ...

objRst.Open strSQL, objConn ,1


Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date