ADO Recordsets

1. ADO Recordsets Using SQL Select Queries and ADO to populate web pages.

2. ADO What is it? • ActiveX Data Objects (ADO) is a cursor library. • It allows SQL to be sent to the server and status messages, rows and columns to be returned to the client. • It consists of a series of objects that are create-able with the “Server.CreateObject()” method. • All ADO Objects are derived from two projects ‘ADODB’ and ‘ADOR’. • ADO is Microsoft’s ultimate replacement for all other cursor libraries. • Fast and lightweight. ASP & ADO Recordsets

3. ADO Objects • Connection • Establishes and manages a connection to the data source • Command • Used for execute stored procedures, bulk changes and to manipulate database structure • Recordset • For returning rows to the client • Errors • Hold all of the errors returned from a provider ASP & ADO Recordsets

4. ADO Objects Organization • Although nominally all objects are decedents of the connection object all ADO objects may be created and used separately. • If you only need one instance of any of the ado objects, it is faster and cheaper to create just that one object without creating a connection object. • However, if you need multiple instances, a connection object will let you "pool" the connections. ASP & ADO Recordsets

5. Creating and Using DSNs • A DSN is a "Data Source Name" • It is a series of instructions to the client engine about how and where to connect to the back end data base. • In general there are three common kinds of DSNs • System : Stored in the registry (not portable) • File: Stored in a file • DSNless: Placed In Line In The Code ASP & ADO Recordsets

6. Which DSN to use? DSNless • For ASP • Placed in session variables in the global.asa file in the child web • In a constant at the top of the page • In a constant in a server side include • In an INI file fetched by the page • From the registry ASP & ADO Recordsets

7. Sample DSNs These are DSNless strings. (They would be all on one line no wrapping.) SQL*Server: Const cDSN1 = "DRIVER=SQL Server; SERVER=Parana; DATABASE=pubs; UID=student;PWD=student;" MS Access: const cDSN2 = "DRIVER=Microsoft Access Driver (*.mdb);DBQ= e:\XyzClub.mdb; DefaultDir=e:\;FIL=MS Access;DriverId=25; UID=admin;PWD=; " ASP & ADO Recordsets

8. Creation/Disposal of ADO Objects Dim CN 'Create a Connection Object Set CN = Server.CreateObject("ADODB.Connection") CN.ConnectionString = cDSN1 CN.Open 'Use connection object... 'All done with connection object. We have already disposed of all objects connected to it or created from it. (e.g. we must dispose of things in the exact opposite order we created them in. Also we should always dispose of things in the same scope we create them in… CN.Close Set CN = Nothing ASP & ADO Recordsets

9. Methods and Properties of the Connection object • Properties • ConnectionString: DSN • CommandTimeOut: How many seconds should ADO attempt to carry out a query before giving up and erroring. • ConnectionTimeOut: How many seconds should ADO try to connect to the data source before giving up and erroring. • Methods • Open: See previous example • Close: See previous example • Execute Execute command, recordsaffected, commandtype ASP & ADO Recordsets

10. .Execute Example 'Presume that the connection object is open to a database. Dim RA 'as Long Dim mySQL 'as String mySQL = "Delete From Authors Where AuID = 47" CN.Execute mySQL, RA If RA <> 1 Then Response.Write "Error: " & Str$(RA) & " records affected!" Else Response.Write "Auhor #47 Deleted" End if ASP & ADO Recordsets

11. The RecordSet Object • Used to return rows to the client. • #1 most used part of ADO • Can be created alone or with a connection object to "pool" connections. • Can be used to execute stored procedures without parameters. • Can be created w/o a connection object • Is created from "ADODB.Recordset" ASP & ADO Recordsets

12. Using the connection object to return a recordset ' You can use a select statement to 'create' a snapshot forward only, ' read only cursor. (A "firehose" cursor) dim rs 'as ADODB.Recordset dim mySQL 'as string mySQL = "Select * from authors" Set rs = CN.Execute mySQL '... Use cursor rs.Close set rs = nothing ASP & ADO Recordsets

13. Example: Snapshot (Read Only) Cursor (Rows) Dim mySQL as String Dim rs As New ADODB.Recordset 'Assume CN is an ADODB.Connection Object and is Open mySQL = "Select * From Authors" Set rs.ActiveConnection = CN rs.CursorType = adOpenStatic rs.Source = mySQL rs.Open ASP & ADO Recordsets

14. Example: R/W (Updateable) Cursor Dim mySQL as String Dim rs As New ADODB.Recordset 'Assume CN is an ADODB.Connection Object and is Open mySQL = "Select * From Authors" 'Notice use of SET verb Set rs.ActiveConnection = CN rs.CursorLocation = adUseClient rs.CursorType = adOpenDynamic rs.LockType = adLockOptimistic rs.Source = mySQL rs.Open ASP & ADO Recordsets

15. Recordset Methods • .MoveFirst, .MoveLast, .MovePrevious, .MoveNext (moves cursor in rows, error if .EOF or .BOF or if .RecordCount = 0) • .Move n (moves number of rows specified by, see note above) • .AddNew (adds a new row to recordset, applies defaults if any, needs to be followed by .Update) • .Update (commits changes to .Recordset) • .UpdateBatch (use in addition to update with batch locking) ASP & ADO Recordsets

16. Recordset Example #1 'Assuming rs is a ADODB.Recordset object If rs.State = adStateOpen Then If rs.RecordCount <> 0 Then rs.MoveFirst While not rs.EOF '… do something with records rs.MoveNext Wend End IF End If ASP & ADO Recordsets

17. Explanations #1a • EOF and BOF are functions that return true if we are at the end or beginning of a recordset. To check to make sure we are actually on a good (usable record) we should: If not (rs.EOF or rs.BOF) Then ASP & ADO Recordsets

18. Explanations #1b • The .RecordCount property returns the number of records: -1: Unknown (Any updatable recordset has this value (use the previous EOF/BOF test) 0: No records 1…n (only valid for static type (read only) recordsets) • Never use RecordCount to loop • Always test for <> 0, not a value ASP & ADO Recordsets

19. Explanations #1c • .State Property • adStateOpen (Recordset is opened and has a valid cursor, notice this does not imply there are rows!) • adStateClosed (Recordset is closed, cursor is invalid) ASP & ADO Recordsets

20. Recordsets w/o connection objects. Set rs = Server.CreateObject("ADODB.Recordset") ' Not no SET, creates a unique connection rs.ActiveConnection = cDSN 'DSNless string rs.CursorLocation = adUseClient rs.CursorType = adOpenStatic rs.LockType = adLockBatchOptimistic rs.Source = mySQL rs.Open 'If you only need one recordset, period, use this. ASP & ADO Recordsets

21. ADOASP.Asp <% 'Constant Values for ADO/file operations const adOpenStatic = 3 const asOpenDynamic = 2 const adOpenKeySet = 1 const adUseClient = 3 const adUseServer = 2 const adLockBatchOptimistic = 4 const adLockReadOnly = 1 const adOpenForwardOnly = 0 const ForReading = 1 ' Open a file for reading only. You can't write to this file. const ForWriting = 2 ' Open a file for writing only. You can't read from this file. const ForAppending = 8 ' Open a file and write to the end of the file. %> ASP & ADO Recordsets

22. The recordset has fields • A field is a column of data. For each row all of the fields will be of the same order, name and type. • These are kept in the fields collection • You may use early or late binding syntax to address fields. ASP & ADO Recordsets

23. Limiting the number of rows returned from a recordset. • The .MaxRecords property controls how many records are return by the recordset to the client. ( 0 = no limit ) • You are at the mercy of the provider, which records will be returned. • Usually, the query will be fully executed on the server then the specified number of rows are returned. • Sometimes this only works on server side cursors. (True for SQL*Server) ASP & ADO Recordsets

24. Field Binding Syntax • ASP does not support the Early Binding syntax rs!FieldName • Late Binding rs.Fields(i).Value rs.Fields("FieldName").Value • All are equivalent and are listed in ascending order of performance. • ASP Programmers can use a short cut rs("FieldName") ASP & ADO Recordsets

25. Field Binding Notes • As always early binding is faster, however you can't use early binding in scripts (yet) or ASP. • You can't iterate the fields collection with early binding. • You may mix early and late binding if early binding is allowed. • For safeties sake ASP Programmers should use the short cut which insure that the .Value property is being examined. • Indexed iteration is a fast way to spit out all of the columns ASP & ADO Recordsets

26. Iterating the fields collection 'Assuming rs is an open recordset object 'Notice like most collections, starts at ZERO to Count -1 'Prints name and type (a table of which is on the next slide) For i = 0 to rs.Fields.Count -1 response.write rs.fields(i).Name & ": " & rs.Fields(I).Type Next 'Prints name and value For i = 0 to rs.Fields.Count -1 response.write rs.fields(i).Name & ": " & rs.Fields(I).Value Next ASP & ADO Recordsets

27. Type Constants Constant Value Access VB adBoolean 11 Yes/No Boolean adTinyInt 16 Number/Byte Byte adSmallInt 2 Number/Integer Integer adInteger 3 Number/Long Long adCurrency 6 Currency Currency adDBTimeStamp 135 Date Date/Time adSingle 4 Number/Single Single adVarChar 200 Text String adLongVarChar 201 Memo String ASP & ADO Recordsets

28. Output of a Recordset to HTML 'Assume the record rs contains data and is at the beginning and you want all columns 'Create The Table and Print the columns response.write "<table border='1' cellpadding='2' cellspacing='0'>" response.write "<tr>" for I = 0 to rs.fields.Count - 1 response.write "<td><b>" & rs.fields(I).Name & "</b></td>" next response.write "</tr>" While not rs.EOF response.write "<tr>" for I = 0 to rs.Fields.Count - 1 response.write "<td>" & rs.fields(I).value & "</td>" next response.write "</tr>" rs.MoveNext wend response.write "</table>" ASP & ADO Recordsets

29. Doing updates in cursors • The following slides use recordsets to do the 3 action queries (insert, update, delete) however because of the episodic nature of the internet, these techniques should be avoided in favor of the techniques next lecture. • Skim these slides only. ASP & ADO Recordsets

30. Updating values in recordsets 'Assuming rs is an open recordset and is updateable. Rs("LastName") = "Smith" rs("FirstName") = "Tom" rs("CatsOwned") = 77 rs("Crazy") = True rs.Update 'Commit Changes rs.UpdateBatch ‘Send changes to server ASP & ADO Recordsets

31. Adding a new record 'Assuming rs is an open recordset and is updatable. rs.AddNew rs("LastName") = "Smith" rs("FirstName") = "Tom" rs("CatsOwned") = 7 rs("Crazy") = -1 rs.Update 'Commit Changes rs.UpdateBatch ‘Send changes to server ASP & ADO Recordsets

32. Undoing edits in progress. • .CancelUpdate executed before .Update will "undo" the changes you have made. • After the update method is executed for optimistic locking (no batching) it is too late to fix. (consider using a transaction). • In the case where you have Batch Optimistic locking, .CancelBatch before .UpdateBatch "undoes" the changes. ASP & ADO Recordsets

33. Deleting the current record. • The .Delete method deletes the current record. • Both .EOF and .BOF will be set to true, you may therefore have trouble looping through the rest of the recordset. • Consider using an execute query instead. • No undo except in a transaction. ASP & ADO Recordsets

34. Notes on previous two examples • If you specified 'BatchLocking' for LockType e.g. adLockBatchOptimistic, you must (at some point) execute the .BatchUpdate Method to commit changes to database. • Batch locking is more efficient, but you have to manage batch collisions and remember to do batch update. ASP & ADO Recordsets

35. ASP and ADO Continued Paging Results Sets and Using Action Queries To Maintain Data

36. What is Paging? • ADO has a feature that easily allows programmers to query a database and divide the results into equal size pieces that can be retrieved as a set. This is called paging. • ADO Provides the following 3 properties • PageSize: To set how many rows are in each page, set before recordset is opened • PageCount: Determined after the recordset is opened calculates the total number of pages including the partial page of records at the end • AbsolutePage: This is used to set which page is brought back to the client. Pages start at 1 and go to PageCount. • In most cases a self referencing form is desirable ASP & ADO Recordsets

37. Title Search Start Page Title search example start page. One text box and three check boxes. The default is Title. Check boxes were chosen as being more intuitive for users as opposed to a combo box or radio buttons. ASP & ADO Recordsets

38. Title Search Results This is the 2nd page notice the URL. This URL was constructed for the 'Next 2' Hyperlink ASP & ADO Recordsets

39. Opening a recordset for use in paging set rs = server.createobject("ADODB.Recordset") rs.ActiveConnection = cDSN 'Defined above rs.CursorType = adOpenStatic 'Notice this type rs.LockType = adLockReadOnly 'Readonly (for speed) rs.cursorLocation = adUseServer 'so pages can be computed rs.PageSize = cRECMAX 'Number of Records/Page rs.Source = mySQL rs.Open 'Now adjust page number to be correct if PAGE < 1 then PAGE = 1 if PAGE > rs.PageCount Then PAGE = rs.PageCount 'Then set the desired page If rs.RecordCount <> 0 Then rs.AbsolutePage = PAGE 'Display Records... (See next slide) ASP & ADO Recordsets

40. Spinning the paged records If rs.RecordCount = 0 Then response.write "<p>No matching records, <a href='TitleSrch.Asp'>Search Again</a><p>" Else Response.Write "<table width='100%' Border='0' cellspacing='0' cellpadding='4'>" 'Write out Column Names '... 'Write out all rows values For J = 1 To cRECMAX 'Catch the last page where there are fewer then cRECMAX records if rs.Eof Then exit for end if 'Write out the row… '... rs.Movenext Next Response.Write("</table>") End if ASP & ADO Recordsets

41. Setting up the passed parameters 'Notice that we create the local variables from BOTH the 'querystring() and form() Arguments by using the shortcut notation. SEARCH=Trim(request("SEARCH")) 'The numeric ones demonstrate a trick for converting to a number quickly FLAG=cInt("0" & Trim(request("FLAG")) ) PAGE=cInt("0" & Trim(request("PAGE")) ) C1=cInt("0" & Trim(request("C1")) ) C2=cInt("0" & Trim(request("C2")) ) C3=cInt("0" & Trim(request("C3")) ) 'If no flags are selected default to title. If (C1 + C2 + C3) = 0 then C1=1 'Create a QueryString for use in the paging hyperlinks myRETURN = MakeReturn(SEARCH,C1,C2,C3) '... 'F(X) To make the arguments for the hyperlinks (called the return in this example) Function MakeReturn(SearchText, F1, F2, F3) MakeReturn ="&amp;C1=" & F1 & "&amp;C2=" & F2 & "&amp;C3=" & F3 & "&amp;SEARCH=" & SearchText Exit Function End Function ASP & ADO Recordsets

42. Notes on parameter passing • Every link to a page needs to include • Page desired (1 - Page Count) • All of the search parameters (e.g. Search, C1-C3) • A flag that indicates the results page is needed • In this design, a function MakeReturn() creates the query string for the search parameters that can be appended to each of the hyper links generated thus allowing the query to be re-executed each time with the same criteria and different pages. • This may seem wasteful, but the operation is actually fairly cheap providing the parameters used in the query are indexed. ASP & ADO Recordsets

43. Creating Hyperlinks • We desire a hyperlink as on slide 4 of : http://www.valtara.com/csc96c/aspsamples/1TitleSrch.Asp?FLAG=1&Page=2&C1=1&C2=1&C3=1&SEARCH=comp • To get that we compose a querystring for the hyperlink using code like this: myReturn = MakeReturn() Response.Write ". . .<a href='TitleSrch.Asp?FLAG=1&amp;Page=" & cStr(myPage+1) & myReturn & "'> . . ." 1. notice the server expanded the hyperlink in the code generated when it got sent back to the client ASP & ADO Recordsets

44. Creating the page navigation Function DoNav(myReturn,myPage,MaxPage) dim sret sRet = "" sRet = sRet & "<table border='0' cellpadding='3' cellspacing='0' width='100%'><tr>" sRet = sRet & "<td bgcolor='#FFCC00' align='left'><a href='TitleSrch.asp?FLAG=0'> <font color='#000000'>New Search</font></a></td>" sRet = sRet & "<td bgcolor='#FFCC00' align='left'><a href='#TOP'><font color='#000000'>Top of Page</font></a></td>" if myPage > 1 then sRet = sRet & "<td bgcolor='#FFCC00' align='left'><a href='TitleSrch.asp?FLAG=1&amp;Page=1" & myReturn & "'><font color='#000000'>First Page</font></a></td>" else sRet = sRet & "<td bgcolor='#FFCC00' align='right'>&nbsp;</td>" end if sRet = sRet & "<td bgcolor='#FFCC00' align='left'>myPage " & myPage & " of <a href='TitleSrch.asp?FLAG=1&amp;Page=" & cStr(MaxPage) & myReturn & "'><font color='#000000'>" & MaxPage & "</font></td>" if myPage > 1 then sRet = sRet & "<td bgcolor='#FFCC00' align='right'><a href='TitleSrch.Asp?FLAG=1&amp;Page=" & cStr(myPage-1) & myReturn & "'><font color='#000000'>Previous " & cRECMAX & "</font></a></td>" else sRet = sRet & "<td bgcolor='#FFCC00' align='right'>&nbsp;</td>" end if if myPage < MaxPage Then sRet = sRet & "<td bgcolor='#FFCC00' align='right'><a href='TitleSrch.Asp?FLAG=1&amp;Page=" & cStr(myPage+1) & myReturn & "'><font color='#000000'>Next " & cRECMAX & "</font></a></td>" else sRet = sRet & "<td bgcolor='#FFCC00' align='right'>&nbsp;</td>" end if sRet = sRet & "</tr></table>" DoNav = sRet exit function End Function ASP & ADO Recordsets

45. Notes about page navigation • We go through all of that trouble to present the user with only the relevant navigation links. • What is missing is a routine to count the number of actual records on the last page and display that instead of the number of records per page. • The page size of 2 is to force paging for the example. Obviously, bigger would be better. • The server side cursor used this was is fairly light weight and the technique is highly scale-able. ASP & ADO Recordsets

46. Achieving the “green bar” effect • Set a variable to the back color attribute desired, in this case force the default back color to show in each cell: bColor = "" • As each record is written determine if it is an even record and set the back color attribute to the desired contrast color by creating an attribute string: if j mod 2 = 0 then bColor="bgcolor='#FFFFCC'" • Then insert that value into each of the <TD>s written Response.write "<td " & bColor & " valign=… ASP & ADO Recordsets

47. Maintaining Databases Action queries are our friends

48. Overview • The web is episodic in nature, so too should be our data access • IIS 4 is built on MTS that provides connection pooling. The upshot of this is that connection objects normally very expensive to create are quite cheap and fast. • Thus, the connection objects execute method provides a good way to modify records in the database using the SQL update, insert and delete verbs. ASP & ADO Recordsets

49. Authors Table Maintenance Example Notice we hyperlink the display with the PK of the table so that when a record is clicked on the record # is passed to the next page. ASP & ADO Recordsets

50. Editing the author record Again we pass the action in the embedded hyperlink ASP & ADO Recordsets