Active Server Pages Database Connectivity

1. Active Server PagesDatabase Connectivity

2. Generalized Data Access • ALL data structures that can be meaningfully used for ‘databases’ must support a common set of functions: • ADD data • CHANGE data • RETRIEVE data • Many who have noted the functional commonality of databases have envisioned a common access method and one set of commands to manipulate any database, regardless of vendor, underlying implementation, etc.

3. SQL • SQL was almost there! • The problem: SQL is not a complete language, and must be embedded in a host programming language to handle non-database aspects of programming • The interface between host language and SQL can be very ‘klugy’

4. ADO • ActiveX Data Objects (ADO) are Microsoft’s answer to universal data access - at least for all their languages • ADOs insulate the programmer from database implementation details, and allow you to use as much or as little SQL as you desire

5. ADO Basics • ADO is a programming interface that enables access and manipulation of data in a database. • You can move data from a server to a client, manipulate the data, and return updates to the server, with ADO's Remote Data Service (RDS) • ADO and RDS are automatically installed with Microsoft IIS • ADO can be accessed from within ASP pages

6. ADO Object Model • ADO is based on yet another object model. The two most important objects are: • CONNECTION • A connection must be established with the database before it can be accessed. The CONNECTION object stores all the details. • RECORDSET • The recordset ‘is’ or contains the actual data records. This object’s methods read from and write to the database

7. Accessing a Database • Create an ADO connection to a database • Open the database connection • Create an ADO recordset • Open the recordset • Extract the data you need from the recordset • Close the recordset and set to nothing • Close the connection and set to nothing

8. The ADO Connection Object • The ADO Connection Object is used to establish a database connection.  • Syntax: conn.method conn.property • Methods • Close: Closes a connection • Execute: Executes a query, statement, procedure or provider specific text • Open: Opens a connection • Properties • Mode: Sets or returns the provider access permission • Provider: Sets or returns the provider name • State: Returns a value describing if the connection is open or closed • Version: Returns the ADO version number • See this, for a complete reference to the Connection Object’s methods, properties and events.

9. Creating an ADO Connection • The CONNECTION object is created by the Server object. • You must create and open a connection before you can access the database • Creation is easy and standard! Dim objConn‘reserve space for the object Set objConn = Server.CreateObject (“ADODB.Connection”)

10. Opening the ADO Connection • To open a connection we must specify the type of the database and its location • This information is supplied to the connection object in a ‘connection string’ objConn.Open “<connection string goes here>” • To build a connection string you can either • Use a DSN-less connection, or • Create a DSN (Data Source Name)

11. Creating a DSN-less Connection • If you have a database called prod.mdb located in the“data” folder, you can connect to the database with the following ASP code: <%setconn = Server.CreateObject(“ADODB.Connection”) conn.Provider = “Microsoft.Jet.OLEDB.4.0“conn.Open server.mappath(“data/prod.mdb”) %> • Note that, you have to specify the Microsoft Access database driver (Provider), and the physical path to the database on your computer.

12. DSN-less Connection (2) • Another example accessing an Excel database <%setconn = Server.CreateObject("ADODB.Connection") DSN = “Driver={Microsoft Excel Driver (*.xls)}; DriverID=22; DBQ=” & server.mappath(“data/prod.xls”)     ’ For an Access database: ‘ DSN = "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & server.mappath("data/test.mdb") conn.Open DSN %> • Examples: 1, 2, 3

13. Connecting using a DSN • A DSN (Data Source Name) is Microsoft’s name for a parameter file that contains information about a database. It is created using the ODBC 32 utility. • Once the DSN exists, use it as the connection string. For example, if you have an ODBC database with a DSN called “products”, you can connect to the database with the following ASP code: <% set conn = Server.CreateObject("ADODB.Connection") conn.Open “products" %>

14. Creating a System DSN • Here is how to create a connection to a MS Access Database:  • Open the ODBC icon in your Control Panel. • Choose the System DSN tab. • Click on Add in the System DSN tab. • Select the Microsoft Access Driver. Click Finish. • In the next screen, click Select to locate the database. • Give the database a Data Source Name (DSN). • Click OK. • Note that this configuration has to be done on the computer where your web site is located. If you are running Personal Web Server (PWS) or Internet Information Server (IIS) on your own computer, the instructions above will work, but if your web site is located on a remote server, you have to have physical access to that server, or ask your web host to do this for you. 

15. The ADO RECORDSET Object • To be able to read database data, the data must first be loaded into a recordset. • The RECORDSET object can be viewed as a one-record-wide sliding window on the database The recordset ‘window’ moves up and down the database ‘page’ making one record visible at a time

16. The ADO RECORDSET Object • The Recordset Object is used to hold a set of records from a database table.  • Syntaxrs.method rs.property • Methods • AddNew: Creates a new record • Close: Closes a Recordset • Delete: Deletes the current record or group of records • MoveFirst: Moves to the first record • MoveLast: Moves to the last record • MoveNext: Moves to the next record • MovePrevious: Moves to the previous record • Open: Opens a Recordset • Update: Saves any changes

17. The ADO RECORDSET Object • Properties • BOF: Returns true if the current record is before the first record, otherwise it returns false • EOF: Returns true if the current record is after the last record, otherwise it returns false • Fields: Contains all of the field objects for the Recordset Object • RecordCount: Returns how many records there are in a Recordset object • Sort: Specifies a comma-separated list of field names the Recordset is sorted on • For a complete reference of the Recordset Object's methods, properties and events, Click Here.

18. Creating a RecordSet • The RECORDSET object must be created, and then opened before it can be accessed • Creating is simple and standard Dim rs‘reserve space for the object Set rs = Server.CreateObject (“ADODB.Recordset”)

19. Opening the RecordSet • To access the “Products” table inside the prod.mdb database: <% set conn = Server.CreateObject(“ADODB.Connection”) conn.Provider = "Microsoft.Jet.OLEDB.4.0" conn.Open server.mappath(“data/prod.mdb”) set rs = Server.CreateObject(“ADODB.recordset”) rs.Open “Products”, conn %> or rs.Open “Select * from Products”, conn or SQL = “Select * from Products” rs.Open SQL, conn

20. Using the ADO Connection Execute Method • Can also create and open a recordset and execute an SQL query by using the ADO Connection Execute Method • Limitation: The returned Recordset object is always a read-only, forward-only cursor. Of course, if the query’s purpose is mostly to update, delete, or insert, this may not be a problem. <% setconn = Server.CreateObject("ADODB.Connection") connString = "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & server.mappath("data/test.mdb") conn.Open connString SQL = “Select * from Products” Set rs = conn.Execute(SQL) ‘or even conn.Execute(SQL) is you don’t care to create a recordset %>

21. Extracting Data from the RecordSet • Once open, the RECORDSET is positioned at the first record in the recordset, and field level information is available by referencing the field names given in the ACCESS database: nameStr = rs (“Name”) • gets the Name field from the current data record and assigns it to the nameStr variable

22. RecordSet Navigation • Moving through the database is accomplished with the MoveNext method of the Recordset rs.MoveNext Database Start and End are detected by testing for: rs.EOF(end of file) rs.BOF(beginning of file)

23. Displaying Records • After a recordset is opened, data from the recordset can be displayed on an HTML page. See example1, and example2 <% Set conn = Server.CreateObject("ADODB.Connection") conn.Provider = "Microsoft.Jet.OLEDB.4.0" conn.Open server.mappath(“data/prod.mdb”) Set rs = Server.CreateObject("ADODB.recordset") rs.Open "Select * from Customers", conn %> <table border=“1” width=“100%”> <%do until rs.EOF%> <tr> <%for each x in rs.Fields%> <td><%Response.Write(x.value)%></td> <%next rs.MoveNext%> </tr> <%loop rs.close Set rs = Nothing conn.close Set conn = Nothing %> </table> Click for info on VBScript looping

24. Displaying Records – cont’d <% set conn=Server.CreateObject("ADODB.Connection") conn.Provider = "Microsoft.Jet.OLEDB.4.0" conn.Open “Toys” set rs = Server.CreateObject("ADODB.recordset") rs.Open "Select * from Customers", conn for each x in rs.fields response.write(x.name) response.write(" = ") response.write(x.value) next %>

25. Tidying up with ADO • When done with a connection and/or a recordset, you must: • Close the CONNECTION and RECORDSET • Delete those objects from memory

26. Closing the Connection and RecordSet • Close is accomplished with a method (of the same name) for each object • Object deletion is accomplished by setting the variable to ‘Nothing’ rs.Close‘close the recordset before its connection Set rs = Nothing‘deleting objects prevents “memory leaks” conn.Close Set conn = Nothing

27. Advanced ADO operations • Advanced Database Navigation • Writing to the database • Changing database records • Filtering database records (retrieving only part of the database based on conditions)

28. Types of RecordSets • When opening a Recordset the recordset type and the lock type may be specified. • Doing so, improves the efficiency of the task. • Types specify • updateable vs. non-updatable • scrollable vs not scrollable • dynamic or static • use keys or not

29. Lock Types • Lock types specify the type of record locking to apply to the Recordset (remember those lectures in database class on database integrity?) • Types include: • adLockReadOnly – Default; only for retrieving data. Indicates read-only records. You cannot alter the data. • adLockoptimistic – use if you will be doing add, edit, update in the database. Indicates optimistic locking, record by record. The provider uses optimistic locking, locking records only when you call the Update method.

30. Cursor Types • A database cursor is “a place in memory where records are placed after they are retrieved from the database. The cursor then allows you to access the records row-by-row so that you can display or manipulate them however you want.” (ASP for Dummies) • Types include: • adOpenStatic – After you get your results, you do not see changes that other users may make to the database • adOpenForwardOnly – Default; like static, but further constrained to moving forward one row at a time • adOpenDynamic – Records constantly updated to reflect changes by other users

31. RecordSet Open in Detail • The complete syntax for the Recordset.Open is: rs.Open [Source] [, ActiveConnection][, CursorType] [, Locktype] [, SourceType] • Source: Specifies a data source such as an SQL statement, a table name, or a stored procedure call. • ActiveConnection: a valid Connection object variable name • CursorType: value that determines the type of cursor that the provider should use when opening the Recordset. • LockType: The type of locks placed on records during editing • SourceType: value that indicates how the provider should evaluate the Source argument • Set toadCmdTableto evaluate Source as a table name whose columns are all returned by an internally generated SQL query. • Set toadCmdTextto evaluate Source as a textual definition of a command

32. Symbolic Constants • To use the symbolic constants specified in the ADO Reference, you must include a file that contains information about the ADO constants. For example to use: <% rs.Open "Customers", Conn, adOpenStatic, adLockOptimistic %> instead of <% rs.Open "Customers", Conn, 3, 3 %> • You must include the ADO Constants Include file in the .asp file by using a server-side include (SSI) statement. • If your primary scripting language is VBScript, the file you should include is named Adovbs.inc. If you are using JScript™, you should include the file named Adojavas.inc. • You can include these files in your .asp file by using the following: <!--#include virtual="http://infosys.uncc.edu/ASPSamp/Samples/adovbs.inc"--> Note: If you have problems with this, copy this file to your account and and use <!--#include file="adovbs.inc"-->

33. Moving Forward and Back • rs.BOF and rs.EOF are recordset properties for beginning and end of file, respectively. • The BOF property returns True (-1) if the current record position is before the first record in a Recordset, otherwise it returns False (0). • The EOF property returns True (-1) if the current record position is after the last record in a Recordset, otherwise it returns False (0). • Note: The BOF and EOF properties are set to True if you open an empty Recordset. RecordCount property is zero. • Note: If a Recordset holds at least one record, the first record is the current and the BOF and EOF properties are False. • rs.MoveNext moves the recordset ahead 1 record • rs.MovePrevious moves the recordset back 1 record

34. Moving through - example <% setconn = Server.CreateObject("ADODB.Connection") connString = "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & server.mappath("data/test.mdb") conn.Open connString set rs = Server.CreateObject("ADODB.recordset") sql="SELECT Companyname, Contactname FROM Customers" rs.Open sql, conn %> <table border="1" width="100%"> <% do until rs.EOF %> <tr> <% for each x in rs.Fields %> <td><% response.Write(x.value)%></td> <% next rs.MoveNext %> </tr> <% loop response.Write(“</table>”) rs.close Set rs = Nothing conn.close Set conn = Nothing %> rs.Open sql, conn response.Write(“<table border="1" width="100%">”) do until rs.EOF response.Write(“<tr>”) for each x in rs.Fields response.Write(“<td>”) response.Write(x.value) response.Write(“</td>”) next rs.MoveNext response.Write(“</tr>”) loop response.Write(“</table>”) rs.close Set rs = Nothing conn.close Set conn = Nothing %>

35. Finding Records • The Find method searches for a record in a Recordset that satisfies specified criteria. • Syntax: rs.Find “field = condition” • Where field is a data dictionary field name and condition is the condition to be found. Cannot use AND or OR • Cursor Type must be set to adOpenKeyset (1) or adOpenDynamic (2)

36. Finding Records (2) • Enclose text in single quotes: “State = ‘FL’” • Enclose dates in ‘#’ symbols: "date > #9/22/99#“ • Can use ‘*’ as a wildcard: “name like N*“ FindCondition = "Student_Number = '" + StNum + "'" rs.Find FindCondition If not rs.EOF Then Response.Write("<td align='center'> Yes </td>") End If

37. Filtering Records • Filter is a property of the recordset object. A filter, like an SQL ‘WHERE’ clause, returns only selected records to the recordset. • Syntax: rs.Filter = “state = ‘FL’” • “state = ‘FL’” can be replaced with any valid fieldname and condition, and conditions can be ‘AND’ed and ‘OR’edrs.Filter = “id = 20 AND cat = ‘ABC’”

38. Adding Data Records • Adding new records requires the combination of: • the AddNew method of the recordset. Thiscreates a new record for an updateable recordset object. After you call this method, the new record will be the current record. recordset.AddNew [Fields, Values] • the assignment of values to fields in the new record (within the recordset object) • the Update method of the recordset. This saves any changes you make to the current record of a recordset object recordset.Update [Fields, Values]

39. Record Add Example Dim Connect, DSN Set Connect = Server.CreateObject("ADODB.Connection") Connect.Open Horse SQLtext = "Select * from horse" Set rs = Server.CreateObject("ADODB.Recordset") rs.Open SQLtext,Connect,adOpenDynamic,adLockOptimistic rs.AddNew rs("Horse_Name") = Request("Horse_Name") rs("Horse_Description") = Request("Horse_Description") rs("Lg_Picture") = Request("Lg_Picture") rs.Update rs.close

40. Updating Existing Records • Updating (changing) existing records is a three step process: • Find the record to be updated - The Find method is one way • Set field values with updated data: objRec (“Field”).Value = “whatever” • Update the recordset - just as for adding records

41. Deleting Records • There is a Delete method for recordset • Find the record you wish to delete (with Find) • To delete the current record only, issue the command: rs.Delete

42. Or … use SQL statements Set Conn = Server.CreateObject("ADODB.Connection") Conn.Open ODBCAccess 'Look for a record with the given username and password SQL = "SELECT LastName, Firstname FROM Personal WHERE LastName = '" & Username & "' AND Pass = '" & Password & "'" Set RSAuthorizedUser = Conn.Execute(SQL) 'If no matching record was found, redirect to the error file If RSAuthorizedUser.EOF Then RSAuthorizedUser.Close Response.Redirect "..\Error.html" End if RSAuthorizedUser.Close 'Update the record using the data passed on from the form SQL = "UPDATE Personal SET FirstName=‘”&FirstName&"',eMail='"&eMail&"',Phone='"&Phone&"' WHERE (LastName = '" & Username & "' AND Pass = '" & Password & "')" Conn.Execute(SQL) 'Query for the updated record Set RSStudentRecord = Server.CreateObject("ADODB.Recordset") RSStudentRecord.ActiveConnection = Conn RSStudentRecord.CursorType = 3 'Static cursor. RSStudentRecord.LockType = 2 'Pessimistic Lock. RSStudentRecord.Source = "SELECT * FROM Personal WHERE LastName = '" & Username & "' AND Pass = '" & Password & "'" RSStudentRecord.Open %>

43. Examples & Reference • ADO example with list, insert, delete, update records • More examples • Lots of examples and tutorials from ActiveServerPages.com • Microsoft ASP Documentation • Microsoft ADO Reference