When you think ASP, think...
Recent Articles
All Articles
ASP.NET Articles
ASPFAQs.com
Message Board
Related Web Technologies
User Tips!
Coding Tips

Sections:
Sample Chapters
Commonly Asked Message Board Questions
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Security
Stump the SQL Guru!
XML Info
Information:
Feedback
Author an Article
ASP ASP.NET ASP FAQs Message Board Feedback
Print this page.
Published: Saturday, May 01, 1999

Creating Dynamic Websites
By Todd Bailey


Active Server Pages allows a user to connect to a data source, retrieve data, and then manipulate that data. This unlocks some great potential when building a web site. The entire site could be totally data driven and be updated from a web browser anywhere and anytime. No more will you have to fool with HTML, ftp, or Front Page Extensions. If you do the proper planning, then you can build a very robust web site that is easily maintained.

- continued -

The Connecting To a Database article was very good in giving the basics of the ADO Connection object. Just keep in mind that you DO NOT have to use a database when retrieving data. The great thing about ADO is that you can write or obtain a provider for any type of data source; whether email, text files, excel files, or csv files. You can learn more about ADO and OLE DB at Microsoft's Data Website.

After you connect to the database, you need to retrieve the data into a recordset. Again, ADO gives us an easy way of doing this. You create the recordset object and then open that object with a SQL command. You may need to brush up on you SQL commands, but a good tool to use is the Access Query Builder. Visual Interdev also includes a Query builder and Stored Procedure Editor. These tools allow you to see the SQL in a graphical interface. (See Figure 1 and Figure 2.)

The first thing to do is determine what data you want to retrieve and what table or tables that data is coming from. I'm going to base the code in this article on the table information in Figure 3. As you can see, we have a fairly simple relational database. Keep in mind that you want to make sure that you have a good database design. There are several articles on the site about Database Design that you may want to read!

The first thing that we want to do is retrieve a list of Members' Names and Email. We need to have a Connection to the database and an open recordset with the information that we want to display. This code is assuming that you have included that adovbs.inc file at the top of your ASP file. (More information available on ADOVBS.inc.) Since this is a read only, we are going to be using Client Cursors (the cursor is controlled by the web server instead of the database) and we're opening it as a static snapshot.

The Datebase connection:
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.CursorLocation = adUseClient
Conn.Open DSN
SQL = "SELECT MemberID, FName, LName, Email FROM Members"
Set rs = Server.CreateObject("ADODB.RecordSet")
Rs.Open SQL, Conn, adOpenStatic, adLockReadOnly

What you do next really depends on how you want to display the information. If you just want to list it out, you could do something like the following.

Response.Write "<Table width=400>"
Response.Write "<TR>"
Response.Write "<TD><b>Info</b></TD><TD><b>First Name</b></TD>"
Response.Write "<TD><b>Last Name</b></TD><TD><b>Email</b></TD>"
Response.Write "</TR>"
Do While Not rs.EOF
   Response.Write "<TR>"
        Response.Write "<TD><a href=""MemberInfo.asp?MemberID=")
        Response.Write rs("MemberID") & """>Info</a></td>"
   Response.Write "<TD>" & rs("Fname") & "</TD><TD>"
   Response.Write rs("Lname") & "</TD>"
   Response.Write "<TD><a  href=""mailto:" & rs("Email") & """>"
   Response.Write rs("Email") & "</a></TD>"
   Response.Write "</TR>"
Rs.MoveNext
Loop
Response.Write "</Table>"

As you can see, it was fairly simple to display all the members and their email addresses with a mailto link and a link to a Details page. If you have some basic HTML skills, along with ASP and ADO, you can quickly create a dynamic listing of you site's members. But what if you want to make sure that they are members before letting them view this list? We can create a login page that will verify their login and then set a session variable or a cookie to allow them to view the member information. We can create a form and pass their UserName and Password to ASP for processing. This can be done easily with one asp page. Remember that you don't have to create a lot of ASP pages when you can pass information along in the Request.Form or Request.Querystring objects.

Our Login Page has two purposes.
1. It will check the request object and if no action is found it will display the login form.
2. If information is found, then it will test that information to see if it is correct and then pass them on to the member's page.

The Form:
<form action="default.asp" method=post name="frmMain">
<input type=hidden name="txtAction" value="login">
<table align=middle>
        <tr>
                <td>Username:&nbsp;</td>
                <td><input type=text name="txtUserName"></td>
        </tr>
        <tr>
                <td>Password:&nbsp;</td>
                <td><input type=password name="txtPassword"></td>
        </tr>
        <tr><td colspan=2>&nbsp;</td></tr>
        <tr><td colspan=2 align=center>
                <input type=submit value="Login">
        </td>
        </tr>
</table>
</form>

The Database check:
<%
If Request("txtAction") = "login" Then
        Set conn = Server.CreateObject ("ADODB.Connection")
        Conn.CursorLocation = adUseClient
        Conn.Open DSN
        Set rs = Server.CreateObject("ADODB.RecordSet")
        sql = "SELECT MemberID FROM Members " & _
              "WHERE UserName = '" & Request("txtUserName") & "' " & _
              "AND Password = '" & Request("txtPassword") & "'"
        rs.Open sql, Conn, adOpenStatic, adLockReadOnly
        If rs.RecordCount = 1 Then
                Session("Member") = "Yes"
                rs.Close
                conn.Close
                Set rs = Nothing
                Set conn = Nothing
                Response.Redirect "MemberListing.asp"
        End If
End If
%>

Using ASP and a database, we've created a simple website that allows us to dynamically view members and their information. As you can see, what we've accomplished here is not really difficult. We created a listing of our members and secured it with a login. Of course, if we really wanted this information to be secure, we would need to host these pages on a SSL website.

This article shows you how to retrieve information from that database and display it on the browser. The next thing that would be needed is to give users the ability to sign up and store that information in the database. I'll concentrate on that in the next article. I hope that this has been informative. If you have any questions, please be sure to post them in the ASP or Database forums at ASPMessageboard.com.

Be sure to get the source code and sample database for this article.

Note: There are many different ways of connecting to recordsets. Everything depends on what you want to do, whether dynamic SQL or Stored Procedures. I concentrated on dynamic SQL in this article. There is a wonderful artice on 4Guys that might help, it's called Recordset Cursors: Choose the Right Cursor for the Right Job


Attachments:

  • Source code to this article, in zip format


    Todd Bailey is a Senior Programmer at Apps Communications, Inc. He has been programming with ASP and doing Internet / Intranet Web Development for 3 years. Prior to that, he worked as a Networking Engineer. Todd has worked on many different types of Internet and Intranet projects from simple infomation lists, ECommerce sites to very complex Customer Information Systems. He's mainly worked with ASP, DHTML, VB Components, MTS, SQL Server, DB2 and Oracle. When not programming, he likes to watch Sci-Fi movies, hunt, fish, and go camping. And of course, like all the other internet junkies, he likes to research the latest technologies.


  • ASP.NET [1.x] [2.0] | ASPMessageboard.com | ASPFAQs.com | Advertise | Feedback | Author an Article