Programming In Active Server Page(ASP) - Module One - Part II

Started by sivaji, Jan 10, 2008, 04:46 PM

Previous topic - Next topic
Go Down Pages1

sivaji

Jan 10, 2008, 04:46 PM Last Edit: Jan 10, 2008, 04:50 PM by sivaji
Programming Stuffs in ASP - Technical Skills for INTERVIEW

Create a Guest Book

This lesson describes how to develop a guest book application. Guest books allow visitors to your site a chance to give you feedback. Information such as the visitor's name, e-mail address, and comments can be available to you.

Create the guest book database

You must first create an Access database called Guestbook.mdb and save it in the C:\Inetpub\Wwwroot\Tutorial directory. The database must have the fields with the properties described in the following table.
Field Name   Data Type and General Properties
ID              AutoNumber, Field Size=Long Integer, New Values=Increment, Indexed=Yes(No Duplicates)
TB1             Text, Field Size=255, Required=No, Allow Zero Length=Yes, Indexed=No
TB2             Text, Field Size=255, Required=No, Allow Zero Length=Yes, Indexed=No
TB3              Text, Field Size=255, Required=No, Allow Zero Length=Yes, Indexed=No
TB4             Text, Field Size=255, Required=No, Allow Zero Length=Yes, Indexed=No
MB1              Memo, Required=No, Allow Zero Length=Yes


After you create the database, you need to create a data source name (DSN) connection to the database so your ASP application can interact with it. The DSN must be created on the Web server that is running the ASP application. If you move the ASP application to a different Web server, you have to re-create the DSN on the new Web server. The following procedure describes how to create a DSN on Windows NT and Windows 2000.

         *  In the ODBC Data Source Administrator, select the ODBC icon.
         *  Select File DSN.
         *  Select Add, select Microsoft Access Driver, and click Next.
         *  Type in a descriptive name for your file DSN (Guestbook) and click Next.
         *  Click Finish, click Select, specify the location of the database file, and select OK.
         *  Click OK twice. After you specify the location of the database file, the ODBC Data Source Administrator creates a
             file DSN for it.

Now that you have created the database and the DSN, paste the following code into a file named Guestbook.asp and place it in the C:\Inetpub\Wwwroot\Tutorial directory.

<% @Language=VBScript %>
<html dir=ltr>
<head>
<TITLE>Guest Book</TITLE>
</head>
<body bgcolor="#FFFFFF" text="#000000">
<%
IF request.form ("Message")="True" THEN
strTB1=request.form("To")
strTB2=request.form("EMailAdd")
strTB3=request.form("CC")
strTB4=request.form("Subject")
strMB1=request.form("Memo")
IF strMB1 = "" THEN
iLenMB1=255
ELSE
iLenMB1 = Len(strMB1)
END IF
'Connects to the Access driver and Access database in the Inetpub
'directory where the database is saved
strProvider = "Driver={Microsoft Access Driver (*.mdb)};
   DBQ=C:\Inetpub\Wwwroot\Tutorial\guestbook.mdb;"
'Creates an instance of an Active Server component
set objConn = server.createobject("ADODB.Connection")
'Opens the connection to the data store
objConn.Open strProvider
'Instantiate Command object and use ActiveConnection property to
'attach connection to Command object
set cm = Server.CreateObject("ADODB.Command")
cm.ActiveConnection = objConn
'Define SQL query
cm.CommandText ="INSERT INTO Guestbook (TB1,TB2,TB3,TB4,MB1)
   VALUES (?,?,?,?,?)"
'Define query parameter configuration information for guestbook fields
set objparam=cm.createparameter(, 200, , 255, strTB1)
cm.parameters.append objparam
set objparam=cm.createparameter(, 200, , 255, strTB2)
cm.parameters.append objparam
set objparam=cm.createparameter(, 200, , 255, strTB3)
cm.parameters.append objparam
set objparam=cm.createparameter(, 200, , 255, strTB4)
cm.parameters.append objparam
set objparam=cm.createparameter(, 201, , iLenMB1, strMB1)
cm.parameters.append objparam
cm.execute
response.write("Thank you!")
ELSE%>
<h1>Guestbook</h1>
<!--Post information to Guestbook form -->
<form name=guestbook.asp  action="guestbook.asp"  method="POST">
<p>To</p>
<p><input type="Text" name="To"></p>
<p>Email Address</p>
<p><input type="Text" name="EmailAdd"></p>
<p> CC</p>
<p><input type="Text" name="CC"></p>
<p>Subject</p>
<p><input type="Text" name="Subject"></p>
<p>Message</p>
<p><textarea name="Memo" rows=6 cols=70></textarea></p>
<input  type="HIDDEN" name="Message" value="True">
<input type="submit" value="Submit information">
</form>
<%End if%>
</body>
</html>

View the database in a browser

Using a database to collect information left by visitors is a safe and simple way to make your Web site more useful. Once information is entered in a database, you can either open the database in the application in which it was originally created, or you can use a Web page containing another script to view and edit the data. The following is a script that allows visitors to view and edit your database. To learn more about how to limit access to resources on your site, see "NTFS Security, Part 1: Implementing NTFS Standard Permissions on Your Web Site,"

<% @Language=VBScript %>
<html dir=ltr>
<head>
<title>View Guest Book</title>
</head>
<body bgcolor="#FFFFFF" text="#000000">
<%
'This section makes it possible for visitors to sort the data in the
   columns in ascending order.
if request.form("sort")<> "" THEN
StrSort=request.form("sort")
ELSE
StrSort="TB1 ASC"
END IF
strQuery="SELECT * FROM Guestbook ORDER BY " &StrSort
'Database path statement describing the driver to use and the
   path to the desired database.
strProvider = "Driver=Microsoft Access Driver (*.mdb); DBQ=C:\Inetpub\Wwwroot\Tutorial\guestbook.mdb;"
IF Request("ID") <> "" THEN
strIDNum=Request("ID")
'Creates an instance of an Active server component
set objConn = server.createobject("ADODB.Connection")
'Opens the connection to the data store
objConn.Open strProvider
'Instantiate Command object and use ActiveConnection property to
'attach connection to Command object
set cm = Server.CreateObject("ADODB.Command")
cm.ActiveConnection = objConn
'Define SQL query
cm.CommandText = "DELETE FROM Guestbook WHERE ID = " &strIDNum
cm.Execute
END IF
'Instantiate a Recordset object and open a recordset using
'the Open method
Set rst = Server.CreateObject("ADODB.recordset")
rst.Open strQuery, strProvider
%>
<h1>Guest Book</h1>
<form name=viewdb.asp action=viewdb.asp method=post>
<table border=1 cellspacing=3 cellpadding=3 rules=box>
<%
ON ERROR RESUME NEXT
IF rst.EOF THEN
Response.Write "There are no entries in the database."
ELSE%>
<tr>
<%
'Deletes rows from the database, this cannot be undone
Response.Write "<td width=200><center>Delete Record</center></td>"
FOR i = 1 to rst.Fields.Count -1
Response.Write "<td width=200><input name=sort value=" & rst(i).Name
   & " type=submit></td>"
NEXT
WHILE NOT rst.EOF %>
<tr>
<%
Response.Write "<td align=left valign=top bgcolor='#ffffff'>
   <a href=viewdb.asp?id=" & rst(0) & ">Delete</a></td>"
FOR i = 1 to rst.fields.count - 1
Response.Write "<td align=left valign=top bgcolor='#ffffff'>" & rst(i)
   &"</td>"
NEXT
rst.MoveNext
WEND
END IF
%>
</table>
</form>
</body>
</html>

Display an Excel Spreadsheet in ASP

This lesson demonstrates a technique that can be used to display a Microsoft Excel spreadsheet in a Web page. There are several standard server components supplied with IIS. One of these is the ActiveX® Data Objects (ADO) component, which is used as a connection mechanism to provide access to data. In the database lesson earlier, we used ADO. We use it again here to show you how to view and edit a spreadsheet with a Web browser.

To prepare your Excel spreadsheet for display in an Active Server Page:
        *   Create a spreadsheet using either Excel 98 or Excel 2000 and save it as ASPTOC.xls in the C:\Inetpub\Wwwroot\
             directory.
             Note: Do not include any special formatting or column labels when creating the spreadsheet.
        *   Highlight the rows and columns on the spreadsheet that you want displayed in the Web page.
        *   On the Insert menu, choose Name, and select Define.
        *   If there are any names listed, select them and select Delete.
        *   Type a name for the workbook, select Add, and select OK.

To display the spreadsheet in a Web page, you must create a DSN for the spreadsheet. The process is the same as it was for the database lesson earlier in this module. The only difference is that you must select the {Microsoft Excel Driver (*.xls)} option for the DSN.

Once you have created the spreadsheet and named the table in the spreadsheet, and also created a DSN for the spreadsheet, you are ready to create the page that displays the contents. Paste the following code into a new file and name it ASPTOC.asp. Save it in the C:\Inetpub\Wwwroot\Tutorial directory and view the page in your browser.

<%@Language=VBScript %>
<html>
<head>
<title> Displaying An Excel Spreadsheet in an Web Page </title>
</head>
<body bgcolor="#FFFFFF" text="#000000" >
<h1>ASP Table of Contents</h1>
<%
'Creates an instance of an Active Server Component
Set oConn = Server.CreateObject("ADODB.Connection")
'Connects to the Excel driver and the Excel spreadsheet
'in the directory where the spreadsheet was saved
strConn = "Driver={Microsoft Excel Driver (*.xls)}; DBQ=C:\Inetpub\Wwwroot\Tutorial\ASPTOC.xls;"
'Opens the connection to the data store
oConn.Open strConn
'Selects the records from the Excel spreadsheet
strCmd = "SELECT * from `ASPTOC`"
Set oRS = Server.CreateObject("ADODB.Recordset")
'Opens the recordset
oRS.Open strCmd, oConn
'Prints the cells and rows in the table
Response.Write "<table border=1><tr><td>"
'Gets records in spreadsheet as a string and prints them in the table
Response.Write oRS.GetString (, , "</tr><td>", "</td></tr><tr><td>",
   NBSPACE)
%>
</body>
</html>
Am now @ Chennai
Go Up Pages1