Thread: Counting records?

I want to count the number of records from this query:

Code:

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

My logic:

1. User enters values on form.
2. Query uses form values to build query to count the amount of records.
   
3. IF DB record count is > than user selection
4. THEN Insert records...

But putting SELECT COUNT (*)... causes me problems.
And how would i write point 3 of my logic in code?
Any ideas?

Hi there,

Did you try like this?

Code:

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

Originally Posted by Rebelle

Hi there,

Did you try like this?

Code:

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

Thanks, i added that. Running the query in Access brings the number of records up.

Now i just need to get IF statement to check the records?
I have wrote this code:

Code:

tickets = 10

IF availabilityRS > tickets  THEN 
response.write "There are more than 10 records in the DB"
ELSEIF < tickets response.write "There are less than 10 records in the DB"
end if
%>

Originally Posted by jmurrayhead

Actually, I would write the SQL like this for performance reasons:

Code:

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

Then, to check the count:

Code:

tickets = 10

If CInt(availabilityRS("MyCount")) > tickets Then 
    Response.Write "There are more than 10 records in the DB"
ElseIf CInt(availabilityRS("MyCount")) < tickets Then
    Response.Write "There are less than 10 records in the DB"
End If
%>

Got this error:

Microsoft JET Database Engine error '80040e21'

You tried to execute a query that does not include the specified expression 'performanceID' as part of an aggregate function.

/group.asp, line 78

What is it that you were trying to do J?

Originally Posted by jmurrayhead

It's missing a GROUP BY clause:

Code:

SELECT Count(1) 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

Yes that sounds about rite, was just reading up on the error message on google. BUT its complaining about line 83:

Code:

If CInt(availabilityRS("MyCount")) > tickets Then

It doesnt seem to recognise the "MyCount" as it gives an error of :

ADODB.Recordset error '800a0cc1'

Item cannot be found in the collection corresponding to the requested name or ordinal.

/group.asp, line 83

Originally Posted by jmurrayhead

Try this:

Code:

SELECT Count(1) 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

If that doesn't work, post all relevant ASP code for the page.

It does work, but the problem it isnt counting the total records. The expression shows a "1" next to each record in the database(when i test in MS ACCESS) when it should say something like 65!

my relevant code:

Code:

 

<%
DIM availabilitySQL, availabilityConn, availabilityRS

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 Count(*) 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"


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

'testing the 
tickets = 800

If CInt(availabilityRS("MyCount")) > tickets Then 
    Response.Write "There are more than 10 records in the DB"
ElseIf CInt(availabilityRS("MyCount")) < tickets Then
    Response.Write "There are less than 10 records in the DB"
End If
%>