Database Connectivity Session 2

1. Database Connectivity Session 2

2. Topics Covered • ADO Object Model • Database Connection • Retrieving Records • Creating HTML Documents on-the-fly

3. Database Access • ASP provides easy access to databases • It contains Database Access Component • Contains ActiveX Data Objects (ADO) • Object Model • ADO methods to manipulate databases and build dynamic pages • ADO can use ODBC interface to RDBs. • ADO can also use other interfaces – OLE-DB Provider

4. ADO Object Model Connection Object Recordset Object Fields Collection Properties Collection Field Object Property Object Command Object Parameters Collection Properties Collection Parameter Object Property Object Properties Collection Errors Collection Property Object Error Object

5. ADO Objects • Connection Object • Establish active connection to gain access to data • Command Object • Used to execute SQL queries or manipulate data directly • Recordset Object • Gives us access to data that is returned from executing the SQL query, a stored procedure, or by opening the table • Properties Collection • Collection of Properties for Connection, Command, and Recordset objects • Fields Collection • Individual fields (values) within each record

6. Connecting to Data Sources • Before connecting to a data store, we need to specify what it is and where it is • Three ways to supply this information when creating the connection: • Connection String • Simple character string that lists all of the information needed to connect to a data source • Data Link Files • Create a Universal Data Link (UDL) file that stores the info. • Data Source Names (DSN) • Use ODBC drivers to set up the DSN • Now considered as outdated approach – OLE-DB provider is considered better because of efficiency

7. Connection String • A typical connection string will contains some or all of the following key pieces of information • Provider:the type of OLE-DB provider used in the connection • Driver:type of ODBC driver used if not using OLE-DB provider • Initial File Name or Data Source:the physical database path and the file name • Initial Catalog:the name of the database • User ID:the user name needed to connect to the database • Password:the password for the specified user • Persist Security Info:a boolean variable set to TRUE if you want windows to remember the password

8. Examples of Connection String “Provider = Microsoft.Jet.OLEDB.4.0;” & _ “Data Source = c:\mydatabases\test.mdb;” & _ “Persist Security Info = False” • For Access Database • MS Access uses the Microsoft Jet Engine, so the OLE-DB provider is specified as the Jet Engine • If we are accessing the same database using the ODBC driver for MS Access (instead of the OLE-DB provider), we use the following: • For SQL Server database, the connect string may look like: “Driver = {Microsoft Access Driver (*.mdb)};” & _ “DBQ = c:\mydatabases\test.mdb” “Provider = SQLOLEDB; Persist Security Info = False;” & _ “User ID = joe; Password = shmoe;” & _ “Initial File Name = c:\mydatabases\test.mdf”

9. DSN-less Connection String • Make a folder called db in your web folder • Put your database into the db folder you just created.  • Use the following connection string in your ASP code:set objConn= Server.CreateObject("ADODB.Connection")objConn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; _ DBQ= " & server.mappath("db/DBName.mdb") • Modify the DBName.mdb to match your actual database name.

10. Connection Object • Create an instance of connection object • set myconn=Server.CreateObject(“ADODB.Connection”) • Scope of Connection • Create the connection every time you access the data • Create the connection once and use it for different operations • Connection creation statement can be placed in the Session_onStart or Application_onStart routines in global.asa • Connection object contains methods and properties to open, and close connections, execute commands on the data source specified in the connection, and controlling transactions.

11. Methods of Connection Object • OpenOpens a new connection to a data source • CloseCloses an existing open connection • ExecuteExecute a query, SQL statement or stored procedure • BeginTransBegins a new transaction • CommitTransSaves any changes made and ends the transaction. May also start a new transaction • RollbackTransCancels any changes made and ends the transaction. May also start a new transaction • OpenSchemaFor server side scripts, allows the view of database schema, such as tables, columns, etc.

12. Command Object • Command object can be used to directly execute commands • It provides methods and properties to manipulate individual commands. • Methods • CreateParameter • Execute • Properties • ActiveConnection, CommandText, Command Timeout, CommandType, Name, Prepared, State

13. RecordSet Object • Contains query results • Syntax set myset = connection.execute(CommandText, RecordsAffected, Options) set myset = command.execute(RecordsAffected, Parameters, Options) • Example set mycon=server.createobject(“ADODB.Connection”) mycon.open “Northwind” “System DSN” set myset = mycon.execute(“select * from products”)

14. RecordSet Object (Continued) • The ADO Recordset object is used to hold a set of records from a database table • A Recordset object consist of records and columns (fields) • Most important and the most used object to manipulate data from a database • When you first open a Recordset, the current record pointer will point to the first record and the BOF and EOF properties are False. • If there are no records, the BOF and EOF property are True.

15. RecordSet Object (Continued) 0 1 2 3 4 Fields Collection MyRs.fields.count will return 5 MyRs(0).Name returns the name of the first field MyRs(0).Value returns the value of the first field Current Record Pointer BOF First Record RecordSet Object (say MyRs) Last Record EOF When the recordset object is created, the Current Record Pointer will be pointing to the First Record and BOF and EOF properties will be False If the recordset is empty, BOF and EOF will be True

16. Methods of RecordSet Object • AddNew – Creates a new record in an updatable recordset • CancelBatch – Cancels a pending batch update • CancelUpdate – Cancels any changes made to the current or new record • Clone – creates a duplicate of the current recordset • Close – closes an open recordset and any dependent objects • Delete – deletes the current record in an open recordset • GetRows – Extract a number of rows into an array • Move – Moves the cursor forward or backward by specified number of records Example: move(5) – moves the cursor forward by 5 records move(-3) – moves the cursor backward by 3 records

17. Methods of RecordSet Object (contd.) • MoveFirst, MoveLast, MoveNext, MovePrevious • Moves to the first, last, next, or previous record in the recordset, and makes that the current record • NextRecordset – Move to the next recordset in the query • Open – Opens a curser on a recordset • Requery – Updates data by re-executing the original query • Resync – Refreshes the data, but does not re-execute the query. This allows updates to be seen but no new rows. • Supports – Determines whether the recordset supports certain functions • Update – Saves any changes made to the current record • UpdateBatch – Writes all pending batch updates to disk.

18. Examples • Example 1 - Get all the customers • Example 2 – Get products with CategoryID = 3 • Example 3 - Retrieve data based on user input

19. Example 1 Code (customers_new.asp) <HTML><HEAD><TITLE>Results</TITLE></HEAD> <BODY BGCOLOR=cyan> <%@ LANGUAGE="VBSCRIPT"%> <% SQL = "SELECT * FROM CUSTOMERS;“ SET DbObj = Server.CreateObject("ADODB.CONNECTION") myvar = "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & _ server.mappath("db_f03/Northwind.mdb") DbObj.Open myvar SET oRs = DbObj.Execute(SQL) %> (continued in next slide) Define a simple sql statement create an instance of the connection object Construct the DSN-less connection string Notice the continuation character ( _ ) Open the connection to the data source execute the sql string and store the results in the recordset object

20. Example 1 Code (Continued) <h1>Customer Information</h1> <TABLE BORDER=3> <TR> <TD><b><center>CustomerID</b></center></TD> <TD><b><center>CompanyName</b></center></TD> <TD><b><center>ContactName</b></center></TD> <TD><b><center>Address</b></center></TD> </TR> <% WHILE NOT oRs.EOF %> <TR> <TD> <%= oRs.Fields("CUSTOMERID").Value %> </TD> <TD> <%= oRs.Fields("COMPANYNAME").Value %> </TD> <TD> <%= oRs.Fields("CONTACTNAME").Value %> </TD> <TD> <%= oRs.Fields("ADDRESS").Value %> </TD> </TR> <% oRs.MoveNext %> <% WEND %> </TABLE> </BODY></HTML> Create a Table. Hard code the attribute names in the first row while the recordset is not empty Add the value of each attribute into HTML table using the <%= operator Move the cursor to the next available record End of the while loop

21. Example 2 Code (products_new.asp) <html><head> <meta http-equiv="Content-Type" content="text/html; charset=windows-1252"> <meta name="GENERATOR" content="Microsoft FrontPage 4.0"> <meta name="ProgId" content="FrontPage.Editor.Document"> <title>New Page 1</title></head> <body> <h1>Products with Category ID 3</h1> <% set my_conn= Server.CreateObject("ADODB.Connection") myvar = "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & server.mappath("db_f03/Northwind.mdb") my_conn.Open myvar StrSql= "Select * from products where CategoryID = 3" set MyRs = my_conn.Execute (StrSql) if MyRs.BOF or MyRs.EOF then response.write "ah, yeah: That didn't work.<br>There was an error. your gonna hafta to go " & _ "ahead, and ah, try again.<a href=javascript:history.back();>back</a> okay? thanks a lot." response.end end if %> Establish connection with the nortwind database Construct the DSN-less connection string Open the connection to the data source From the products table, retrieve the products with categoryid=3 and store the records in the MyRs recordset object If no records found are found, i.e., the Recordset object is empty, then send the user back to the previous page

22. Example 2 Code (Continued) Now, go through the record set and create the HTML Table <p><br> <table border =1 width="100%"> <tr> <% howmanyfields=MyRs.fields.count for i=0 to howmanyfields-1 %> <td><b><font color = "darkblue"><%= MyRs(i).name %></font></b></td> <% next %> </tr> <% do while not MyRs.eof %> <tr> <% for I = 0 to howmanyfields-1 cur_field = MyRs(I).value %> <td valign=top><%= cur_field %></td> <% next %> </tr> <%MyRs.movenext loop%> <!-- end of do while loop --> <%MyRs.close Set MyRs= Nothing My_Conn.Close set My_Conn=nothing%> <br></table></body></html> Get the field count Output the field names for the first row Now, go through each record and print out the values of each field. Start with the first record. Within each record, for each field get its value and store it in the variable Output the value of the variable into the table data element Move the record pointer to the next record Close the recordset and the connection and set them to nothing

23. Example 3 Code (select_new.asp) <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=windows-1252"> <title>Get Product from Form</title> </head> <% 'Establish the connection with the nowrthwind database set my_conn= Server.CreateObject("ADODB.Connection") 'Construct the connection string using relative path for the database file myvar = "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & server.mappath("db_f03/Northwind.mdb") 'Open the connection to the data source my_conn.Open myvar 'Get the products from the products_new table StrSql= "Select * from products" set rs = my_conn.Execute (StrSql) 'Display a message if no products found if rs.BOF or rs.EOF then ' No records found response.write "Hmm... That didn't work.<br>There was an error. " & _ "Oh well, try again.<a href=javascript:history.back();>back</a>" response.end end if %>

24. Example 3 Code Continued (select_new.asp) <body> <h1>Available Products..</h1> <h2>Please select a product and click on Submit Query to get more information.</h2> <!-- Create a select object with product ids from the retrieved tuples --> <!-- From that list the user can select a particular product --> <!-- When the user clicks on the submit button, the form, along with its data, --> <!-- is sent as input to the get_product_new.asp file --> <form method="post" action="get_product_new.asp"> <!-- call the next ASP page --> Select Product: <select name="product"> <!– creating the drop down list --> <% do while not rs.eof %> <option value="<%=rs("productid")%>"><%=rs("productname")%></option> <% rs.movenext 'move the cursor to the next record in the recordset loop my_Conn.Close ‘close th e connection object set my_conn = nothing %> <input type="submit"></form> </body> </html>

25. Example 3 Code (get_product_new.asp) <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=windows-1252"> <title>Get Product from Form</title> </head> <% if request.form("product")="" then response.write "You must select a product in <a href=""select_new.asp"">select_new.asp</a> first." response.end end if 'Establish the connection with the nowrthwind database set my_conn= Server.CreateObject("ADODB.Connection") 'Construct the connection string using relative path for the database file myvar = "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & server.mappath("db_f03/Northwind.mdb") 'Open the connection to the data source my_conn.Open myvar 'Construct the SQL statement by concatenating the value selected by the user StrSql= "Select * from products where productid=" & request.form("product") 'Execute the SQL statement and create a recordset set rs = my_conn.Execute (StrSql) 'Display a message if no products found if rs.BOF or rs.EOF then ' No records found response.write "Hmm... That didn't work.<br>There was an error. Oh well, " & _ "try again.<a href=javascript:history.back();>back</a>" response.end end if %>

26. Example 3 Code Continued (get_product_new.asp) <body> <h1>Information about the " <%=rs("productname")%> " product:</h1> <table border="1"> <!-- Create the first row of the table with headings --> <tr><td>Product ID</td><td>Product Name</td><td>Supplier ID</td><td>Quantity Per Unit</td> <td>Price</td><td>Units in Stock</td></tr> <% do while not rs.eof %> <!-- Display the information corresponding to the selected product --> <tr><td><%=rs("productid")%> </td><td><%=rs("productname")%></td> <td><%=rs("supplierid")%></td><td> <%=rs("QuantityPerUnit")%></td> <td><%=rs("UnitPrice")%></td><td><%=rs("unitsinstock")%> </td></tr> <% rs.movenext loop 'End of do while loop my_Conn.Close ‘Close the connection object set my_conn = nothing %> </table> </body> </html>

27. Assignment 2 • Create two asp pages. • The first asp page should list the Company names in a drop-down list. The user can select a Company and click on the submit button. It should call the next asp page which lists the Company information as well as the sponsorship information • Database to be used: exercise2.mdb • Tables to be used: Company and Projects • Click to see the assignment demo