A348 Database/ADO

• Basics
• Exercise 1 - Access Database
• Exercise 2 - Defining a Data Source
• ASP Database Use
  • Exercise 3 - ADO/SQL
• User Data
• Exercise 4 - ADO/User Data
• JavaScript Exceptions
• SQL Update, Insert, and Delete

Basics

Databases are one key component of most Web systems and can easy be generically accessed from ASP using the ADO (ActiveX Data Objects) technology. We will examine the combination of ASP, Access databases, ADO, and SQL to produce database driven pages.

Databases

Relational databases from nearly any vendor can be manipulated using the SQL language, that includes Access and Microsoft SQL Server. On an IIS Web server (and many others) the ADO (ActiveX Data Objects) provide a simple means of connecting to an SQL compliant database, issuing SQL commands to create, delete, retrieve or modify parts of the database. Essentially anything that can be expressed in SQL can by performed by a server-side application (e.g. ASP).

ADO can provide a simple, generic means of database use but the nuances are very complex. The examples given here will generally work for our purposes but are not intended for every application.

Microsoft Access

Relational databases such as Access, Oracle, SQL Server are the primary means used of storing, maintaining, and retrieving data. Microsoft developed the technology called ADO (ActiveX Data Objects) that provides generic access to data, including Access, Lotus Notes, Excel, Word, etc. A sample Access database will be downloaded for use with the following examples.

Web Servers/Databases/ADO

Web applications that store persistent data implement a three-tier model consisting of:

1. User interface on the browser.
2. Logic on the Web server.
3. Data storage on data server.

Tier 2 relates to ASP and server scripts, gathering and presenting data to the user, and storing or retrieving the data. To simplify database use, Microsoft has developed ActiveX Data Objects (ADO) for any application that supports ActiveX. 

The following VBScript ASP program uses ADO to open the A348db database, send the SQL statement to the database engine to return all records from the Employee table, and print the LNAME field of the first record, which happens to be Smith.

<%@ Language=JScript%> <% rs = Server.CreateObject("ADODB.Recordset") rs.Open ("SELECT Fname, Lname FROM Employee;" , "Data Source=A348db;") %> <TABLE BORDER=1> <TR> <TD>First</TD> <TD>Last</TD> </TR> <TR> <TD><%= rs("Fname") %></TD> <TD><%= rs("Lname") %></TD> </TR> </TABLE>

Browser Output First Last John Smith

The key points of ADO illustrated are:

• rs = Server.CreateObject("ADODB.Recordset") - Creates rs as an ADO recordset object.
• rs.Open ("Select Fname, Lname from Employee;","Data Source=A348db;") - Executes the Open method on the rs object, returning the results from opening A348db and performing the SQL. The recordset object, rs, is populated with the records resulting from the Select. In this example, the recordset contains only the Fname and Lname fields for each record.
• rs("Fname")  - Returns the value of the FNAME field.

Common ADO methods

Each ASP language (JavaScript, VBScript, PerlScript) varies somewhat on the syntax but each implements the same ADO methods.

ADO object creation. JavaScript - rs = Server.CreateObject("ADODB.Recordset"); Perl - $rs = Win32::OLE->new('ADODB.Recordset'); VB - rs = Server.CreateObject("ADODB.Recordset") Open database and execute SQL. JavaScript - rs.Open ("SELECT * FROM Employee;", "Data Source=A348db;"); Perl -  rs->Open("SELECT * from Employee;","Data Source=A348db;") VB - rs.Open "SELECT * FROM Employee" , "Data Source=A348db;"  Move to the first record. JavaScript - rs.MoveFirst(); Perl - rs->MoveFirst() VB - rs.MoveFirst() Move to next record. JavaScript - rs.MoveNext(); Perl - rs->MoveNext() VB - rs.MoveNext() Test for end of file. JavaScript - rs.EOF Perl - rs->EOF VB - rs.EOF Test for beginning of file JavaScript - rs.BOF Perl - rs->BOF VB - rs.BOF Access current record fields JavaScript - rs("Lname") Perl - rs->Fields('LNAME')->value VB - rs("Lname") Close the recordset. JavaScript - rs.Close(); Perl - rs->Close() VB - rs.Close

Iteration

Databases generally consist of multiple records and iteration over the records allows each record to be accessed. The following example iterates over all employee records, generating a table of first and last names.
 

<%@ Language=JScript%> <% rs = Server.CreateObject("ADODB.Recordset"); rs.Open ("SELECT FNAME, LNAME FROM Employee;", "Data Source=A348db;"); %> <TABLE BORDER=1> <TR> <TD>First</TD> <TD>Last</TD> </TR> <% while (!rs.EOF ) { %> <TR> <TD><%= rs("Fname") %></TD> <TD><%= rs("Lname") %></TD> </TR> <% rs.MoveNext(); } rs.Close(); %> </TABLE>

Partial HTML listing <TABLE BORDER=1>   <TR>      <TD>First</TD>      <TD>Last</TD>   </TR>       <TR>          <TD>John</TD>          <TD>Smith</TD>       </TR>       <TR>           <TD>Franklin</TD>           <TD>Wong</TD>       </TR>

User Form Data

ASP scripts can interact with users using the same GET and POST methods previously examined for CGI. The following uses a form to collect user input, the last name of an employee, that is retrieved from the Employee table of the A348db database source.

GET - With use of parameters to Request, the Web server parses the HTTP GET data and returns the values of the specified parameters. If your application requires unparsed data from the form, you can access it by calling Request without any parameters.

The GET form with Last data is returned to the script and its value can be accessed by: Request("Last") which has the value Smith given the input to the form in the example below. Note in the ASP that when Last is undefined, all records of the table are retrieved and displayed. For Last=Smith the SQL statement generated will be:

SELECT * FROM Employee WHERE LNAME = 'Smith'

Search.htm <H1>Employee Search</H1> <form method = "GET" action="Search.Asp"> Employee Last Name <INPUT TYPE = "text"       name="Last" size="20"> </form> Search Form Results of Search

Search.Asp <%@ Language=JavaScript%>  <%  if (Request("Last") == "") // No data sent by browser         sql = "SELECT * FROM Employee";      else         sql = "SELECT * FROM Employee WHERE Lname = '" + Request("Last") + "'";      rs = Server.CreateObject("ADODB.Recordset");      rs.Open (sql, "Data Source=A348db;");  %>  <TABLE BORDER=1>      <TR>          <TH BGCOLOR="silver">First</TH>         <TH BGCOLOR="silver">Last</TH>      </TR>  <%       while (!rs.EOF) {  %>           <TR>   <TD> <%= rs("Fname") %></TD>  <TD> <%= rs("Lname") %></TD>          </TR>  <%              rs.MoveNext();  }        rs.Close();  %>  </TABLE>

JavaScript and Exceptions

JavaScript (JScript) is by default one of the scripting languages supported by ASP. The following example is identical to that immediately above, where user input is appended to the URL by the user or by the GET method. It is interesting to compare the two scripting languages, noting the close similarities. JavaScript runs on the client and server by default but cannot be used for CGIs while VBScript also runs on the client and server but requires using the IE browser to run VBScript specific functions and ActiveX.

In the following note the use of try and catch which perform exception handling in Java and JavaScript. When ADO methods encounter an error, such as attempting to read more records than there are in the record set, the method throws an exception. When the method call is enclosed by the try operation, the exception is caught by the nearest catch. 
 

SQL Update, Insert and Delete

SQL Select reads a database table, producing a set of records matching the selection criteria; Update, Insert, Delete and some other operations write or otherwise modify the table. To modify databases or other files through ASP scripts, IIS must have write access to the files. For Access databases, give the Internet Guest account IUSR_MACHINE Write permissions on the directory containing the .mdb file. The IUSR_MACHINE must have sufficient rights to the folder ASP or other Web server run program. A common error indicating security problems is: 

Operation must use an updateable query

Do the following to correct while logged in as an administrator:

1. Start | Settings | Control Panel | Administrative Tools | Computer Management | Local Users and Groups |
   Users | IUSR_MACHINE (i.e. the Internet Guest Account) | Right Click | Properties | Member Of | Administrators
2. Sometimes you may have to delete the .mdb database file from the directory and copy back to the directory.
3. Reboot machine.
4. In ASP, set access mode = 3 for write access to database.

Delete

The following example deletes an existing record from the Employee table only if a record with the SSN='666666666' is already in the table. 

<%@ Language=JScript%>
<%	
  conn = Server.CreateObject("ADODB.Connection");
  conn.Mode = 3;
  conn.Open ("DSN=A348db");
  conn.Execute("DELETE from Employee where SSN='666666666'");
  conn.Close();
%>

Note the ADO is a little more complicated, using an ADODB.Connection object in order to set the connection mode = 3 for write access.  The following example on insert provides a more complete perspective.

Update

The following example updates the salary of a record from the Employee table only if a record with the SSN='123456789' is already in the table. The lucky employee is given a 10% raise each time the script executes.

<%@ Language=JScript%>
<%	
  conn = Server.CreateObject("ADODB.Connection");
  conn.Mode = 3;
  conn.Open ("DSN=A348db");
  conn.Execute("UPDATE Employee SET Salary = Salary * 1.10 where SSN='123456789'");
  conn.Close();
%>

Insert

The following example inserts a new record into the Employee table only if a record with the same SSN is not already in the table. Only four of the record fields are inserted: SSN, FNAME, LNAME, DNO.

Note that running the script once inserts the record and displays "Record Inserted", running a second time displays "Name already in table". The SSN is the primary table key, attempting to insert a record with the same primary key as one already in the table violates the integrity of the database and is an error. Testing if(rs.EOF) ensures that when true the record can be safely inserted.

Note that

• rs = conn.Execute("SELECT * FROM Employee where SSN='666666666'");

returns the record set resulting from the SELECT.

<%@ Language=JScript%>
<%	
  conn = Server.CreateObject("ADODB.Connection");
  conn.Mode = 3;
  conn.Open ("DSN=A348db");
  rs = conn.Execute("SELECT * FROM Employee where SSN='666666666'");

  if (rs.EOF) {
    conn.Execute("Insert into Employee (SSN, FNAME, LNAME,DNO) values ('666666666', 'Frank','Smith','5')");
    Response.Write("Record Inserted");
  }
  else {
    Response.Write("Name already in table");
  }
  conn.Close();
%>