Connecting to Oracle using Forms

Inputting a value

Getting a value from the user and submitting it to a database requires the use of a form.

You therefore need two pages, a "front" page with the form to get the user input and a "back" page with the ASP code, to connect to the database.

This example also makes use of a function query2table. This formats the results from the database into a html table to make the results look better. Since it is a useful function that could be used in further ASP pages, it has been saved to a library file: ASPLib.asp

Warning!

These examples were put together years ago, long before SQL-Injection was heard of, or understood. Allowing the user to type the value in the fields is not recommended, since it leaves it open to SQL-Injection and other nasties!

It is better practice to allow the user to pick a value from a list instead, see this example instead, which uses Access, but the concepts are equally applicable to Oracle.

html Form

<html>
<head>
<title>Forms and a Database</title>
</head>

<body>
<h2>Connecting to Oracle using a Form</h2>
<form action="employeeList.asp" method="post">
<p>An example form to enter search for employees from a particular department.</p>
<P>Enter department number (10, 20, 30, 40, 90)
<INPUT TYPE="text" NAME="deptno"> </p>
<p><input type="submit" value="Search Database"> </p>
</form>
</body>
</html>

ASP Code

<html>
<head>
<title>Employee List</title>
</head>
<body>

<h2><font color= "blue">Employee List</font></h2>

<!--#include file="_lib\ASPLib.asp"-->

<% SET myConn=SERVER.CreateObject("adodb.connection")
myConn.Open "Provider=MSDAORA; Data Source=ora12c;" & _
      "User ID=MyOracleUsername;" & "Password=MyOraclePassword"

' request gets the value of the deptno variable
' This is what you should not do and makes the website vunerable to SQL Injection attacks:
SQLStr="SELECT * from Emp WHERE deptno=" & request("deptno")

' see the string generated
'(Note, you should only show this when testing a website)
response.write ("'" & SQLStr &"'")
CALL query2table(SQLStr,myConn)
myConn.Close %>

</body>
</html

ASP Include File

<% SUB query2table(inputquery, conntemp)
DIM rstemp
SET rstemp=conntemp.execute(inputquery)
howmanyfields=rstemp.fields.count -1%>

<table border=2><tr>
<% 'Put Headings On The table of Field Names
for i=0 to howmanyfields %>
<td><B><%=rstemp(i).name%></B></td>
<% NEXT %>
</tr>
<% ' Now lets print the records
DO WHILE NOT rstemp.EOF %>
<tr>
<% FOR i=0 TO howmanyfields
thisvalue=rstemp(i)

' hardcode a space if value blank
IF ISNULL(thisvalue) THEN
thisvalue=" "
END IF%> <td VALINE=TOP><%=thisvalue %></td>
<% NEXT %>
</tr>

<%rstemp.MoveNext
LOOP %>
</table>

<% ' close the connections
rstemp.Close
SET rstemp=nothing
END SUB %>