Source code

ASP Source Code:
<%@ Language = VBScript %>
<%option explicit%>
<html>

<head>
<title>SQL Example</title>
</head>
<!-- #include virtual="/header.html" -->

<body bgcolor="#FFFFFF">

<center><h1>Using a form to limit query</h1></center>

<p>
This program demonstrates the use of Active Server Pages to embed SQL 
queries in an HTML page. It presents the user with a form where they can
enter search text. They can then choose to search based on first name or
last name. Take a look at the source
<a href="source.asp?file=<%= right(Request.ServerVariables("PATH_INFO"), len(Request.ServerVariables("PATH_INFO"))-instrrev(Request.ServerVariables("PATH_INFO"), "/")) %>">source</a>
to get a better idea of what's going on behind the schenes.
</p>

<form method=post action="example2.asp">

<p align="center">

Enter Employee's Name:
<input type=text name="search" maxlength="28" size="28" ><BR>

Select Inquiry Type:   
First Name<input type=radio name="type" value="first" checked>
Last Name <input type=radio name="type" value="last"><BR>

<input type=submit value="Search">

</p>

</form>

<hr>

<center>
<table border=2><tr><th>FIRST_NAME</th><th>LAST_NAME</th></tr>

<%

dim sql, search, oConn, oRs

' The first step in querying the data is to retrieve the information that was
' entered into the form and see if it was null. If so (and it is probably the 
' result of the page having been loaded and not submitted) then we just forego 
' the query.  Otherwise we use the information from the form to generate a result 
' set. The area following the while statement is executed once for every row in 
' the result set.

' We build the SQL statement with the text that was entered in the form. 
' The form action tells the client to pass the information to this page so this
' page is opened once to present the form and again when the form is submitted.
' On the initial open the form information is of course blank but on subsequent
' submissions it should contain the choices from the user.

If Cstr(Request.form("type")) = "first" Then
  sql = "select FIRST_NAME, LAST_NAME from EMPLOYEE_MASTER where FIRST_NAME like '%" & _
  Request.form("search") & "%';"
Else
  sql = "select FIRST_NAME, LAST_NAME from EMPLOYEE_MASTER where LAST_NAME like '%" & _
  Request.form("search") & "%';"
End If 

If not IsNull(Request.form("search")) and Request.form("search") > " " Then

  search = UCase(Cstr(Request.form("search")))
  SET oConn = Server.CreateObject("ADODB.Connection")
  oConn.Open "DSN=qcd;UID=qsql;Password=pa$$w0rd;"

  set oRs = oConn.Execute(sql)

  WHILE NOT oRs.EOF 
    response.write "<tr>"

    If Request.form("type")="first" then
      response.write "<td bgcolor='EOEOEO'>"
    else 
     response.write "<td>"
    end if

    response.write oRs.Fields("FIRST_NAME").Value 
    response.write "</td>"

    If Request.form("type")="last" then
      response.write "<td bgcolor='EOEOEO'>"
    else
      response.write "<td>"
    end if

    response.write oRs.Fields("LAST_NAME").Value
    response.write "</td></tr>"
    oRs.MoveNext
  WEND
  oConn.Close
  set oConn = Nothing
End If 
%>

</table>

</center>

<!-- #include virtual="/footer.html" -->
</body>
</html>
© 1999-2019, Qantel Technologies, Inc.
