Thread: Counting records?

Same error? What line? highlight the line in your code.

Nevermind...you were actually opening the database connection three times. Comment out this line:

Code:

availabilityConn.open server.mappath("../db/database.mdb")

Originally Posted by jmurrayhead

Nevermind...you were actually opening the database connection three times. Comment out this line:

Code:

availabilityConn.open server.mappath("../db/database.mdb")

No J, the db connection is just opened once:

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   

%>

The connection cannot be used to perform this operation. It is either closed or invalid in this context.

You've yet to highlight the line the error is occurring on. This will help determine the problem. However, this should work:

Code:

set availabilityConn=Server.CreateObject("ADODB.Connection")
availabilityConn.Provider="Microsoft.Jet.OLEDB.4.0"
availabilityConn.Open(Server.Mappath("../db/database.mdb"))

set availabilityRS=Server.CreateObject("ADODB.recordset")
availabilitySQL="SELECT performances.performanceID, shows.showName, performances.status FROM shows INNER JOIN performances ON shows.showID = performances.showID WHERE  (((performances.status) = ""Available""))"
availabilityRS.Open availabilitySQL,availabilityConn

if availabilityRS.Supports(adApproxPosition)=true then
  i=availabilityRS.RecordCount
  response.write("The number of records is: " & i)
end if

availabilityRS.Close
availabilityConn.Close

If you'd like, you can zip up your application (including the database) and attach it here so I can run on my IIS server to test out. Or you can email me the ZIP'd file. I'll post back with my results.

The following code gave me, "The number of records is: 4", and I see there are only 4 records in the database:

Code:

Dim cn, rs, sql, connectionString
connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("db/database.mdb") & ";Jet OLEDB;"
set cn = Server.CreateObject("ADODB.Connection")
set rs = Server.CreateObject("ADODB.RecordSet")

sql = "SELECT COUNT(1) As TicketCount FROM shows"
cn.Open(connectionString)
set rs = cn.Execute(sql)
if Not rs.BOF And Not rs.EOF then
	response.write("The number of records is: " & rs("TicketCount"))
end if
rs.Close
cn.Close
set cn = Nothing
set rs = Nothing