Error using COUNT statement

[snowboardr]

Microsoft VBScript runtime error '800a01b6'

Object doesn't support this property or method: 'RecordCount'

<%

Dim ConnString, strSQL



ConnString = &quot;Provider=Microsoft.Jet.OLEDB.4.0; Data Source=&quot; & Server.MapPath(&quot;db6236dcsf2de.mdb&quot;)



' ADO connection class

set my_conn= Server.CreateObject(&quot;ADODB.Connection&quot;)



' rs recordset will contain all fields in db such as userID, name, email 

set rs = Server.CreateObject(&quot;ADODB.RecordSet&quot;)



' Connect ADO & open database

my_conn.Open ConnString



strSQL = &quot;SELECT COUNT(*) FROM UserEmails&quot;



' Execute SQL statement

rs = my_conn.Execute(strSQL)



' Write out the number of records

Response.Write &quot;There are <b>&quot; & rs.RecordsCount & &quot;</b> records.&quot;



my_conn.close ' Close database connection

set my_conn = nothing 'obj variable released



%>

 

[exodus300]

Try this:

Response.Write rs.Fields(0)

Using SELECT COUNT(*) FROM UserEmails returns a table similar to this (in this example there are 99 records)

Expr0001
--------
99

[Thanks in advance|Hope I helped you]
Exodus300
World-Wide Alliance of Evil and Twisted People
[red]"Experimentation by Example is the best learning tool" - Exodus300[/red]

 

[snowboardr]

That didn't work =/

Microsoft VBScript runtime error '800a01b6'

Object doesn't support this property or method: 'Fields'
jason@vzio.com

http://www.vzio.com/?tt

 

[exodus300]

This is some code taken from an asp page I made which allows you to type in any SQL query and view the results. At the top there is a counter of how many records were returned. The q variable contains the SQL query. This script works fine for me. The lines which might interest you are red


Response.Write &quot;The SQL query you submitted was:<br>&quot;
Response.Write &quot;<font color=&quot;&quot;green&quot;&quot;><b>&quot; & Replace(Server.HTMLEncode(q), vbCRLF, &quot;<br>&quot & &quot;</b></font>&quot;
Response.Write &quot;<br><br>&quot;

' Connect to database
Set objConnection = Server.CreateObject(&quot;ADODB.Connection&quot
objConnection.Open(&quot;DB_NAME&quot

' Count and display the number of records
[red]Set rs = objConnection.Execute(&quot;SELECT COUNT(*) FROM (&quot; & q & &quot&quot
' You should put SELECT COUNT(*) FROM TableName to count all records
If Not rs.EOF Then
Response.Write &quot;This query yielded <b>&quot;
Response.Write rs.Fields(0)
Response.Write &quot;</b> results, shown following:&quot;
Response.Write &quot;<br><br>&quot;
End If
[/red]

' Get the recordset
Set rs = objConnection.Execute(q)

If rs.EOF Then
' No records found
Response.Write &quot;<font color=&quot;&quot;red&quot;&quot;>No matching records found!</font> &quot;
Response.Write &quot;<a href=&quot;&quot;pm.asp?q=&quot; & Server.URLEncode(q) & &quot;&quot;&quot;>Execute query again</a><br>&quot;
Else
Response.Write &quot;<table border=&quot;&quot;0&quot;&quot;>&quot;
Response.Write &quot;<tr>&quot;
For T = 0 To rs.Fields.Count - 1
Response.Write &quot;<th>&quot; & rs.Fields(T).Name & &quot;</th>&quot;
Next
Response.Write &quot;</tr>&quot;
Do While Not rs.EOF
Response.Write &quot;<tr>&quot;
For I = 0 To rs.Fields.Count - 1
Response.Write &quot;<td>&quot; & rs.Fields(I) & &quot;</td>&quot;
Response.Flush
Next
Response.Write &quot;</tr>&quot;
rs.MoveNext
Loop
Response.Write &quot;</table>&quot;
End If
rs.Close
Set rs = Nothing
objConnection.Close
Set objConnection = Nothing

[Thanks in advance|Hope I helped you]
Exodus300
World-Wide Alliance of Evil and Twisted People
[red]&quot;Experimentation by Example is the best learning tool&quot; - Exodus300[/red]

 

[GabeC]

Change

' Execute SQL statement
rs = my_conn.Execute(strSQL)

to

rs.open strSQL, my_conn

Using the sql you wrote you now would use

rs.Fields(0)

to find out how many records met your criteria.

Besure to close your recordset

rs.close
set rs = nothing

Thanks,

Gabe