ASP - Counting records

[artsapimp]

I know I have done this before when running some tutorial but I can't remember what I'm missing. I'm trying to find the number of records in a table where for a specific rep.


Dim intRecords

strSQL = "SELECT Count(*) AS intRecords, TicketNumber, Rep"
strSQL = strSQL = " WHERE Rep = " & Request.Form("Rep")



Thanks

[mosquito]

I think you want to do something like this.

strSQL = "SELECT Count(*) AS intRecords, TicketNumber, Rep"
strSQL = strSQL & "FROM tbl"
strSQL = strSQL & " WHERE Rep = " & Request.Form("Rep") & " "
strSQL = strSQL & "GROUP BY TicketNumber, Rep "

[artsapimp]

Other than being a little neater than mine, is there a difference in the statement?

[mosquito]

The Group by statement

[artsapimp]

Perfect, thank you.

You don't know how to set the page to timeout as soon as they submit a form.... so they can't go back and create more than one record?

[mosquito]

Not really. You can execute an ado command asynchronously, but I don't think that gives the desired effect.

[artsapimp]

I tried at vbforums.com and they didn't know either. Not a problem.

I have another question though.

I am having some problems writing a page that grabs information from an access database. The page is SUPPOSED to make a table row for each Rep including the Rep's Name and the amount of tickets they've entered withing a time frame.

The strSQL looks like this

strSQL = "SELECT Count(*) As intTickets, Opened_By FROM Tickets"
strSQL = strSQL & " WHERE Opened_Date Between #" & Request.form("Date_From") & "# AND #" & Request.Form("Date_To") & "#"
strSQL = strSQL & " GROUP by Opened_By"


I am having a problem with the FOR EACH loop.


For each Opened_By in ?
%>
<tr>
<td><%= rst("Opened_By")%></td>
<td><%= rst("intTickets")%></td>
</tr>
<%
Next
...

Thanks for any help you can offer.

[UncaDanno]

So, are you executing the SQL string by opening a recordset? Something like:

Set rsTickets = connTicketConn.execute strSQL ?

If so, you build a loop:

While not rsTickets.EOF
Response.Write "&lt;TR&gt;&lt;TD&gt;" &amp; rsTickets("Opened_By") &amp; _
"&lt;/TD&gt;&lt;TD&gt;" &amp rsTickets("intTickets") &amp "&lt;/TD&gt;&lt;/TR&gt;"
rsTickets.MoveNext
Loop


Or am I way out in left field?


[Edited by UncaDanno on 01-17-2001 at 10:53 PM]

[artsapimp]

That is exactly what I need to do, but I'm stuck with something I don't think anyone online can help me with. I'm getting an error that says "Object Required" if I put the script into the page. If I put it at the top of the page it works fine. Same strSQL and everything. I don't know what I'm doing wrong, but it has to be within the page somewhere that's stopping it from working.

I'll keep reading into the page and let you know if I have any more problems.

Thanks again for your help everyone.

[artsapimp]

I found it. Thanks guys. It was stuck in an include file that I thought would not change what I was doing. Thanks again for your help.