I found myself at a complete loss when I was asked to work on a project.  

I hope that you can help me!

Here's the scenario:  I'm trying to have users register for an event 

(SignUp), but I always want to stop users from signing up for an event if 

the availability is filled to capacity (Calendar.avail).  I was hoping to 

redirect to a page when the capacity was met.  I want to take the total 

count of people signed up for a specified event and have that less than or 

equal to the total availability.  I tried to generate a SQL 

statement 'almost' doing what I want; however, I'm not certain how to 

integrate it into my ASP.  I have all the connections and whatnot already 

set up and functioning.  This is just the last piece to finishing this.



Here's my SQL statement:

SELECT COUNT(SignUp.Contact_ID) AS Total, SignUp.Event_ID, 

    Calendar.Avail

FROM SignUp INNER JOIN

    Calendar ON SignUp.Event_ID = Calendar.Event_ID

WHERE (SignUp.Event_ID = ?)

GROUP BY SignUp.Event_ID, Calendar.Avail

HAVING COUNT(SignUp.Contact_ID) <= Calendar.Avail;



How would I do something like this in ASP?  Thanks in advance!

Are you using SQL Server? If so, you can use a sproc to check the current

number of participants -vs- available spots, and only insert the new record

is there is still space. Use an output parameter to indicate whether the

insert actually happened or not.



If you are using Access, then you could return the results of that query to

an ADO recordset.

You need to Select the Count() of the current participants, plus the maximum

number available. In ASP compare the two. If the Count() is still less than

the max number, then do an INSERT.



Cheers

Ken





~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

----- Original Message -----

From: <penguin0012@a...>

To: "ASP Web HowTo" <asp_web_howto@p...>

Sent: Thursday, July 19, 2001 7:14 PM

Subject: [asp_web_howto] using ASP to limit submission





: I found myself at a complete loss when I was asked to work on a project.

: I hope that you can help me!

: Here's the scenario:  I'm trying to have users register for an event

: (SignUp), but I always want to stop users from signing up for an event if

: the availability is filled to capacity (Calendar.avail).  I was hoping to

: redirect to a page when the capacity was met.  I want to take the total

: count of people signed up for a specified event and have that less than or

: equal to the total availability.  I tried to generate a SQL

: statement 'almost' doing what I want; however, I'm not certain how to

: integrate it into my ASP.  I have all the connections and whatnot already

: set up and functioning.  This is just the last piece to finishing this.

:

: Here's my SQL statement:

: SELECT COUNT(SignUp.Contact_ID) AS Total, SignUp.Event_ID,

:     Calendar.Avail

: FROM SignUp INNER JOIN

:     Calendar ON SignUp.Event_ID = Calendar.Event_ID

: WHERE (SignUp.Event_ID = ?)

: GROUP BY SignUp.Event_ID, Calendar.Avail

: HAVING COUNT(SignUp.Contact_ID) <= Calendar.Avail;

sSQL =3D "SELECT COUNT(SignUp.Contact_ID) AS Total, SignUp.Event_ID,

Calendar.Avail"

sSQL =3D sSQL & " FROM SignUp INNER JOIN Calendar ON SignUp.Event_ID 

=3D

Calendar.Event_ID"

sSQL =3D sSQL & " WHERE (SignUp.Event_ID =3D ?)"

sSQL =3D sSQL & " GROUP BY SignUp.Event_ID, Calendar.Avail"

sSQL =3D sSQL & " HAVING COUNT(SignUp.Contact_ID) <=3D Calendar.Avail;"



set oConn =3D Server.CreateObject("ADODB.Connection")

oConn.Open sDSN



set oRS =3D oConn.Execute(sSQL)



'now oRS is your recordset, which you can do what you want with...



e.g.



oRS("Total") is the total field, oRS("Avail") is the Avail field, etc



use oRS.MoveNext to move through your records



check oRS.EOF to ensure there is a record before attempting to read 

from it



you could use a loop to go through all the records



Do Until oRS.EOF

	'do stuff

	oRS.MoveNext

Loop



-----Original Message-----

From: penguin0012@a... [mailto:penguin0012@a...]

Sent: 19 July 2001 20:15

To: ASP Web HowTo

Subject: [asp_web_howto] using ASP to limit submission





I found myself at a complete loss when I was asked to work on a 

project. 

I hope that you can help me!

Here's the scenario:  I'm trying to have users register for an event

(SignUp), but I always want to stop users from signing up for an event 

if

the availability is filled to capacity (Calendar.avail).  I was hoping 

to

redirect to a page when the capacity was met.  I want to take the total 



count of people signed up for a specified event and have that less than 

or

equal to the total availability.  I tried to generate a SQL

statement 'almost' doing what I want; however, I'm not certain how to

integrate it into my ASP.  I have all the connections and whatnot 

already

set up and functioning.  This is just the last piece to finishing this.



Here's my SQL statement:

SELECT COUNT(SignUp.Contact_ID) AS Total, SignUp.Event_ID,

    Calendar.Avail

FROM SignUp INNER JOIN

    Calendar ON SignUp.Event_ID =3D Calendar.Event_ID

WHERE (SignUp.Event_ID =3D ?)

GROUP BY SignUp.Event_ID, Calendar.Avail

HAVING COUNT(SignUp.Contact_ID) <=3D Calendar.Avail;



How would I do something like this in ASP?  Thanks in advance!