Thread: Counting records?

J, i think it should be? :

Code:

SELECT Count(*) AS MyCount FROM performances WHERE (((performances.Status)=""Available""))"

???

Originally Posted by jmurrayhead

Try using the column name in the COUNT function instead:

Code:

SELECT Count(performances.performanceid) as MyCount,performances.performanceid,
       shows.showname,
       performances.status
FROM   shows
       INNER JOIN performances
         ON shows.showid = performances.showid
WHERE  (((performances.status) = "Available"));
GROUP BY performances.performanceid, shows.showname, performances.status

That still doesn't count properly.. What i just posted counts correctly in Access and when i implement that in my asp page and then dubug:

response.write (availabilityRS("MyCount"))

it shows "1" which is wrong!

Originally Posted by jmurrayhead

Also, since you're using the ADO recordset, you can simply do this:

Code:

Dim ticketCount
ticketCount = availabilityRS.RecordCount

See here: ADO RecordCount Property

I have had a bit of a go with what you posted. Unfortunatly the recordset count displays a "-1" value. Im lost now.. If anyone else knows of a solution, i would appreciate

edit: think i found the solution.. im gonna try it now

http://www.slxdeveloper.com/page.asp...e&articleid=33

Originally Posted by Centurion

I have had a bit of a go with what you posted. Unfortunatly the recordset count displays a "-1" value. Im lost now.. If anyone else knows of a solution, i would appreciate

edit: think i found the solution.. im gonna try it now

How To Retrieve a Record Count From an ADO Recordset by Ryan Farley

Having looked at the link.. I am having problems getting the page to work..
my code:

Code:

<%
Option Explicit

<!--#include virtual="adovbs.inc"-->

DIM availabilitySQL, availabilityConn, availabilityRS, adUseClient

set availabilityConn=Server.CreateObject("ADODB.Connection")
availabilityConn.ConnectionString="Provider=Microsoft.Jet.OLEDB.4.0"
availabilityConn.open server.mappath("../db/database.mdb")

'query to check all the shows that are available
availabilitySQL = "SELECT * FROM shows"

Set availabilityRS = Server.CreateObject("ADODB.Recordset")
availabilityRS.Open availabilitySQL, availabilityConn


With availabilityRS
	.CursorLocation = adUseClient
	.Open availabilitySQL, Application.availabilityConn

	MsgBox .RecordCount & " records"

	.Close
End With
Set availabilityRS = Nothing   
%>

Getting this error:

ADODB.Recordset error '800a0e79'

Operation is not allowed when the object is open.

/groupV2.asp, line 17

Originally Posted by jmurrayhead

You are opening it twice:

Code:

<%
Option Explicit

<!--#include virtual="adovbs.inc"-->

DIM availabilitySQL, availabilityConn, availabilityRS, adUseClient

set availabilityConn=Server.CreateObject("ADODB.Connection")
availabilityConn.ConnectionString="Provider=Microsoft.Jet.OLEDB.4.0"
availabilityConn.open server.mappath("../db/database.mdb")

'query to check all the shows that are available
availabilitySQL = "SELECT * FROM shows"

Set availabilityRS = Server.CreateObject("ADODB.Recordset")
availabilityRS.Open availabilitySQL, availabilityConn


With availabilityRS
	.CursorLocation = adUseClient
	.Open availabilitySQL, Application.availabilityConn

	MsgBox .RecordCount & " records"

	.Close
End With
Set availabilityRS = Nothing   
%>

Nope.. stil doesnt want to run...

ADODB.Recordset error '800a0e79'

Operation is not allowed when the object is open.

The object is supposed to be open thou!???

Originally Posted by jmurrayhead

Yes, it has to be open, but you are opening it twice. Change this:

Code:

With availabilityRS
	.CursorLocation = adUseClient
	.Open availabilitySQL, Application.availabilityConn

	MsgBox .RecordCount & " records"

	.Close
End With

To this:

Code:

With availabilityRS
	.CursorLocation = adUseClient
	MsgBox .RecordCount & " records"

	.Close
End With

If still errors after that, post the code you have and highlight the line the error is occurring on.

Sorry J, no luck for me.. here is my code:

Code:

<%Option Explicit

<!--#include virtual="adovbs.inc"-->

DIM availabilitySQL, availabilityConn, availabilityRS, adUseClient

set availabilityConn=Server.CreateObject("ADODB.Connection")
availabilityConn.ConnectionString="Provider=Microsoft.Jet.OLEDB.4.0"
availabilityConn.open server.mappath("../db/database.mdb")

'query to check all the shows that are available
availabilitySQL = "SELECT performances.performanceID, shows.showName, performances.status FROM shows INNER JOIN performances ON shows.showID = performances.showID WHERE  (((performances.status) = ""Available"")) GROUP BY performances.performanceID, shows.showName, performances.status"

Set availabilityRS = Server.CreateObject("ADODB.Recordset")
availabilityRS.Open availabilitySQL, availabilityConn


With availabilityRS
	.CursorLocation = adUseClient
	MsgBox .RecordCount & " records"
	.Close
End With
Set availabilityRS = Nothing   

%>