Home | | Internet & World Wide Web HOW TO PROGRAM | | Internet Programming | | Web Programming | Accessing a Database from an Active Server Page(ASP)

Chapter: Internet & World Wide Web HOW TO PROGRAM - Active Server Pages (ASP)

Study Material, Lecturing Notes, Assignment, Reference, Wiki description explanation, brief detail

Accessing a Database from an Active Server Page(ASP)

Active Server Pages can communicate with databases through ADO . ADO provides a uniform way for a program to connect with a variety of databases without having to deal with the specifics of those database systems.

Accessing a Database from an Active Server Page

 

Active Server Pages can communicate with databases through ADO . ADO provides a uniform way for a program to connect with a variety of databases without having to deal with the specifics of those database systems.



Web applications are typically three-tier distributed applications, consisting of a user interface, business logic and database access. The user interface in such an application is often created using XHTML, Dynamic HTML or XML. The user interface can contain ActiveX controls, client-side scripts and, in some cases, Java applets. XHTML is the pre-ferred mechanism for representing the user interface in systems where portability is a con-cern. Because most browsers support XHTML, designing the user interface to be accessed through a Web browser guarantees portability across all browser platforms. The user inter-face can communicate directly with the middle-tier business logic by using the networking provided automatically by the browser. The middle tier can then access the database to manipulate the data. All three tiers may reside on separate computers that are connected to a network or on a single machine.

 

In multi-tier architectures, Web servers are increasingly used to build the middle tier. They provide the business logic that manipulates data from databases and that communi-cates with client Web browsers. Active Server Pages, through ADO, can interact with pop-ular database systems. Developers do not need to be familiar with the specifics of each database system. Rather, developers use SQL-based queries, and ADO handles the spe-cifics of interacting with each database system through OLE DB.

 

Databases enhance applications by providing a data source that can be used to dynam-ically generate Web pages. Figure 25.15 (instantpage.asp) puts the power of Web page creation into the hands of individuals who are not familiar with XHTML or ASP. However, we may want only a certain subset of pre-approved users to be able to access instantpage.asp. To restrict access, we use password protection. [Note: The example presented here is IIS specific. PWS users should use the version in the Chapter 25 examples directory (on the CD-ROM that accompanies this book). Separate files are included on the CD for users running Personal Web Server.] Before executing this example, an ODBC System DSN for this database must be created. See the “Setting up a System Data Source Name” at www.deitel.com.

Fig. 25.22 (database.asp) is an ASP document used to connect to and query an Access database.

 

          <% @LANGUAGE = VBScript %>

 

          <%

          ' Fig. 25.22 : database.asp

          ' ASP document for interacting with the database

          Option Explicit

 

          Dim connection, loginData

 

          ' provide error handling code

          On Error Resume Next

          Session( "errorString" ) = ""

         

          Set connection = Server.CreateObject( "ADODB.Connection" )

          Call connection.Open( "login" )

          Call errorHandlerLog()

          ' create the record set

          Set loginData = Server.CreateObject( "ADODB.Recordset" )

          Call loginData.Open( Session( "query" ), connection )

          Set Session( "loginData" ) = loginData

 

          Call errorHandlerLog()

 

          Sub errorHandlerLog()

          If Err.Number <> 0 Then

          Dim errorString   

                  

          errorString = Session( "errorString" )        

          errorString = errorString & "<p class = " & _    

          Chr( 34 ) & "error" & Chr ( 34 ) & ">Error         (" _

          & Err.Number & ") in " & Err.Source & "<br     />" & _

          Err.Description & "</p><br />"      

          Session( "errorString" ) = errorString

          End If

          End Sub

          %>

Fig. 25.22  ASP document for connecting to a database

 

For simplicity, if an error occurs while the records are being retrieved, we choose to handle the error later in the script. Line 11 specifies that any error caused by a statement from this point onward is ignored, and control is transferred to the statement immediately following the statement that caused the error. Line 12 declares session variable error-String and assigns it an empty string as its value.

The Server object provides a method (CreateObject) to instantiate other objects (e.g., built-in objects, ActiveX components, etc.). Line 14 calls Server method Cre-ateObject to create an ADODB.Connection object and Sets it to reference con-nection. An ADODB.Connection object encapsulates the functionality necessary to connect to a data source. Line 15 calls method Open to open the database referenced by the specified ODBC System DSN (i.e., login).

 

Line 16 calls procedure errorHandlerLog to process any errors that might have occurred in the script. Lines 25–36 define procedure errorHandlerLog. When an error occurs in the script, Err object’s Number property contains an integer representing which VBScript error has occurred. Line 26 tests if an error has occurred. If True, lines 27–34 assign XHTML text containing the error number and a message to session variable

 

errorString.

 

Lines 19–20 Set reference loginData to an ADODB.Recordset object and call method Open to execute the query (passed by login.asp) against the database refer-enced by connection. Method Open is passed a string containing the SQL query and the ADODB.Connection object that connection references. When Open finishes executing, the ADODB.Recordset object referenced by loginData contains all records that match the SQL query and points to either the first record or end of file (EOF) if no records were found.

 

Line 21 Sets session variable loginData to variable loginData referencing the ADODB.Recordset object containing all records that matched the SQL query. Line 23 calls procedure errorHandlerLog for a second time. Note that in line 30 the error number and message are concatenated to variable errorString to ensure that error information is added to previous errors the script has encountered. In Fig. 25.26, we show a sample error.

 

Fig. 25.23 provides an Active Server Page named login.asp, which prompts the user for a login name and password. The login names and passwords are stored in the Access database opened in database.asp.

 

 

          <% @LANGUAGE = VBScript %>

 

 

          <%

          ' Fig. 25.23 : login.asp

          ' ASP document to login to instantpage.asp

          Option Explicit

 

          ' create the SQL query

          Session( "query" ) = "SELECT loginID FROM Users"

          Call Server.Execute( "database.asp" )

          %>

 

          <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"

          "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

 

          <html xmlns = "http://www.w3.org/1999/xhtml">

 

          <head>

          <title>Login Page</title>

         

          <style type = "text/css">

          table { text-align: center;

          font-size: 12pt;

          color: blue;

          font-size: 12pt;

          font-family: arial, sans-serif }

          .error { color: red }

          </style>

         

          </head>

         

          <body>

         

          <!-- #include virtual="/includes/header.shtml" -->

          <%

          If Session( "errorString" ) = "" Then

          ' if this is a return after a failed attempt,

          ' print an error

          If Session( "loginFailure" ) = True Then %>

                   <p class = "error">Login attempt failed,

                   please try again</p>

          <%    End If

          ' begin the form %>

          <p>Please select your name and enter

          your password to login:</p><br />

         

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

         

          <!-- format the form using a table -->

          <table border = "0">

          <tr>

          <td>Name:</td>

         

          <td>

          <select name = "loginID">

          <option value = "noSelection">

          Select your name</option>

         

          <%

          If Request.Cookies( "loginID" ) <> "" Then

          Call BuildReturning()

          Else

          Call BuildNewUser()

          End If

          %>

          </select>

          </td>

          </tr>

         

          <tr>

          <td>Password:</td>

          <td><input type = "password"

          name = "password" /></td>

          </tr>

         

          <tr>

          <td></td>

          <td align = "left">

          <input type = "submit" value = "Log Me In" />

          </td>

          </tr>

          </table>

          </form>

 

          <!-- #include virtual="/includes/footer.shtml" -->

          <%

          Else

          Call Response.Write( Session( "errorString" ) )

          End If

          %>

          </body>

          </html>

 

          <%

          ' builds the option items for loginIDs and writes

          ' selected for the loginID of the returning user

          Sub BuildReturning()

          Dim found, loginData

 

          Set loginData = Session( "loginData" )

          ' pull user names from the record set to populate the

          ' dropdown list

          found = False

 

          While Not loginData.EOF

          ' create this record's dropdown entry

          %>    <option

          <%    ' if we did not write selected for any option

          ' before

          If ( Not found ) Then

         

          ' if the current record's loginID is equal to

          ' the loginID cookie, then it is the loginID of

          ' the returning user, and thus we need to write

          ' selected for this option; in this        case we also

          ' need to signal that we have written selected

          ' for an option by setting found to    True.

          If Request.Cookies( "loginID" ) _    

          = loginData( "loginID" ) Then

          Call Response.Write( "selected =      " & _

          Chr( 34 ) & "selected" & Chr( 34 ) )

          found = True       

          End If

          End If

          %>    value = "<% =loginData( "loginID" ) %>">

                   <% =loginData( "loginID" ) %></option>

          <%    Call loginData.MoveNext()

          Wend

          End Sub

         

          ' builds the option items for loginIDs without writing

          ' selected for any loginID

          Sub BuildNewUser()

          Dim loginData

 

          Set loginData = Session( "loginData" )

 

          ' pull user names from the record set to populate the

          ' dropdown list

          While Not loginData.EOF

          ' create this record's dropdown entry

          %>    <option value = "<% =loginData( "loginID" ) %>">

                   <% =loginData( "loginID" ) %></option>

          <%    Call loginData.MoveNext()

          Wend

          End Sub

          %>



Fig. 25.23  ASP document that allows the user to log into a site

 

This example uses cookies to identify users. The user’s browser must have cookies enabled to run this example. If cookies are disabled, the browser will not permit the example to write a cookie to the client machine, and the example will not be able to identify the user properly. Cookies are enabled in Internet Explorer 5.5 by selecting Internet Options from the Tools menu, which displays the Internet Options dialog. Click the Security tab at the top of this dialog to view the current security settings. Select the Custom Level... button, scroll down and find Cookies, then click Enable for both cookie options.

 

The Active Server Page login.asp prompts the user for a login ID and a password while submitlogin.asp is responsible for validating the user’s login. Both submit-login.asp and login.asp use session variable loginFailure. If login is suc-cessful, loginFailure is set to False, and the client is transferred to instantpage.asp. If login is unsuccessful, the variable is set to True and the client is transferred back to login.asp. The page recognizes that there was an error in submit-login.asp and displays the error message, because login.asp has access to session variable loginFailure.

 

The loginID and password fields are stored in table Users inside an Access data-base named login.mdb. For this example to perform correctly, use for username and password: bug1, bug2, bug3 or bug4. Users select their loginID from a drop-down list populated from the Users table. Note that submitlogin.asp also accesses the data-base to verify login information.

 

The file submitlogin.asp writes a cookie (named loginID) to the client con-taining the user’s loginID string to recognize returning users and to have their loginID displayed as selected in the drop-down list. When the user returns, login.asp reads the cookie and selects the user’s login name from the drop-down list.

 

Line 9 assigns the SQL query that SELECTs all the loginIDs FROM the Users table to session variable query. We use this session variable in database.asp (Fig. 25.22) as a parameter in method Open to query the database for each login ID. Line 10 exe-cutes database.asp to retrieve the login IDs from the database.

Line 36 tests if session variable errorString has an empty string as its value. Ses-sion variable errorString will have an empty string as its value unless an error has occurred in database.asp. If this returns False, line 89 calls Response method Write to print the error message to the user.

Lines 39–86 are executed only if database.asp has not returned an error. Lines 39–41 determine whether or not the session variable loginFailure is True, indicating that submitlogin.asp has detected an invalid login. If True, a message is displayed informing the client that the login attempt failed and prompting for another login.

 

The select structure is included to build the drop-down list of loginIDs. Lines 57–58 write the first option that displays, “Select your name.” If no other option is marked as selected, this option is displayed when the page is loaded. The next options are the loginIDs retrieved from the database. If this is a returning user, we want to display the loginID as selected.

 

Line 61 requests the loginID cookie. If this is the user’s first visit, or if the cookie has expired, Cookie returns an empty string. [Note: It is possible for a cookie to store an empty string. If this is the case, Cookie returns the cookie contents, which is an empty string.] Otherwise, the user’s loginID is returned. Lines 61–65 call BuildReturning if loginID contains a login ID and call BuildNewUser. Both BuildReturning and BuildNewUser build the login ID options. However, BuildReturning selects the returning user’s login ID option while BuildNewUser does not.

 

BuildReturning’s While loop (lines 107–130) iterates through loginData’s records. Recall that loginData contains the loginID column (field) of the Users table from line 101 and points either to the first record or to EOF. Line 107 tests for the end of the record set, indicating that there are no further records. Line 129 increments the record set pointer to the next record.

 

Each iteration of the While loop builds an option item for the current record. Line 109 simply writes the opening of the option item. Next, we test whether this option needs to be selected with the If statement in lines 120–125. Note that once we have written selected for an option, there is no need to perform this check in further iter-ations—selected is written for only one option. The code that writes selected for an option is thus wrapped in another If statement (lines 112–126). Variable found is set to False before the loop, in line 105. Once selected is written for an option, found is assigned True. Line 112 prevents the code that writes selected for an option from being executed unnecessarily after an option is already selected. Lines 120–121 deter-mine whether the current record’s loginID field is equal to the value of the loginID cookie. If so, lines 122–124 write selected and set found to True.

 

Line 127 sets the value for the option to the current loginID. Finally, line 128 writes the display of this option as the current loginID.

 

Active Server Page submitlogin.asp (Fig. 25.24) takes the values passed to it by login.asp and checks the values against the Users table in the database. If a match is found, the user is redirected to instantpage.asp. If no match is found, the user is redi-rected back to login.asp. The user never sees or knows about submitlogin.asp because the page is pure scripting code (i.e., its entire contents are enclosed in scripting delimiters).

 

          <% @LANGUAGE = VBScript %>

 

          <% ' Fig. 25.24 : submitlogin.asp

          ' ASP document to check user's username and password

          Option Explicit

 

          ' test if a user name and a password were

          ' entered. If not, transfer back to the login page.

          If Request( "password" ) = "" Or _

          Request( "loginID" ) = "noSelection" Then

          Session( "loginFailure" ) = True

          Call Server.Transfer( "login.asp" )

          End If

 

          Dim connection, loginData

 

          ' create the SQL query

          Session( "query" ) = _

          "SELECT * FROM Users WHERE loginID = '" & _

          Request( "loginID" ) & "'"

 

          Call Server.Execute( "database.asp" )

          Set loginData = Session( "loginData" )

         

          If Request( "password" ) = loginData( "password" ) Then

 

          ' password is OK, adjust loginFailure

          Session( "loginFailure" ) = False

      ' write a cookie to recognize them the next time they

      ' go to login.asp

      Response.Cookies( "loginID" ) = Request( "loginID" )

 

      ' give it three days to expire

      Response.Cookies( "loginID" ).Expires = Date() + 3

 

      ' send them to instantpage.asp

      Call Server.Transfer( "instantpage.asp" )

      Else

      Session( "loginFailure" ) = True

      Call Server.Transfer( "login.asp" )

      End If

      %>

 



Fig. 25.24  ASP document that validates user login

 

Lines 9–13 check whether the form’s password field is empty or if the loginID field was submitted with the default value. If so, session variable loginFailure is set to True and the client is redirected back to login.asp.

 

Lines 18–20 select all the fields from the table. The WHERE clause in this SQL state-ment specifies a condition on which records are selected: Only the record(s) whose log-inID field has the same value as the form’s loginID field are selected. Also note that this SQL statement always finds a record because the form’s loginID values are retrieved from the UsersloginID field. For example, if loginID bug1 is selected, then ses-sion variable query contains

 

SELECT * FROM Users WHERE loginID = 'bug1'

 

Line 22 calls Server method Execute to execute database.asp to query the database for the login ID that the user has submitted. Line 23 sets reference loginData to session variable loginData set in database.asp containing the records that have matched our query.

 

Line 25 checks the password against the password from the record set. Note that the submitted loginID is a valid login ID that was selected from the drop-down list. Thus, we only need to check the password to validate a login. If correct, line 32 writes the form’s loginID value as a cookie named loginID.

Line 28 sets the value of session variable loginFailure to False because the password has been validated. Line 35 sets the expiration date of this cookie to the current date plus three days. If we do not set an expiration date for the cookie when we create it, it is treated as a session cookie (i.e., it is destroyed when the browser is closed). [Note: If an existing cookie’s content is updated, then the expiration date needs to be set again. Other-wise, the cookie is destroyed at the end of the session regardless of the expiration date it had before the update.] The cookie remains on the client’s machine until the cookie expires, at which time the browser deletes it.

Next, line 38 calls Server method Transfer to redirect the client to instantpage.asp. Otherwise, the session variable loginFailure is set to True, and the client is redirected back to login.asp (lines 40–41).

 

Study Material, Lecturing Notes, Assignment, Reference, Wiki description explanation, brief detail


Copyright © 2018-2020 BrainKart.com; All Rights Reserved. Developed by Therithal info, Chennai.