1 / 20

CIS 451: ASP Recordsets

CIS 451: ASP Recordsets. Dr. Ralph D. Westfall May, 2002. Recordset Object. group of records returned by a query "cursor" initially points to 1st record can create a recordset without explicitly opening a connection Set objRS = Server.CreateObject("ADODB.Recordset"). Opening a Recordset.

irish
Download Presentation

CIS 451: ASP Recordsets

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. CIS 451: ASP Recordsets Dr. Ralph D. Westfall May, 2002

  2. Recordset Object • group of records returned by a query • "cursor" initially points to 1st record • can create a recordset without explicitly opening a connection • Set objRS = Server.CreateObject("ADODB.Recordset")

  3. Opening a Recordset • objRS.Open "Product", strConnect, adOpenForwardOnly, adLockReadOnly, adCmdTable • "Product" (change to database table name) • strConnect = same string as for connection • adOpenForwardOnly = one-way cursor • adLockReadOnly = non-updateable • adCmdTable = indicates that the 1st parameter (above) is a table name

  4. Recordset Open Parameters • objRS.Open (1st, 2nd, 3rd, 4th, 5th) • can skip values e.g. (1st, , , , 5th) • 3rd, 4th, 5th parameters = integer constants • can hard code numeric values ( , , 0, 1, 2) • or use constant names with hard coded values • see prodtest2.asp code • can include constant values (adovbs.inc, p. 520) • or from ADO type library dll p. 519

  5. Recordset Parameters - 2 • pulling ADO constant values into your ASP • recommended (insert following <html> tag) • <!- metadata type="typelib" file="C:\Program Files\Common Files\System\ado\msado15.dll" -> • need to make sure that file you want is on server in specified path (OK on class server?) • former method (not recommended) • <!-- #include file=C:\Program Files\Common Files\System\ado\adovbs.inc--> • (open in Notepad, then see next page)

  6. Recordset Parameters - 3 • objRS.Open (1st, 2nd, 3rd, 4th, 5th) • 1st parameter is a string, or string variable • contents of 1st parameter identified by 5th: • (2) adCmdTable - table name e.g. "Product" • (1) adCmdText - command e.g. SQL string • (8) adCmdUnknown - default • plus others

  7. Recordset Cursor Parameters • (0) adOpenForwardOnly - not scrollable: cursor can move forward, not backward • (3) adOpenStatic - scrollable, but can't see changes that occurred while viewing • (2) adOpenDynamic - scrollable, and can see all changes • (1) adOpenKeyset - similar to dynamic • ForwardOnly = fastest, Dynamic = slowest

  8. Recordset Locking Parameters • (1) adLockReadOnly - no changes to source • (2) adLockPessimistic - 100% locked • (3) adLockOptimistic - another user can update before you (rejects your update) • (4) adLockBatchOptimistic - change several records, then update (optimistic lock on each record as updated) • other (non-batch) locks only update record at cursor

  9. Using the Recordset Object • [objectName].[property] • EOF (Boolean) – objectName.EOF = true if cursor past last record • end of file • BOF - true if cursor moved before 1st record • beginning of file • Bookmark - cursor position of a specific record • objectName.Bookmark moves cursor to Bookmark • RecordCount - # of records in recordset

  10. Using the Recordset Object - 2 • move methods (objectName.[method]) • cursor must be adOpenStatic or adOpenDynamic • MoveFirst, MoveLast • go to first or last record • MovePrevious, MoveNext • Move # [, Start] • (positive # =forward, negative #=back) • starting from Start (optional)

  11. Find Records in the Recordset • objectName.Find criteria[, skip, direction, start] • criteria: "<fieldname> <compare> <value>" • put value in single quotes if has blanks, special chars • compare (operator) can be >, <, =, or LIKE (with wildcards [*] in value) • optional parameters • skip = # of records to skip • direction - defaults to adOpenForwardOnly • start = bookmark

  12. Find Records in Recordset - 2 • example • objRS.Find "Price > 4.00" • when open recordset, make cursor adOpenStatic • Note: search changes cursor position • if no record found, cursor is at EOF (forward search), or possibly BOF with other directions • can reset cursor with bookmark created before • objRS.Bookmark = [variable holding bookmark]

  13. Filtering Records • Find moves cursor to next matching record • Filter screens out all non-matching records • create as a property of recordset object • [objectName].Filter = [criteria] • objRS.Filter = "ProdName = 'widget' " • note single quotes for a string

  14. Filtering Records - 2 • special filters • objectName.Filter = adFilterNone • objRS. Filter = adFilterNone • turns filter off so can access all records again • objectName.Filter = adFilterPendingRecords • shows records changed but not yet updated (for batch mode)

  15. Review: For Each … Next • can cycle through collections without knowing individual item names • For Each [any name] In [collection] • [code does something to each any name] • Next • [any name] = anything you want to call an individual item in the collection • [collection] = collection name e.g. Request.QueryString, Session.Contents, etc.

  16. Fields Collection • contains names of all fields in recordset • can access with For Each … so don't have to know field names to create output • For Each fldF in objRS.Fields • [code puts field names/data in html table] • Next • code samples: • DataStore.asp, RecToTable.asp, ProdTable.asp

  17. Creating Array from Recordset • varArray=objRecordset.GetRows (Rows, Start, Fields) • Rows = # of rows (default = here to end of file) • Start = bookmark • Fields = field name or # (1 = 1st column, etc.) • could use variable name of an array of field names or #s as the field name

  18. Access Recordset Array Data • varFields=objRS.GetRows 'get rows • intLastRow=UBound(varFields, 1) • 'function gets row # for last row • intLastCol=UBound(varFields, 2) • For intRow=1 to intLastRow • For intCol=1 to intLastCol 'nested loop • [code to write columns for each row] [2 Nexts to close loops]

  19. Exercise – 1 (code) • copy DataStore.asp to diskette and then edit • change file and path to your database • copy ProdTable.asp to diskette and edit • change objRS.Open "Product" to name of your table (only if your table has different name) • save changes, upload to your account • also upload RecToTable.asp • test by loading ProdTable.asp in browser

  20. Exercise - 2 • copy prodtest2.asp to diskette and then edit • change database file to your database • add fields from your database e.g. Price • upload to your account and test • modify it to use DataStore.asp & metadata typelib (see top of ProdTable.asp) [Dim too] • replace loop with a Find • change cursor parameter to adOpenStatic

More Related