ASP VBScript ADO Recordset RecordCount

**CiberTHuG** · Apr 22nd, 2002, 11:47 AM

Given this code...

Code:

<% @Language="VBScript" %>
<%
  Option Explicit

%>
<!---#include virtual="/Gabby/Utilities.inc"-->
<!---#include virtual="/Gabby/Globals.inc"-->
<%

  Dim adodbConn, adodbRS
  Dim strSQL

  Set adodbConn = Server.CreateObject("ADODB.Connection")
  Set adodbRS = Server.CreateObject("ADODB.Recordset")

  adodbConn.Open MonitorCS, MonitorUserID, MonitorPassword

  strSQL = "SELECT Node FROM Node"
  adodbRS.Open strSQL, adodbConn, adOpenForwardOnly, adLockReadOnly
%>
<html>
  <head>
    <title>Test Page</title>
  </head>
  <body>
    <p><%=adodbRS.RecordCount%></p>
    <%While Not adodbRS.EOF%>
      <p><%=adodbRS.Fields("Node").Value%></p>
      <%adodbRS.MoveNext%>
    <%Wend%>
  </body>
</html>

I get this result...

Code:

-1

CICS-Region-1

CICS-Region-2

CICS-Region-3

localhost

NT-BackOffice-Server

Unix-DB-Server

Unix-DB-Server

Unix-DB-Server

UnixGateway

UnixGateway2

Since ADODBRecordset.RecordCount obviously doesn't work, is there any other way?

joan_fl · Apr 22nd, 2002, 11:52 AM

try this sql statement:

SELECT COUNT(Node) as NodeCount, Node FROM Node ORDER BY Node

You will now have a column NodeCount that contains the node count.

Hope this helps,

Joan

**CiberTHuG** · Apr 22nd, 2002, 12:05 PM

I always forget aliasing in SQL statements. I actually like that solution, though. A slap in the face to VBScript.

Though, I'm having problems.

Code:

  strSQL = "SELECT Node, COUNT(Node) as NodeCount FROM Node"
  'or
  strSQL = "SELECT COUNT(Node) as NodeCount, Node FROM Node"

Both give me an error.

[Microsoft][ODBC Microsoft Access Driver] You tried to execute a query that does not include the specified expression 'Node' as part of an aggregate function.

If I drop "Node" and just ask for "COUNT(Node) as NodeCount" it works, though I just get the count and not the data.

**billwagnon** · Apr 22nd, 2002, 12:26 PM

yourRS.recordcount should work if you substitute

'adOpenKeyset' for 'adOpenForwardOnly'

joan_fl · Apr 22nd, 2002, 01:18 PM

Sorry, instead of the ORDER BY. Use GROUP BY...

SELECT COUNT(Node) as NodeCount, Node FROM Node GROUP BY Node

**CiberTHuG** · Apr 22nd, 2002, 02:37 PM

There we go, thank you both.

Thread: ASP VBScript ADO Recordset RecordCount

Thread Tools

Display

ASP VBScript ADO Recordset RecordCount

Posting Permissions