Browse DevX
Sign up for e-mail newsletters from DevX


Our Top 10 Tips for Classic ASP : Page 10

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




Building the Right Environment to Support AI, Machine Learning and Deep Learning

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.

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 %>

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

Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



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