Segment 4 - Using a RecordSet

oh boy the fun stuff

Using and manipulating a recordset can be extremely easy or can turn out to be a nightmare, the more you know about them the more versatility you can get out of them.

The RecordSet Object has a Multitude of Properties, Methods, and Events. All of which are referenced by means of RS.<Param> where RS is the recordset object created in Segment 3 - Creating a RecordSet and Param is one of the Following

Properties :
.AbsolutePage
.AbsolutePosition
.ActiveCommand
.BOF
.Bookmark
.CacheSize
.CursorLocation
.CursorType
.DataMember
.DataSource
.EditMode
.EOF
.Filter
.Index
.LockType
.MarshalOptions
.MaxRecords
.PageCount
.PageSize
.RecordCount
.Sort
.Source
.Status
.StayInSync

Methods :
.AddNew
.Cancel
.CancelBatch
.CancelUpdate
.Clone
.Close
.CompareBookmarks
.Delete
.Find
.Fields
.GetRows
.GetString
.Move
.MoveFirst
.MoveLast
.MoveNext
.MovePrevious
.NextRecordset
.Open
.Requery
.Resync
.Save
.Seek
.Update
.UpdateBatch

Events :
.EndOfRecordset
.FetchComplete
.FetchProgress
.FieldChangeComplete
.MoveComplete
.RecordChangeComplete
.RecordsetChangeComplete
.WillChangeField
.WillChangeRecord
.WillChangeRecordset
.WillMove


The listing was supplied in order for you to be able to search for futher information on the use of these on your own, but i will cover some of the basic and more useful applications of some of these.



Cycling through a recordset
Do while not RS.EOF [green]'RS.EOF and RS.BOF are True/False Values denoting the beginning and ending of your recordset[/green]
Response.Write RS(FieldName) [green]' Fieldname being a string value of a field name or a variable containing the string value of a field name [/green]
RS.MoveNext [green]' Advances the Recordset Collection by one record, please rememeber to put this into your loop cycles, otherwise they will run until timeout due to without advancing the recordset, you will never reach the RS.EOF condition to stop the loop.[/green]
Loop



What's in my recordset?
If Not RS.EOF Then
For each Field in RS.Fields [green]' this is the fields Property of the RS object which is an array of the fieldnames respectively in order for the recordset queried[/green]
Response.write "Field Name:" & Field.Name & "<br>" [green]' Name of the Field[/green]
Response.Write "Field Type:" & Field.Type & "<br>" [green]' numeric representation unique to the Field Data Type[/green]
Response.Write "Field Size:" & Field.DefinedSize & "<br>" [green]' numeric representation of the length of the field Access Memo fields are handled strange, see below..[/green]
Response.Write "-----------------------<br>" [green]' just a divider for the next field[/green]
Next
End If



How do i get a quick table of the recordset Contents?
Response.Write "<table><tr>" [green]'starting an output table[/green]
If Not RS.EOF AND Not RS.BOF Then
Dim Counter
Counter = 1 [green]' If the recordset is not EOF or BOF then it's at least got one record for the counter[/green]
Response.Write "<td>#</td>" [green]' This makes the record count column head[/green]
For each Field in RS.Fields
Response.write "<td>" & Field.Name & "</td>" [green]' This makes column headers for the Field Names[/green]
Next
Response.write "</tr>"
RS.MoveFirst [green]' Places the Record pointer to the First record, just in case any prior record navigation had taken place[/green]
Do while Not RS.EOF
Response.Write "<tr>"
Response.Write "<td>" & Counter & "</td>" [green]' This Gives Record Number[/green]
For each Field in RS.Fields
Response.write "<td>" & RS(Field.Name) & "</td>" [green]' Remember the Value in the recordset parens can be a variable, in this case an array value with the field name.[/green]
Next
RS.MoveNext [green]' Advances the Recordset pointer[/green]
Counter = Counter + 1 [green]' tally off another record count[/green]
Response.Write "</tr>"
Loop
Else
Response.write "<td>Empty RecordSet</td>" [green]' Just in Case the Recordset has no Records[/green]
End If
Response.Write "</tr></table>" [green]'Closing out the table[/green]


more examples to be added soon.