ASP VBScript ADO Recordset RecordCount

Printable View

Apr 22nd, 2002, 11:47 AM
CiberTHuG

ASP VBScript ADO Recordset RecordCount

Given this code...

Code:

<% @Language="VBScript" %> <% Option Explicit %>   <% 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?
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
Apr 22nd, 2002, 12:05 PM
CiberTHuG

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.

Quote:

[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.
Apr 22nd, 2002, 12:26 PM
billwagnon

yourRS.recordcount should work if you substitute

'adOpenKeyset' for 'adOpenForwardOnly'
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
Apr 22nd, 2002, 02:37 PM
CiberTHuG

There we go, thank you both.