Segment 3 - Creating a RecordSet

Recordsets, the heart behind alot of our ASP pages...

Recordsets are complex little beasts. Most people will overlook their multi-functionality due to their simplicity of use.

1.) Making a Recordset :
Using the Connection Con referenced in Segment 2 Creating a Connection....

1a.) Easy-Peasy RecordSets :
Commonly used for action queries where no recordset is returned, or for simple record output uses.

[blue]Set RS = Con.Execute([red]StrSQL[/red])[/blue]

where StrSQL is a valid SQL String, be it INSERT, UPDATE, SELECT, DELETE, a SP, etc.

1b.) Creating an Active RecordSet :
[green]Please note that in saying 'Active' it means that the connection is open, that there is a record pointer, records are updateable, deleteable etc, not meaning that it's an active connection from server to client and bi-directional, it's only bi-directional from the web server to the data server ( if not same machine ) for the duration of the execution of code just like ASP it's server side only[/green]
Used for handling paged recordsets (see FAQ: http://www.tek-tips.com/faqs.cfm?fid=186), modifiable pointer based recordsets, and other 'special' method based recordsets.

[blue]Set RS = Server.CreateObject("ADODB.Recordset")
[green]this is where method/collection arguments and pre-populated recordset code would take place.[/green]
RS.Open StrSQL, ActiveConnection, CursorType, LockType, Options[/blue]

Documentation on what each of the arguments are rather involved, although StrSQL is of course the SQL statement and ActiveConnection would be the Connection Object Con.
For more information on the arguments for a recordset please see :
http://msdn.microsoft.com/library/en-us/ado270/htm/mdmscadoproperties.asp

http://support.microsoft.com/default.aspx?scid=kb;en-us;165671


2.) Manipulating the RecordSet :
At this point is where record output, pointer navigations and updates/deletes would take place these are covered in Segment 4 - Using a RecordSet


3.) Closing the RecordSet Object :
Just like in the Connection Object, this is one of the most often overlooked steps, or misplaced steps in coding, it is necessary to close the Recordset otherwise the residual connection will eat up available connection slots to the data source and eat up server memory.

[green]If step 1b was used[/green]
[blue]RS.Close[/blue]
[green]used for both steps 1a & 1b[/green]
[blue]Set RS = nothing[/blue]

This calls RS's Close method, hence closing the active recordset ( if opened ), and Set RS=nothing clears the server memory of the recordset object

Next Section : http://www.tek-tips.com/faqs.cfm?fid=3804