<% @LANGUAGE = VBScript %>
<% ' Listing Retrieving Information about groups/projects from sem6_project_db 1998.
Per Christiansson 25.10.2000.
Option Explicit
' Explicit means that all variables are declared with Dim, Proivate, Public or
'Redim statements. Should be placed before other statements.
Response.Expires = 0
' minutes until the web page content is deleted on the server.
'It is also possible to give absolute date for expiration
Dim strFirstname, strLastname, strMessage
Dim objConn, objRS, strQuery
Dim strConnection
'Check if data have been sent from the client.
'First time it has not and the ELSE part is executed
If Request.ServerVariables("CONTENT_LENGTH") <> 0 Then
' Open Database
Set objConn = Server.CreateObject("ADODB.Connection")
strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\inetpub\wwwroot\asp_example\c2xx.mdb;"
objConn.Open strConnection
' Retrieving Form Data from Post command body
strFirstname = Trim(Request.Form("firstname"))
strLastname = Trim(Request.Form("lastname"))
strMessage = Trim(Request.Form("message"))
strMessage = Replace(strMessage, vbcrlf, "<BR>" & vbcrlf)
' Trim removes spaces from start and end of string
' If empty input strings given, replase with % ('wild' character)
if strFirstname= "" then strFirstname = "%"
if strLastname= "" then strLastname = "%"
' Construct the SQL query string
strQuery = "SELECT enavn, fnavn, projgroup.projid, projtype.projname "
strQuery = strQuery & " FROM person, projgroup, groupperson, projtype "
strQuery = strQuery & " WHERE projtype.projid = projgroup.projid "
strQuery = strQuery & " AND groupperson.groupid = projgroup.groupid "
strQuery = strQuery & " AND person.personid = groupperson.personid "
strQuery = strQuery & " AND person.fnavn LIKE "
strQuery = strQuery & "'" & strFirstname &"'"
strQuery = strQuery & " AND person.enavn LIKE "
strQuery = strQuery & "'" & strLastname &"'"
strQuery = strQuery & " ORDER BY projgroup.groupid ASC, person.enavn ASC, person.fnavn ASC;"
' Do the database SQL search
Set objRS = objConn.Execute(strQuery)
%>
<HTML>
<BODY bgcolor="ffffff">
You have input the folowing data
<br>
<hr>
<B>Search identification:</B> <%= strMessage %><br>
<B>Firstname:</B> <%= strFirstname %><br>
<B>Second name:</B> <%= strLastname %><br>
<hr>
<table>
<tr>
<td width='10%'>
</td>
<td>
<h2>Look for a specific persons by name and list by name, group belonging and project:
</h2><BR><BR>
<table border="1">
<tr>
<td>
<B>Second name</B>
</td>
<td>
<B>First name</B>
</td>
<td>
<B>Project id.</B>
</td>
<td>
<B>Project name</B>
</td>
</tr>
<%
' if no data were found write message in the table output
if objRS.eof then
Response.write "<tr>"
Response.write "<td colspan='4'>"
Response.write "NO DATA FOUND"
Response.write "</td>"
Response.write "</tr>"
end if
' Read all records in the database response
While Not objRS.EOF
Response.Write "<tr>"
Response.Write "<td>"
Response.Write objRS("enavn") & " "
Response.Write "</td>"
Response.Write "<td>"
Response.Write objRS("fnavn") & " "
Response.Write "</td>"
Response.Write "<td>"
Response.Write objRS("projid") & " "
Response.Write "</td>"
Response.Write "<td>"
Response.Write objRS("projname")& "<BR>"
Response.Write "</td>"
Response.Write "</tr>"
objRS.MoveNext
Wend
Response.Write "</table>"
objRS.close
objConn.close
Set objRS = Nothing
Set objConn = Nothing
%>
</td>
<td width='10%'>
</td>
</tr>
</table>
<br><br><br>
<font size="-1">
Per Christiansson, 17.10.2000
</font>
</BODY bgcolor="ffffff">
</HTML>
<%
Else
%>
<HTML>
<BODY bgcolor="ffffff">
Please write first and second name to search for. Use <B>%</B> as wild characters:<BR>
<FORM ACTION="<%= Request.ServerVariables("SCRIPT_NAME") %>" METHOD="POST">
First name: <INPUT TYPE="Text" NAME="firstname"><BR>
Last name: <INPUT TYPE="Text" NAME="lastname"><BR>
Your Message: <TEXTAREA NAME="message">Identify the search here. </TEXTAREA>
<BR>
<INPUT TYPE="Submit" NAME="submit" VALUE="Submit Form">
</FORM>
<br><br><br><br><br>
<font size="-1">
Per Christiansson, 17.10.2000
</font>
</BODY></HTML>
<% End If %>