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
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'RS.EOF and RS.BOF are True/False Values denoting the beginning and ending of your recordset Response.Write RS(FieldName)' Fieldname being a string value of a field name or a variable containing the string value of a field name RS.MoveNext' 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. Loop
What's in my recordset? If Not RS.EOF Then For each Field in RS.Fields ' this is the fields Property of the RS object which is an array of the fieldnames respectively in order for the recordset queried Response.write "Field Name:" & Field.Name & "<br>" ' Name of the Field Response.Write "Field Type:" & Field.Type & "<br>" ' numeric representation unique to the Field Data Type Response.Write "Field Size:" & Field.DefinedSize & "<br>" ' numeric representation of the length of the field Access Memo fields are handled strange, see below.. Response.Write "-----------------------<br>" ' just a divider for the next field Next End If
How do i get a quick table of the recordset Contents? Response.Write "<table><tr>" 'starting an output table If Not RS.EOF AND Not RS.BOF Then Dim Counter Counter = 1 ' If the recordset is not EOF or BOF then it's at least got one record for the counter Response.Write "<td>#</td>" ' This makes the record count column head For each Field in RS.Fields Response.write "<td>" & Field.Name & "</td>" ' This makes column headers for the Field Names Next Response.write "</tr>" RS.MoveFirst' Places the Record pointer to the First record, just in case any prior record navigation had taken place Do while Not RS.EOF Response.Write "<tr>" Response.Write "<td>" & Counter & "</td>" ' This Gives Record Number For each Field in RS.Fields Response.write "<td>" & RS(Field.Name) & "</td>" ' Remember the Value in the recordset parens can be a variable, in this case an array value with the field name. Next RS.MoveNext' Advances the Recordset pointer Counter = Counter + 1 ' tally off another record count Response.Write "</tr>" Loop Else Response.write "<td>Empty RecordSet</td>" ' Just in Case the Recordset has no Records End If Response.Write "</tr></table>" 'Closing out the table