ASP Code HELP

cyberwit · 22-04-2008 8:58pm #1

<html>
<head>
<title>Course Search</title>
</head>
<body>
<%
Set oConn = Server.CreateObject("ADODB.Connection")
sConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
              "Data Source=" & Server.MapPath("db\HR System.mdb") & ";" 
oConn.Open(sConnection)
'SELECT from the database
sSQL = "SELECT EmployeePPS, FirstName, LastName, DOB, Address, County, Mobile, Email, Dept from Employee where EmployeePPS='" & Request("EmployeePPS") &"'"
sSQL = "SELECT StudentNo, EmployeePPS, GradYear, Qualifications, CourseTitle, Institute FROM Education where EmployeePPS='" & Request("EmployeePPS") &"'"
set oRS = oConn.Execute(sSQL)
set oRS2 = oConn.Execute(sSQL)
%>
<p><font face="Arial" size="6" color="#000080">Course - Employee Search Results</font></p>
<% 
if NOT oRS.EOF then
Response.Write("<table border = 1><tr><td><strong>EmployeePPS<td><strong>FirstName<td><strong>LastName<td><strong>DOB<td><strong>Address<td><strong>County<td><strong>Mobile<td><strong>Email<td><strong>Dept</td></tr>")
else if NOT oRS2.EOF then
Response.Write("<table border = 1><tr><td><strong>StudentNo<td><strong>EmployeePPS<td><strong>GradYear<td><strong>Qualifications<td><strong>CourseTitle<td><strong>Institute</td></tr>")
else

Response.Write("<h4>No Matching Course details</h4>")
end if
%>
<tr>
<%
Do While NOT oRS.EOF 
  %>
     <tr>
     <td><%=oRS("EmployeePPS")%>
     <td><%=oRS("FirstName")%>
     <td><%=oRS("LastNamee")%>
     <td><%=oRS("CourseDescription")%>
     <td><%=oRS("StartDate")%>
     <td><%=oRS("FinishDate")%>
     <td><%=oRS("DOB")%>
     <td><%=oRS("Address")%>
     <td><%=oRS("County")%>
     <td><%=oRS("Mobile")%>
     <td><%=oRS("Email")%>
     <td><%=oRS("Dept")%>
     
      <%oRS.MoveNextLoop %>
</table>
<%
Do While NOT oRS2.EOF
  %>
     <tr>
     <td><%=oRS2("StudentNo")%>
     <td><%=oRS2("EmployeePPS")%>
     <td><%=oRS2("GradYear")%>
     <td><%=oRS2("Qualifications")%>
     <td><%=oRS2("CourseTitle")%>
     <td><%=oRS2("Institute")%>
     
      <%oRS2.MoveNextLoop %>
</table>
<%
oRS.Close
oRS2.Close
oConn.Close
Set oRS = Nothing
Set oRS2 = Nothing
Set oConn = Nothing
%>%>
<p>
<a href="JavaScript:window.close()">Close</a>
</body>
</html>

Gettting the following error

Error Type:
Microsoft VBScript compilation (0x800A03FB)
Expected 'Loop'
/Employee Stuff Test/listemployee.asp, line 67

Browser Type:

_RoSy_ · 22-04-2008 10:45pm

Hey,

Whenever you use a Do statement you need to have a corresponding Loop statement, so it should always be:

Do

...some code in here
...and some more code in here

Loop

It looks like the line:

<%oRS.MoveNextLoop %>

should actually be over two lines, so it should read:

<%
oRS.MoveNext
Loop
%>

Try that and see if it stops the error (it occurs twice in the code you posted so make sure to change it in both places!)

Hope that helps,

R.

cyberwit · 22-04-2008 11:14pm

<html>
<head>
<title>Course Search</title>
</head>
<body>
<%
Set oConn = Server.CreateObject("ADODB.Connection")
sConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
              "Data Source=" & Server.MapPath("db\HR System.mdb") & ";" 
oConn.Open(sConnection)
'SELECT from the database
sSQL = "SELECT EmployeePPS, FirstName, LastName, DOB, Address, County, Mobile, Email, Dept from Employee where EmployeePPS='" & Request("EmployeePPS") &"'"
sSQL = "SELECT StudentNo, EmployeePPS, GradYear, Qualifications, CourseTitle, Institute FROM Education where EmployeePPS='" & Request("EmployeePPS") &"'"
set oRS = oConn.Execute(sSQL)
%>
<p><font face="Arial" size="6" color="#000080">Course - Employee Search Results</font></p>
<% 
if NOT oRS.EOF then
Response.Write("<table border = 1><tr><td><strong>EmployeePPS<td><strong>FirstName<td><strong>LastName<td><strong>DOB<td><strong>Address<td><strong>County<td><strong>Mobile<td><strong>Email<td><strong>Dept</td></tr>")
Response.Write("<table border = 1><tr><td><strong>StudentNo<td><strong>EmployeePPS<td><strong>GradYear<td><strong>Qualifications<td><strong>CourseTitle<td><strong>Institute</td></tr>")
else
Response.Write("<h4>No Matching Course details</h4>")
end if
%>
<tr>
<%
Do While NOT oRS.EOF 
  %>
     <tr>
     <td><%=oRS("EmployeePPS")%>
     <td><%=oRS("FirstName")%>
     <td><%=oRS("LastName")%>
     <td><%=oRS("DOB")%>
     <td><%=oRS("Address")%>
     <td><%=oRS("County")%>
     <td><%=oRS("Mobile")%>
     <td><%=oRS("Email")%>
     <td><%=oRS("Dept")%>
     <tr>
     <td><%=oRS2("StudentNo")%>
     <td><%=oRS2("EmployeePPS")%>
     <td><%=oRS2("GradYear")%>
     <td><%=oRS2("Qualifications")%>
     <td><%=oRS2("CourseTitle")%>
     <td><%=oRS2("Institute")%>
      <%oRS.MoveNext
      Loop %>
</table>
<%
oRS.Close
oConn.Close
Set oRS = Nothing
Set oConn = Nothing
%>
<p>
<a href="JavaScript:window.close()">Close</a>
</body>
</html>

Error Type:
ADODB.Recordset (0x800A0CC1)
Item cannot be found in the collection corresponding to the requested name or ordinal.
/Employee Stuff Test/listemployee.asp, line 31

What is present on Line 31 is present in the database

Basically what i am trying to do here is view the record of multiple tables based on EmployeePPS
I also have to be able to add to multiple tables in a dtabase from the one form as well as uupdate and delete multiple records from a database based on the Employee PPS

Beano · 22-04-2008 11:28pm

cyberwit wrote: »

<html>
<head>
<title>Course Search</title>
</head>
<body>
<&#37;
Set oConn = Server.CreateObject("ADODB.Connection")
sConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
              "Data Source=" & Server.MapPath("db\HR System.mdb") & ";" 
oConn.Open(sConnection)
'SELECT from the database
sSQL = "SELECT EmployeePPS, FirstName, LastName, DOB, Address, County, Mobile, Email, Dept from Employee where EmployeePPS='" & Request("EmployeePPS") &"'"
sSQL = "SELECT StudentNo, EmployeePPS, GradYear, Qualifications, CourseTitle, Institute FROM Education where EmployeePPS='" & Request("EmployeePPS") &"'"
set oRS = oConn.Execute(sSQL)
%>
<p><font face="Arial" size="6" color="#000080">Course - Employee Search Results</font></p>
<% 
if NOT oRS.EOF then
Response.Write("<table border = 1><tr><td><strong>EmployeePPS<td><strong>FirstName<td><strong>LastName<td><strong>DOB<td><strong>Address<td><strong>County<td><strong>Mobile<td><strong>Email<td><strong>Dept</td></tr>")
Response.Write("<table border = 1><tr><td><strong>StudentNo<td><strong>EmployeePPS<td><strong>GradYear<td><strong>Qualifications<td><strong>CourseTitle<td><strong>Institute</td></tr>")
else
Response.Write("<h4>No Matching Course details</h4>")
end if
%>
<tr>
<%
Do While NOT oRS.EOF 
  %>
     <tr>
     <td><%=oRS("EmployeePPS")%>
     <td><%=oRS("FirstName")%>
     <td><%=oRS("LastName")%>
     <td><%=oRS("DOB")%>
     <td><%=oRS("Address")%>
     <td><%=oRS("County")%>
     <td><%=oRS("Mobile")%>
     <td><%=oRS("Email")%>
     <td><%=oRS("Dept")%>
     <tr>
     <td><%=oRS2("StudentNo")%>
     <td><%=oRS2("EmployeePPS")%>
     <td><%=oRS2("GradYear")%>
     <td><%=oRS2("Qualifications")%>
     <td><%=oRS2("CourseTitle")%>
     <td><%=oRS2("Institute")%>
      <%oRS.MoveNext
      Loop %>
</table>
<%
oRS.Close
oConn.Close
Set oRS = Nothing
Set oConn = Nothing
%>
<p>
<a href="JavaScript:window.close()">Close</a>
</body>
</html>

Error Type:
ADODB.Recordset (0x800A0CC1)
Item cannot be found in the collection corresponding to the requested name or ordinal.
/Employee Stuff Test/listemployee.asp, line 31

What is present on Line 31 is present in the database

Basically what i am trying to do here is view the record of multiple tables based on EmployeePPS
I also have to be able to add to multiple tables in a dtabase from the one form as well as uupdate and delete multiple records from a database based on the Employee PPS

i think you have misunderstood what _RoSy_ said. THe first code example was very close to be right. You just needed to make the change _RoSy_ suggested. The second example looks like you butchered the first version without really understanding what you were doing.

This isnt homework is it?

cyberwit · 22-04-2008 11:49pm

<html>
<head>
<title>Course Search</title>
</head>
<body>
<%
Set oConn = Server.CreateObject("ADODB.Connection")
sConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
              "Data Source=" & Server.MapPath("db\HR System.mdb") & ";" 
oConn.Open(sConnection)
'SELECT from the database
sSQL = "SELECT EmployeePPS, FirstName, LastName, DOB, Address, County, Mobile, Email, Dept from Employee where EmployeePPS='" & Request("EmployeePPS") &"'"
sSQL2 = "SELECT StudentNo, EmployeePPS, GradYear, Qualifications, CourseTitle, Institute FROM Education where EmployeePPS='" & Request("EmployeePPS") &"'"
set oRS = oConn.Execute(sSQL)
set oRS2 = oConn.Execute(sSQL2)
%>
<p><font face="Arial" size="6" color="#000080">Course - Employee Search Results</font></p>
<% 
if NOT oRS.EOF then
Response.Write("<table border = 1><tr><td><strong>EmployeePPS<td><strong>FirstName<td><strong>LastName<td><strong>DOB<td><strong>Address<td><strong>County<td><strong>Mobile<td><strong>Email<td><strong>Dept</td></tr>")
if NOT oRS2.EOF then
Response.Write("<table border = 1><tr><td><strong>StudentNo<td><strong>EmployeePPS<td><strong>GradYear<td><strong>Qualifications<td><strong>CourseTitle<td><strong>Institute</td></tr>")
else
Response.Write("<h4>No Matching Course details</h4>")
end if
%>
<tr>
<%
Do While NOT oRS.EOF 
  %>
     <tr>
     <td><%=oRS("EmployeePPS")%>
     <td><%=oRS("FirstName")%>
     <td><%=oRS("LastName")%>
     <td><%=oRS("DOB")%>
     <td><%=oRS("Address")%>
     <td><%=oRS("County")%>
     <td><%=oRS("Mobile")%>
     <td><%=oRS("Email")%>
     <td><%=oRS("Dept")%>
 </table>
  <%oRS.MoveNext
      Loop %>
    
 <%
Do While NOT oRS.EOF 
  %> 
     <tr>
     <td><%=oRS2("StudentNo")%>
     <td><%=oRS2("EmployeePPS")%>
     <td><%=oRS2("GradYear")%>
     <td><%=oRS2("Qualifications")%>
     <td><%=oRS2("CourseTitle")%>
     <td><%=oRS2("Institute")%>
      <%oRS.MoveNext
      Loop %>
</table>
<%
oRS.Close
oRS2.Close
oConn.Close
Set oRS = Nothing
Set oRS2 = Nothing
Set oConn = Nothing
%>
<p>
<a href="JavaScript:window.close()">Close</a>
</body>
</html>

Error Type:
Microsoft VBScript compilation (0x800A03F6)
Expected 'End'
/Employee Stuff Test/listemployee.asp, line 64

Beano · 23-04-2008 12:15am

one of your if statements is missing its end if.

also if you think this will print out one table with employee data and one table with the student data then you're mistaken. write out the html this will generate and you will see what i mean.

Is this homework?

Ginger · 23-04-2008 10:08am

You are looping oRs twice.. change the second loop to oRS2

cyberwit · 24-04-2008 6:28pm

The following is some Login Code
when the user enter the webpage they are asked to enter there Username and Password however when i exit from the browser and go back in and run the webpage i am immediately redirected to work.html how do i use abandon session to prevent this

<%@ Language = "VBScript" %>
<%
Option Explicit

Dim cnnLogin 
Dim rstLogin
Dim strUsername, strPassword
Dim strSQL 

%>
<html>
<head><title>Login Page</title>
</head>
<body bgcolor="red">
<%

If Request.Form("action") <> "validate_login" Then
	%>
	<form action="login_db.asp" method="post">
	<input type="hidden" name="action" value="validate_login" />
	<table border="0">
		<tr>
			<td align="right">Username:</td>
			<td><input type="text" name="EmployeePPS" /></td>
		</tr>
		<tr>
			<td align="right">Password:</td>
			<td><input type="password" name="Password" /></td>
		</tr>
		<tr>
			<td align="right"></TD>
			<td><input type="submit" VALUE="Login" /></td>
		</tr>
	</table>
	</form>
	<% %>
Else
	strSQL = "SELECT * FROM Authorisation " _
		& "WHERE Username='" & Replace(Request.Form("Username"), "'", "''") & "' " _
		& "AND Password='" & Replace(Request.Form("Password"), "'", "''") & "';"

	Set cnnLogin = Server.CreateObject("ADODB.Connection")
	cnnLogin.Open("DRIVER={Microsoft Access Driver (*.mdb)};" _
		& "DBQ=" & Server.MapPath("db\HR System.mdb"))

	Set rstLogin = cnnLogin.Execute(strSQL)

	If Not rstLogin.EOF Then
		<%
		
        Response.Redirect work.html"
	    %>
	Else
		%>
		<p>
		<font size="4" face="arial,helvetica"><strong>
		Login Failed - Please verify Username and Password.
		</strong></font>
		</p>
		<p>
		<a href="login_db.asp">Try Again</a>
		</p>
		<%
		'Response.End
	End If

	' Clean Up
	rstLogin.Close
	Set rstLogin = Nothing
	cnnLogin.Close
	Set cnnLogin = Nothing
	
%>

</body>
</html>

Ginger · 24-04-2008 8:03pm

You can add a piece of Javascript on a BrowserExit that deletes the session.

You can also check if the previous page using a client referral is actually where its supposed to...

Is this a FYP or something?

Teh Russ · 25-04-2008 4:17am

The code doesn't make any use of sessions, so what you're seeing isn't a result of an authenticated session. The code's full of syntax errors, missing "End If"s and mismatched variable names... here's a corrected version:

<%@ Language = "VBScript" %>
<%
Option Explicit

Dim cnnLogin 
Dim rstLogin
Dim strUsername, strPassword
Dim strSQL 

%>
<html>
<head><title>Login Page</title>
</head>
<body bgcolor="red">
<%

If Request.Form("action") <> "validate_login" Then

%>
	<form action="login_db.asp" method="post">
	<input type="hidden" name="action" value="validate_login" />
	<table border="0">
		<tr>
			<td align="right">Username:</td>
			<td><input type="text" name="EmployeePPS" /></td>
		</tr>
		<tr>
			<td align="right">Password:</td>
			<td><input type="password" name="Password" /></td>
		</tr>
		<tr>
			<td align="right"></TD>
			<td><input type="submit" VALUE="Login" /></td>
		</tr>
	</table>
	</form>
<%
Else
	strSQL = "SELECT * FROM Authorisation " _
		& "WHERE Username='" & Replace(Request.Form("EmployeePPS"), "'", "''") & "' " _
		& "AND Password='" & Replace(Request.Form("Password"), "'", "''") & "';"

	Set cnnLogin = Server.CreateObject("ADODB.Connection")
	cnnLogin.Open("DRIVER={Microsoft Access Driver (*.mdb)};" _
		& "DBQ=" & Server.MapPath("db\HR System.mdb"))

	Set rstLogin = cnnLogin.Execute(strSQL)

	If Not rstLogin.EOF Then
             Response.Redirect "work.html"
	Else
%>
		<p>
		<font size="4" face="arial,helvetica"><strong>
		Login Failed - Please verify Username and Password.
		</strong></font>
		</p>
		<p>
		<a href="login_db.asp">Try Again</a>
		</p>
<%
	End If

	' Clean Up
	rstLogin.Close
	Set rstLogin = Nothing
	cnnLogin.Close
	Set cnnLogin = Nothing

End If
	
%>

</body>
</html>

Try that.

Now, could someone help me with that JSON Javascript thing I posed a week or two back?

Forsaken1 · 07-08-2008 1:24pm

Yes. Missing End if was the issue here...
Asp reports missing a endif as a loop error sometimes, when a loop follows the failed closed condition statement.

Eoin · 08-08-2008 11:18am

I would look at trying to seperate the HTML from the ASP a little - I don't think mixing them as much is very maintainable.

When I was working with ASP, I would put a result set into an array, so I could close the connection immediately, and then use the array in the rest of the page.

If not(rs.eof) Then arrRS = rs.GetRows()
...
if isArray(arrRS) Then
    For i = 0 to ubound(arrRS,2)

    Next
End If

Basically, I would have as much of the ASP at the top of the page as possible, and use more variables in the HTML.

ASP Code HELP

Comments