Event Registration form

[vabeach757]

Hello all, I am having a lot of problems trying to add + 1 to my listed code. The form accepts registrations from an individual and their guest. How could I add the count for guest in the code below. Seriously, stuck on this please help...

<% 
dim r, sqlCount, sqlList, tableNr
tableNr = Cint("1") 'use request("tableNr") to have the tableNr passed in as part of a querystring
%>
 
 
<p>People booked on Table&nbsp;<%=tableNr%></p>
<p>
 
<%
 
'create recordset object
Set r = Server.CreateObject("ADODB.Recordset")
r.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.mappath("seating.mdb")
 
'Construct SQLCount statement 
SQLCount = "SELECT count(Table1.tableNo) AS CountOftableNo FROM Table1 WHERE Table1.tableNo=" & tableNr & "" 
 
 
SQLList = "SELECT firstname, lastname, guestname FROM Table1 WHERE Table1.tableNo=" & tableNr & ""
 
'execute SQLCount statement
r.Open sqlCount
 
dim SeatsBooked, SeatsLeft
 
SeatsBooked = r("CountoftableNo")
SeatsLeft = 10 -  SeatsBooked 
 
response.write "There are "
response.write SeatsBooked
response.write " people sitting at this table."
response.write "<br>"
response.write "<br>"
response.write r("CountoftableNo")
 
r.close
'Set r = NOTHING
'execute SQLList statement
r.Open sqlList
While NOT r.EOF
response.write r("firstname")
response.write "&nbsp;"
response.write r("lastname")
response.write "<br>"
response.write r("guestname")
r.MoveNext
WEND
r.close
Set r = NOTHING
 
 
%>
 
 
<p>
 
<% IF SeatsBooked = 10 THEN %>
 
This table is fully booked. Please try another table.
 
<%ELSE%>
 
There are <%=SeatsLeft%> available seats at this table. To join this table complete the form below.
</p>
 
 
 
<%END IF%>

[jhardman]

So a given user may or may not have a guest? The most straight-forward method to solve this problem is to give guests their own entry in the db.

The second easiest approach (requires no change to the db, but more coding) is to open another recordset. What does the guest field say if the user doesn't have a guest? maybe "none" or null. you open the same recordset but with an additional statement in the where clause - "AND guestName <> 'none'" the count in this recordset is the number of users who have a guest. Add this count to the count in your other recordset for the total number of seats taken.

The most awkward way to solve this problem is the old-fashioned way: count (loop through the data and count whether there is a guest). But of course this isn't very elegant.

Let me know if this helps.

Jared

[markrawlingson]

Correct me if i'm misunderstanding..

You have a registration form where a registrant can sign up for an event, and they may or may not have a guest. If they do have a guest, it is kept in the same record as the person who has registered at that particular table. The problem you are having is you cannot count the guest because the guest is contained within the same record as the registrant. Correct?

The most efficient and easiet way to do this would be to keep the guest as a separate record from the registrant him/her self. You can do this by, as suggested above, having the guest come and register him/herself - OR you could insert the guest as a NEW record when you call your insert statement.

So if a person comes to register and enters a guest - you'd update the record as such..

rs.addnew
rs("firstname") = sFirstName
rs("lastname") = sLastName
rs.Update

rs.AddNew
rs("firstname") = sGuestName
rs("lastname")= sGuestLastName
rs.Update

Then it will count correctly.

However, if you wish to keep it the way you've already got, the only plausible way would be to loop through the recordset, counting +2 for each record that has a guest attached to it, and +1 for each record that does not have a guest attached to it... and then move back to the first record of course.

do until rs.eof
If Not IsNull(rs("GuestName")) Then
regcount = regcount + 2
Else
regCount = regCount+1
end if
rs.movenext
loop
rs.movefirst

Hope this helps.