by Ajoy Krishnamoorthy
I become nervous when people talk about servers, server-side
scripts or active technologies. It so happens sometimes that the
term gives the shock and not the stuff inside. Surprising to me,
Active Server Pages (ASP) does not scare me in anyway rather it
helped me in building an interactive and dynamic weblication (i mean
a web based application). People dont need a high end programming
expertise to start their ASP programming. It needs a modest
programming skills, the argument which you will agree at the end of
this discussion incase if you dont agree now.
Let us get on
with the businesss and start building our ASP application.
I
assume you know HTML(Not a big deal to learn). In anycase you can
cut and paste the samples given here and change the titles and
labels wherever required.
Objective:
To build a form to get information like
name, e-mail, comments/feedback etc. from the vistor to your site.
And store all the information in a database for our reference. This
is something which most of us prefer to have in our site so that the
site could be updated based on the user's feedback and suggestions.
Requirements:
Build a database with a table to store
the details. Create Fields like Name, E-Mail, Comments, Product
Choice etc in the table. Create another table with ProductCode and
Product title as the fields. We are going to use this product table
to populate the select box in the HTML form dynamically.
I
am using Ms-Access to build my database.My database file is
swynk1.mdb. And the tables i have created are Userinfo and Prodinfo.
Fig:Access Screen
Our next step is
to create a directory in the "wwwroot" directory of IIS server. We
need to give read and execute permission to this directory. Then all
our files (.htm,.asp,.mdb,.gif etc) are to be copied to this
directory.
Once the files are copied we need to create a
Data Source Name.
Open the Control panel.Select the ODBC 32
icon from Control Panel and click System DSN Tab. We need to select
the Sysem DSN because it is available to anyone using that Server.
Data sources that you use with Web server hence need to be system
data source. Select add and choose Microsoft Access Drvier from the
list since we are using a Access database. Then we need to give a
DSN name and has to select the Database file. I have given "swynk1"
as the DSN name. This name you will use in the ASP file to get
connected to the database.
After the DSN is created now it
is time to start coding.
First let us create a HTML Form to
get the information from the user.
Going by our objective we
need to create two text boxes to collect name and email information,
one select box to get user's product choice and one another textarea
box to get user's comments/feedback. Apart from these we need to
create two buttons to submit and clear the contents.
The
below listing gives the HTML for the Form:
Listing
1:userform.asp
<%@ Language=VBScript %>
&<lt;HTML>
<HEAD>
<TITLE> USER INFORMATION FORM </TITLE>
</HEAD>
<BODY>
<H1><font color="red"> WELCOME TO TEST INC.</font> </H1>
<FONT FACE="VERDANA,ARIAL" COLOR="BLUE">Thanks for taking time to fill in the feedback form</FONT>
<br><br>
<%set dbconn= Server.CreateObject("ADODB.Connection")
dbconn.Open("swynk1")
sqlstr="select ProductCode from Prodinfo"
set rs=dbconn.Execute(sqlstr)%>
<form action="submit.asp" method="post" name="fomr1">
<table>
<tr>
<td>
Name:<br><br></td><td><INPUT type="text" name=text1><br><br></td></tr>
<tr>
<td>E-Mail Address :<br><br></td><td><INPUT type="text" name=text2><br><br><br></td></tr>
<tr>
<td>Which Product you liked the most?<br><br></td><td><SELECT name=select1>
<%rs.MoveFirst
do while not rs.EOF%>
<OPTION value='<%=rs("ProductCode")%>'><%=rs("ProductCode")%></OPTION>
<%rs.MoveNext
Loop%>
</SELECT><br><br></td></tr>
<tr>
<td>Comments/Suggestions:<br><br></td>
<td><TEXTAREA rows=3 cols=30 name=textarea1>
</TEXTAREA><br><br></td></tr>
<tr>
<td colspan=2 align=center><INPUT type="submit" value="Submit" name=submit1>
<INPUT type="reset" value="Reset" name=reset1><br><br></td></tr>
</table>
</form>
</BODY>
</HTML>
This is how it looks in a browser.
Fig:User Form in Browser
I could hear you aksing why this file has a .asp extension.
If you have a second look at the code you can very well notice
scripts enclosed "<%" and "%>" delimiters. As I told you
earlier we are dynamically populating the select box from the
database. This is accomplished using ASP components. So we need to
save this file with a .asp extension.
Let us go in depth and
understand the ASP part of the above script.
The very first
line in the listing is <%@ Language=VBScript %>. This
statement will inform the server that "VBScript" is used as a
default scripting language.
Then comes our actual code to
talk to the database and fetch the data.
Here we will be
using Database Access Component. Server.CreateObject will create an
instance of the server component. In this case it will instantiate
the Data Access Component. <%set dbconn=
Server.CreateObject("ADODB.Connection")%>. This object will let
us get connected to the database. Once the object is instantiated
now we need to open the database that we will use in our
application. It is what is done in the next statement
<%dbconn.Open("swynk1")%>. Here we are using Open method and
supplying the DSN name as the argument.
Once we are
connected to the database we can execute query and fetch the
required results. In our example we need to select all the Product
Code from the Prodinfo Table. So the SQL statement will be
sqlstr="select ProductCode from Prodinfo". The statement is stored
in a variable "sqlstr" as a string.
Then, we use the Execute
method of the Data Access Component and pass the variable to it.
Alternatively you can also directly pass the Select statement to the
Execute method. We are storing the erturn results in a recordset rs.
All these are done in our next line of code.
<%set
rs=dbconn.Execute(sqlstr)%>
Now we have the results in
hand. And we need to assign them to the select box.
We
create a do while loop which checks for the recordset's End of file.
We assing the values to the Option tag using the syntax
<%=rs("ProductCode">. This is actually
<%=recordset("Fieldname")%>. Take note of the equal sign
before the recordset. If you dont specify the equal sign the value
will not get assigned. Then we move through the recordset one by one
using rs.Movenext method.
This succesfully completes our job
of dynamically populating the select box.
But now once the
data are entered and the submit button is clicked, the information
are transferred to the Server and in turn has to be copied to the
table Userinfo.
So we specify "submit.asp" as our server
file in the
IE 7
Firefox 2.0