dtolo
asked on
Question Continued from previous class registration question
Hello I am continuing this question from: https://www.experts-exchange.com/questions/21170150/Is-this-issue-better-addressed-with-ASP.html
ASKER
*** If you already are getting other class information ***
strSQL = “Select * From Classes”
Set objRs = objConn.Execute(SQL)
If Not objRs.EOF Then MaxSize = objRS(“MaxSize”)
‘Do anything else with the class information
objRs.Close
strSQL = “Select Count(Students.Key) As CurStudents From Students Where Classes.Key = ###”
objRs.Open strSQL, objConn
If Not objRs.EOF Then CurrentCount = objRs(“CurStudents”)
If MaxSize - CurrentCount > 0 Then
Add Insert Code Here and Check for successful update.
Response.Write “You have been added – there are” & CurrentCount – 1 & “ available seats”
Else
Response.Write “ Class if full ”
End If
objRs.Close
Set objRs = Nothing
--------------------------
This is actually good that two types of solutions were presented I actually have two seperate class registration sites that I am working on and the two types of solutions fit the different registration pages. My question now concerns the one that actually only has a registration table and no class table. The table is called regcollect and the fields are regid, employeenumber, jdnumber, classname, and classdate.
regcollect has a relationship with an employee table based on the field employeenumber. After the student has taken the class someone manually updates information in the employee table which indicates that there elibility is good for x amount of years (collectstatus)
I think that I could use the part of the solution above the dotted on this current post for this solution. I don't quite understand this line:
strSQL = “Select Count(Students.Key) As CurStudents From Students Where Classes.Key = ###”
because of the table I am using a few differnt classes are in one table. Could classes.key be regcollect.classname ? That way I could count how many records with that particular classname there are.
Also the the classname and classdate information get passed to this form through a query string, could i also pass max size in the query string as well?
strSQL = “Select Count(Students.Key) As CurStudents From Students Where Classes.Key = ###”
This line is getting the current count of the registrants. Calsses.Key should be any unique field to identify the class and can be more than one field. Using regcollect.classname and regcollect.date should work.
Passing the max size could help because it would save you processing time. I would just be careful to validate the input. A smart user could alter the information submitted and increases the MaxSize causing an insert when it should be declined.
I need to update some of the code:
1. The available seat calculation was wrong.
Response.Write “You have been added – there are” & MaxSize – (CurrentCount +1) & “ available seats”
2. Should set MaxSize & CurrentCount to zero at top of script.
This way if the recordset is empty then the variables still holds a value.
MaxSize = 0 ‘
CurrentSize = 0
One more thought.
Do you have access to create a new table? Much like you are saving the employee number, saving the class number might make it easier. Then move the classname and classdate into a class table. In this table you can also save the MaxSize and Active / Inavtive status. This would allow you to only need to pass the class number over the query string, giving you a little more security and a better search query. It is also easier to validate a number than Classname and Classdate.
Carl
Do you have access to create a new table? Much like you are saving the employee number, saving the class number might make it easier. Then move the classname and classdate into a class table. In this table you can also save the MaxSize and Active / Inavtive status. This would allow you to only need to pass the class number over the query string, giving you a little more security and a better search query. It is also easier to validate a number than Classname and Classdate.
Carl
ASKER
Carl,
This is on an intranet so I am not really worried about security. I would rather do it the way you mentioned because it is definately better, but there is a huge amount of red tape around here and this thing is allready in production. This part is an add-on. So I am really limited to working with the one table.
i am a novice at asp so please pe patient with me.
(OK, so m = max size)
1. the user picks a calendar date with a classlink on it
2. the url passes "classname=juvenile detention&classdate=10/10/ 04&m=40"
3. to a page where the user enters an employee number to sign up for the class
4. before the user even gets an opportunity to enter the employee number I would like the routine to run and give the classfull message, I don't need to do the insert or any thing else all of those other pages are allready written.
So I think that putting your code first would be the best bet. Would this be how it would look?
<%
varClassName = request("ClassName")
varClassDate = request("ClassDate")
varM = request("M")
strSQL = “Select * From RegCollect”
Set objRs = objConn.Execute(SQL)
If Not objRs.EOF Then M = objRS(“M”)
‘Do anything else with the class information
objRs.Close
strSQL = “Select Count(Regcollect.regid) As CurStudents From RegCollect Where RegCollect.ClassName & RegCollect.ClassDate = varClassName & varClassDate”
objRs.Open strSQL, objConn
If Not objRs.EOF Then CurrentCount = objRs(“CurStudents”)
If MaxSize - CurrentCount > 0 Then
All My Other Code From the current page.
And This last part goes after all my code
Else
Response.Write “ Class if full ”
End If
objRs.Close
Set objRs = Nothing
This is on an intranet so I am not really worried about security. I would rather do it the way you mentioned because it is definately better, but there is a huge amount of red tape around here and this thing is allready in production. This part is an add-on. So I am really limited to working with the one table.
i am a novice at asp so please pe patient with me.
(OK, so m = max size)
1. the user picks a calendar date with a classlink on it
2. the url passes "classname=juvenile detention&classdate=10/10/
3. to a page where the user enters an employee number to sign up for the class
4. before the user even gets an opportunity to enter the employee number I would like the routine to run and give the classfull message, I don't need to do the insert or any thing else all of those other pages are allready written.
So I think that putting your code first would be the best bet. Would this be how it would look?
<%
varClassName = request("ClassName")
varClassDate = request("ClassDate")
varM = request("M")
strSQL = “Select * From RegCollect”
Set objRs = objConn.Execute(SQL)
If Not objRs.EOF Then M = objRS(“M”)
‘Do anything else with the class information
objRs.Close
strSQL = “Select Count(Regcollect.regid) As CurStudents From RegCollect Where RegCollect.ClassName & RegCollect.ClassDate = varClassName & varClassDate”
objRs.Open strSQL, objConn
If Not objRs.EOF Then CurrentCount = objRs(“CurStudents”)
If MaxSize - CurrentCount > 0 Then
All My Other Code From the current page.
And This last part goes after all my code
Else
Response.Write “ Class if full ”
End If
objRs.Close
Set objRs = Nothing
ASKER
Oh... I just realized I have a bunch of other else statments on my page. So maybey more like:
<%
varClassName = request("ClassName")
varClassDate = request("ClassDate")
varM = request("M")
strSQL = “Select * From RegCollect”
Set objRs = objConn.Execute(SQL)
If Not objRs.EOF Then M = objRS(“M”)
‘Do anything else with the class information
objRs.Close
strSQL = “Select Count(Regcollect.regid) As CurStudents From RegCollect Where RegCollect.ClassName & RegCollect.ClassDate = varClassName & varClassDate”
objRs.Open strSQL, objConn
If Not objRs.EOF Then CurrentCount = objRs(“CurStudents”)
If MaxSize - CurrentCount > 0
Else
Response.Write “ Class if full ”
My page code...
Is that right?
<%
varClassName = request("ClassName")
varClassDate = request("ClassDate")
varM = request("M")
strSQL = “Select * From RegCollect”
Set objRs = objConn.Execute(SQL)
If Not objRs.EOF Then M = objRS(“M”)
‘Do anything else with the class information
objRs.Close
strSQL = “Select Count(Regcollect.regid) As CurStudents From RegCollect Where RegCollect.ClassName & RegCollect.ClassDate = varClassName & varClassDate”
objRs.Open strSQL, objConn
If Not objRs.EOF Then CurrentCount = objRs(“CurStudents”)
If MaxSize - CurrentCount > 0
Else
Response.Write “ Class if full ”
My page code...
Is that right?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
OK,
I have This:
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>New Page 1</title>
</head>
<body>
<%
'Declare and Set our variables
Dim strSQL, objRs, objConnectionection, strConnectString
Dim intMaxSize, intCurrentSize
Dim strClassName, varClassDate, intM
strClassName = CheckInput(Request("ClassN ame"))
varClassDate = CheckInput(Request("ClassD ate"))
intM = CInt(Request("M"))
intCurrentSize = 0
strConnectString = "DRIVER={SQL Server};SERVER=appalachian \intrasql1 ;DATABASE= CSSD;UID=c ssdUser;PW D=cs#sd4$x g"
Response.write("<p>The new class Name is: " & strClassName & "<P>")
Response.write("<p>The new class date is: " & varClassDate & "<P>")
Response.write("<p>The new class size is: " & intM & "<P>")
' Get Class information and Count while taking advantage of the connection object
' Syntax may change due to the type of database you are using. Such as MS Access
' needs the date to have # at the beginning and ending: #" & Date & "#
' MS SQL Uses ' so it should be '" & Date & "'
strSQL = "SELECT * FROM RegCollect Where ClassName = '" & strClassName & "' " & _
"And ClassDate = '" & varClassDate & "'"
' open your connection to the server. Depending on the database
set objConnection=Server.Creat eObject("A DODB.Conne ction")
objConnection.ConnectionTi meout = 15
objConnection.CommandTimeo ut = 10
objConnection.Mode = 3 'adModeReadWrite
if objConnection.state = 0 then
objConnection.Open strConnectString
end if
'Create Recordset and get count
' intCurrentSize will hold affected records or in this case the count
Set objRs = objConnection.Execute(strS QL, intCurrentSize)
If Not objRs Then
' Do things in here with the data, for only one record
' Use Do While not objRs.EOF ..... Loop for All returned records
' If you loop through, move this next If statement out of the loop
If intM <= intCurrentSize then
'Class is at max
Else
'There is room
End IF
Else
' Not records exists for this search, give the user an error message
End If
objRs.Close
Set objRs = Nothing
'This should make things SQL friendly
Function CheckInput(n)
If n <> "" Then
If Instr(n,"'")<>0 Then n = Replace(n,"'","''")
If Instr(n,"""")<>0 Then n = Replace(n,"""","""""")
CheckInput=Trim(n)
End If
End Function
%>
</body>
</html>
I get this Error:
Type mismatch
/cssd/Collect/register/tes tReg/check Classsize. asp, line 48
Line 48 is:
If Not objRs Then
What did I do wrong here?
I have This:
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>New Page 1</title>
</head>
<body>
<%
'Declare and Set our variables
Dim strSQL, objRs, objConnectionection, strConnectString
Dim intMaxSize, intCurrentSize
Dim strClassName, varClassDate, intM
strClassName = CheckInput(Request("ClassN
varClassDate = CheckInput(Request("ClassD
intM = CInt(Request("M"))
intCurrentSize = 0
strConnectString = "DRIVER={SQL Server};SERVER=appalachian
Response.write("<p>The new class Name is: " & strClassName & "<P>")
Response.write("<p>The new class date is: " & varClassDate & "<P>")
Response.write("<p>The new class size is: " & intM & "<P>")
' Get Class information and Count while taking advantage of the connection object
' Syntax may change due to the type of database you are using. Such as MS Access
' needs the date to have # at the beginning and ending: #" & Date & "#
' MS SQL Uses ' so it should be '" & Date & "'
strSQL = "SELECT * FROM RegCollect Where ClassName = '" & strClassName & "' " & _
"And ClassDate = '" & varClassDate & "'"
' open your connection to the server. Depending on the database
set objConnection=Server.Creat
objConnection.ConnectionTi
objConnection.CommandTimeo
objConnection.Mode = 3 'adModeReadWrite
if objConnection.state = 0 then
objConnection.Open strConnectString
end if
'Create Recordset and get count
' intCurrentSize will hold affected records or in this case the count
Set objRs = objConnection.Execute(strS
If Not objRs Then
' Do things in here with the data, for only one record
' Use Do While not objRs.EOF ..... Loop for All returned records
' If you loop through, move this next If statement out of the loop
If intM <= intCurrentSize then
'Class is at max
Else
'There is room
End IF
Else
' Not records exists for this search, give the user an error message
End If
objRs.Close
Set objRs = Nothing
'This should make things SQL friendly
Function CheckInput(n)
If n <> "" Then
If Instr(n,"'")<>0 Then n = Replace(n,"'","''")
If Instr(n,"""")<>0 Then n = Replace(n,"""","""""")
CheckInput=Trim(n)
End If
End Function
%>
</body>
</html>
I get this Error:
Type mismatch
/cssd/Collect/register/tes
Line 48 is:
If Not objRs Then
What did I do wrong here?
ASKER
Am I opening the connection twice here? This is a page I am testing it on before I throw it into prod. The complete html out put is:
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>New Page 1</title>
</head>
<body>
<p>The new class Name is: bigtest<P><p>The new class date is: 10/10/04<P><p>The new class size is: 35<P> <font face="Arial" size=2>
<p>Microsoft VBScript runtime </font> <font face="Arial" size=2>error '800a000d'</font>
<p>
<font face="Arial" size=2>Type mismatch</font>
<p>
<font face="Arial" size=2>/cssd/Collect/regis ter/testRe g/checkCla sssize.asp </font><fo nt face="Arial" size=2>, line 48</font>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>New Page 1</title>
</head>
<body>
<p>The new class Name is: bigtest<P><p>The new class date is: 10/10/04<P><p>The new class size is: 35<P> <font face="Arial" size=2>
<p>Microsoft VBScript runtime </font> <font face="Arial" size=2>error '800a000d'</font>
<p>
<font face="Arial" size=2>Type mismatch</font>
<p>
<font face="Arial" size=2>/cssd/Collect/regis
ASKER
If I rem out my connection as so:
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>New Page 1</title>
</head>
<body>
<%
'Declare and Set our variables
Dim strSQL, objRs, objConnectionection, strConnectString
Dim intMaxSize, intCurrentSize
Dim strClassName, varClassDate, intM
strClassName = CheckInput(Request("ClassN ame"))
varClassDate = CheckInput(Request("ClassD ate"))
intM = CInt(Request("M"))
intCurrentSize = 0
strConnectString = "DRIVER={SQL Server};SERVER=appalachian \intrasql1 ;DATABASE= CSSD;UID=c ssdUser;PW D=cs#sd4$x g"
Response.write("<p>The new class Name is: " & strClassName & "<P>")
Response.write("<p>The new class date is: " & varClassDate & "<P>")
Response.write("<p>The new class size is: " & intM & "<P>")
' Get Class information and Count while taking advantage of the connection object
' Syntax may change due to the type of database you are using. Such as MS Access
' needs the date to have # at the beginning and ending: #" & Date & "#
' MS SQL Uses ' so it should be '" & Date & "'
strSQL = "SELECT * FROM RegCollect Where ClassName = '" & strClassName & "' " & _
"And ClassDate = '" & varClassDate & "'"
' open your connection to the server. Depending on the database
'set objConnection=Server.Creat eObject("A DODB.Conne ction")
'objConnection.ConnectionT imeout = 15
'objConnection.CommandTime out = 10
'objConnection.Mode = 3 'adModeReadWrite
'if objConnection.state = 0 then
'objConnection.Open strConnectString
'end if
'Create Recordset and get count
' intCurrentSize will hold affected records or in this case the count
Set objRs = objConnection.Execute(strS QL, intCurrentSize)
If Not objRs Then
' Do things in here with the data, for only one record
' Use Do While not objRs.EOF ..... Loop for All returned records
' If you loop through, move this next If statement out of the loop
If intM <= intCurrentSize then
'Class is at max
Else
'There is room
End IF
Else
' Not records exists for this search, give the user an error message
End If
objRs.Close
Set objRs = Nothing
'This should make things SQL friendly
Function CheckInput(n)
If n <> "" Then
If Instr(n,"'")<>0 Then n = Replace(n,"'","''")
If Instr(n,"""")<>0 Then n = Replace(n,"""","""""")
CheckInput=Trim(n)
End If
End Function
%>
</body>
</html>
I get:
The new class Name is: bigtest
The new class date is: 10/10/04
The new class size is: 35
Microsoft VBScript runtime error '800a01a8'
Object required: ''
/cssd/Collect/register/tes tReg/check Classsize. asp, line 46
I know I am missing something, but I don't understand the code enough to know what it is.
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>New Page 1</title>
</head>
<body>
<%
'Declare and Set our variables
Dim strSQL, objRs, objConnectionection, strConnectString
Dim intMaxSize, intCurrentSize
Dim strClassName, varClassDate, intM
strClassName = CheckInput(Request("ClassN
varClassDate = CheckInput(Request("ClassD
intM = CInt(Request("M"))
intCurrentSize = 0
strConnectString = "DRIVER={SQL Server};SERVER=appalachian
Response.write("<p>The new class Name is: " & strClassName & "<P>")
Response.write("<p>The new class date is: " & varClassDate & "<P>")
Response.write("<p>The new class size is: " & intM & "<P>")
' Get Class information and Count while taking advantage of the connection object
' Syntax may change due to the type of database you are using. Such as MS Access
' needs the date to have # at the beginning and ending: #" & Date & "#
' MS SQL Uses ' so it should be '" & Date & "'
strSQL = "SELECT * FROM RegCollect Where ClassName = '" & strClassName & "' " & _
"And ClassDate = '" & varClassDate & "'"
' open your connection to the server. Depending on the database
'set objConnection=Server.Creat
'objConnection.ConnectionT
'objConnection.CommandTime
'objConnection.Mode = 3 'adModeReadWrite
'if objConnection.state = 0 then
'objConnection.Open strConnectString
'end if
'Create Recordset and get count
' intCurrentSize will hold affected records or in this case the count
Set objRs = objConnection.Execute(strS
If Not objRs Then
' Do things in here with the data, for only one record
' Use Do While not objRs.EOF ..... Loop for All returned records
' If you loop through, move this next If statement out of the loop
If intM <= intCurrentSize then
'Class is at max
Else
'There is room
End IF
Else
' Not records exists for this search, give the user an error message
End If
objRs.Close
Set objRs = Nothing
'This should make things SQL friendly
Function CheckInput(n)
If n <> "" Then
If Instr(n,"'")<>0 Then n = Replace(n,"'","''")
If Instr(n,"""")<>0 Then n = Replace(n,"""","""""")
CheckInput=Trim(n)
End If
End Function
%>
</body>
</html>
I get:
The new class Name is: bigtest
The new class date is: 10/10/04
The new class size is: 35
Microsoft VBScript runtime error '800a01a8'
Object required: ''
/cssd/Collect/register/tes
I know I am missing something, but I don't understand the code enough to know what it is.
ASKER
How do i tell which object is missing?
Change this line to: If not objRs.EOF then
And uncomment your connection. I think it is causing an error out because objConnetion is not an object. And before it would error because it needed objRs.EOF
Carl
And uncomment your connection. I think it is causing an error out because objConnetion is not an object. And before it would error because it needed objRs.EOF
Carl
ASKER
Carl,
Thank you very much, you and fritz gave me the answer at exactly the same time. I have a few more questions about this. If you are interested you can follow the thread here: https://www.experts-exchange.com/questions/21174095/Can-you-tell-me-what-is-wrong-with-my-code.html#12349983
I appreciate All of your help, patience, and great work.
David
Thank you very much, you and fritz gave me the answer at exactly the same time. I have a few more questions about this. If you are interested you can follow the thread here: https://www.experts-exchange.com/questions/21174095/Can-you-tell-me-what-is-wrong-with-my-code.html#12349983
I appreciate All of your help, patience, and great work.
David
ASKER
Carl I have run some more tests and although I am not getting any errors, I am not getting stoped from being set to the redirect page and haveing a new class registered anyhow.
<%
'Declare and Set our variables
Dim strSQL, objRs, objConnectionection, strConnectString
Dim intMaxSize, intCurrentSize
'Dim Session("varClassName"), Session("varClassDate"), Session("intM")
Session("varClassName") = CheckInput(Request("ClassN ame"))
Session("varClassDate") = CheckInput(Request("ClassD ate"))
Session("intM") = CInt(Request("M"))
intCurrentSize = 0
strConnectString = "DRIVER={SQL Server};SERVER=appalachian \intrasql1 ;DATABASE= CSSD;UID=c ssdUser;PW D=cs#sd4$x g"
Response.write("<p>The new class Name is: " & Session("varClassName") & "<P>")
Response.write("<p>The new class date is: " & Session("varClassDate") & "<P>")
Response.write("<p>The new class size is: " & Session("intM") & "<P>")
' Get Class information and Count while taking advantage of the connection object
' Syntax may change due to the type of database you are using. Such as MS Access
' needs the date to have # at the beginning and ending: #" & Date & "#
' MS SQL Uses ' so it should be '" & Date & "'
strSQL = "SELECT * FROM RegCollect Where ClassName = '" & Session("varClassName") & "' " & _
"And ClassDate = '" & Session("varClassDate") & "'"
' open your connection to the server. Depending on the database
set objConnection=Server.Creat eObject("A DODB.Conne ction")
objConnection.ConnectionTi meout = 15
objConnection.CommandTimeo ut = 10
objConnection.Mode = 3 'adModeReadWrite
objConnection.Open strConnectString
'Create Recordset and get count
' intCurrentSize will hold affected records or in this case the count
Set objRs = objConnection.Execute(strS QL, intCurrentSize)
If Not objRs.eof Then
Response.Write("<Table Border=1 cellpadding=2 cellspacing=2>")
do while not objRS.EOF
Response.Write("<TR>")
for j=0 to objRS.Fields.count-1
Response.Write("<TD>" & objRS(j).Value & "</TD>")
next
Response.Write("</TR>")
objRS.MoveNext
loop
Response.Write("</Table>")
Else
Response.write("No records found!<p>")
End If
If Session("intM") <= intCurrentSize then
Response.write("Class is full!")
Else
'Response.Redirect("defaul t.asp")
End IF
'If Not objRs.eof Then
' Do things in here with the data, for only one record
' Use Do While not objRs.EOF ..... Loop for All returned records
' If you loop through, move this next If statement out of the loop
' If Session("intM") <= intCurrentSize then
'Class is at max
' Else
'There is room
' End IF
'Else
' Not records exists for this search, give the user an error message
'End If
objRs.Close
Set objRs = Nothing
objConnection.close
set objConnection = Nothing
'This should make things SQL friendly
Function CheckInput(n)
If n <> "" Then
If Instr(n,"'")<>0 Then n = Replace(n,"'","''")
If Instr(n,"""")<>0 Then n = Replace(n,"""","""""")
CheckInput=Trim(n)
End If
End Function
%>
I set m to 4, I am not getting the class is full message. This is the output in html:
The new class Name is: collect training
The new class date is: 10/5/04 6:00 am
The new class size is: 4
30 111111 collect training 10/5/2004 6:00:00 AM
32 396893 JDT8482 collect training 10/5/2004 6:00:00 AM
34 111111 collect training 10/5/2004 6:00:00 AM
35 111111 collect training 10/5/2004 6:00:00 AM
36 111111 collect training 10/5/2004 6:00:00 AM
37 111111 collect training 10/5/2004 6:00:00 AM
38 111111 collect training 10/5/2004 6:00:00 AM
<%
'Declare and Set our variables
Dim strSQL, objRs, objConnectionection, strConnectString
Dim intMaxSize, intCurrentSize
'Dim Session("varClassName"), Session("varClassDate"), Session("intM")
Session("varClassName") = CheckInput(Request("ClassN
Session("varClassDate") = CheckInput(Request("ClassD
Session("intM") = CInt(Request("M"))
intCurrentSize = 0
strConnectString = "DRIVER={SQL Server};SERVER=appalachian
Response.write("<p>The new class Name is: " & Session("varClassName") & "<P>")
Response.write("<p>The new class date is: " & Session("varClassDate") & "<P>")
Response.write("<p>The new class size is: " & Session("intM") & "<P>")
' Get Class information and Count while taking advantage of the connection object
' Syntax may change due to the type of database you are using. Such as MS Access
' needs the date to have # at the beginning and ending: #" & Date & "#
' MS SQL Uses ' so it should be '" & Date & "'
strSQL = "SELECT * FROM RegCollect Where ClassName = '" & Session("varClassName") & "' " & _
"And ClassDate = '" & Session("varClassDate") & "'"
' open your connection to the server. Depending on the database
set objConnection=Server.Creat
objConnection.ConnectionTi
objConnection.CommandTimeo
objConnection.Mode = 3 'adModeReadWrite
objConnection.Open strConnectString
'Create Recordset and get count
' intCurrentSize will hold affected records or in this case the count
Set objRs = objConnection.Execute(strS
If Not objRs.eof Then
Response.Write("<Table Border=1 cellpadding=2 cellspacing=2>")
do while not objRS.EOF
Response.Write("<TR>")
for j=0 to objRS.Fields.count-1
Response.Write("<TD>" & objRS(j).Value & "</TD>")
next
Response.Write("</TR>")
objRS.MoveNext
loop
Response.Write("</Table>")
Else
Response.write("No records found!<p>")
End If
If Session("intM") <= intCurrentSize then
Response.write("Class is full!")
Else
'Response.Redirect("defaul
End IF
'If Not objRs.eof Then
' Do things in here with the data, for only one record
' Use Do While not objRs.EOF ..... Loop for All returned records
' If you loop through, move this next If statement out of the loop
' If Session("intM") <= intCurrentSize then
'Class is at max
' Else
'There is room
' End IF
'Else
' Not records exists for this search, give the user an error message
'End If
objRs.Close
Set objRs = Nothing
objConnection.close
set objConnection = Nothing
'This should make things SQL friendly
Function CheckInput(n)
If n <> "" Then
If Instr(n,"'")<>0 Then n = Replace(n,"'","''")
If Instr(n,"""")<>0 Then n = Replace(n,"""","""""")
CheckInput=Trim(n)
End If
End Function
%>
I set m to 4, I am not getting the class is full message. This is the output in html:
The new class Name is: collect training
The new class date is: 10/5/04 6:00 am
The new class size is: 4
30 111111 collect training 10/5/2004 6:00:00 AM
32 396893 JDT8482 collect training 10/5/2004 6:00:00 AM
34 111111 collect training 10/5/2004 6:00:00 AM
35 111111 collect training 10/5/2004 6:00:00 AM
36 111111 collect training 10/5/2004 6:00:00 AM
37 111111 collect training 10/5/2004 6:00:00 AM
38 111111 collect training 10/5/2004 6:00:00 AM
ASKER
I don't see where where intCurrentSize is getting the count.
I added this:
If intM <= intCurrentSize then
Response.write("Class is full!")
Else
'Response.Redirect("defaul t.asp")
End IF
Response.write("<p>the Current Size is: " & intCurrentSize & "<P>")
and the html output is:
The new class Name is: collect training
The new class date is: 10/5/04 6:00 am
The new class size is: 4
30 111111 collect training 10/5/2004 6:00:00 AM
32 396893 JDT8482 collect training 10/5/2004 6:00:00 AM
the Current Size is: -1
I added this:
If intM <= intCurrentSize then
Response.write("Class is full!")
Else
'Response.Redirect("defaul
End IF
Response.write("<p>the Current Size is: " & intCurrentSize & "<P>")
and the html output is:
The new class Name is: collect training
The new class date is: 10/5/04 6:00 am
The new class size is: 4
30 111111 collect training 10/5/2004 6:00:00 AM
32 396893 JDT8482 collect training 10/5/2004 6:00:00 AM
the Current Size is: -1
ASKER
this question has been continued to https://www.experts-exchange.com/questions/21175667/SQL-Count.html
ASKER
The Query below assumes that you have atleast two tables. One for classes and one for registrants (Students) and the have a common key.
SELECT Classes.MaxSize - COUNT(Students.Key) AS CurrentCount
FROM Classes INNER JOIN
Students ON Classes.Key = Students.Key
WHERE (Classes.Key = ‘Requested Class’)
GROUP BY Classes.MaxSize
This will return the available number of spots available. If it is greater then 0 then you can add the new person. If not, then display a friendly message saying the class is full.
objConn ‘ = Active Connetion
Dim CurrentCount
strSQL = “SELECT Classes.MaxSize - COUNT(Students.Key) AS CurrentCount “ & _
“ FROM Classes INNER JOIN Students ON Classes.Key = Students.Key” & _
“ WHERE (Classes.Key = ‘Requested Class’) GROUP BY Classes.MaxSize”
Set objRS = objConn.Execute(SQL)
If not objRs.EOF Then CurrentCount = objRs(“CurrentCount”)
If CurrentCount > 0 Then
Add Insert Code Here and Check for successful update.
Response.Write “You have been added – there are” & CurrentCount – 1 & “ available seats”
Else
Response.Write “Class if full ”
End If
objRs.Close
Set objRs = Nothing