OVERVIEW of Creatable Recordsets

Contributed by
Rating: 5 stars5 stars5 stars5 stars5 stars / 2
August 13, 2000
Rate this Article:
MEH MEH++


SEARCH ASP FREE
TOOLS YOU CAN USE

advertisement
What we are going to discuss in this article is Recordset object but you might think why we have chosen to name the article as ActiveX Data Objects (ADO), this is because you must have an idea of ADO technology in order to understand the bits and bytes of Recordset object So, we will briefly describe the ADO technology before jumping into the details of Recordset object. The intended audience of this article includes VB as well as ASP programmers, you must bear it in your mind that you can get more detailed information about the ADO or any related technology on the Microsoft® site, the only purpose of this article is to describe the new technology in simple and plain English so that more people can access and read this article.

I have noticed that many new programmers prefer to search sites other than the Microsoft® site for the help on different newer technologies, may be because these articles are more easily available to the programmers and they don’t have to pay for these articles, moreover, the sample code is provided free of cost unlike Microsoft® where only registered users can access the code related to the newest technologies. So, instead of digging deep into the useless details of why this article was written, let’s see, what’s so important about the ADO technology. ActiveX® Data Objects ADO is the object-based interface that provides a logical set of objects you can access from code. These objects are:
ObjectFunctionality
ConnectionManages the connection with the data source
CommandDefines the commands that will be executed against the data source
RecordsetContains the data that is retrieved from the data source

These objects present an interface in the form of properties and methods that can be queried and manipulated. ADO was specifically developed to be small, lightweight, fast, and feature complete - everything you need when you are programming either for the database applications or the Internet.

An important thing in ADO is that the objects in this model are not dependant on one another. This means that you can create instances of objects independently of one another, for example, you can create a Recordset object without creating a connection object. Unlike the older technologies, ADO is more flexible, ADO code is easier to write, read and maintain. ADO is built on top of OLE DB and is capable of accessing any sort of data that is wrapped and exposed by an appropriate OLE DB provider.
Connectionless Recordsets

Connectionless recordsets, persistent recordsets, creatable recordsets, call it whatever you want, all these names refer to the same object and that is ADO Recordset object. The most important feature provided by the ADO is the introduction of the principle that recordsets are creatable objects. With ADO you can access any sort of structured data. Recordset is the most used object in the ADO object library, it is used to temporarily store the set of records (known as recordset) that is returned by a SQL query. Recordsets have a cursor that indicates the current pointer position within the recordset. Whenever you employ ADO, you are using the recordsets to carry data back and forth. Recordsets always contains data, but this data does not necessarily match a table’s records.

Note that connectionless recordset is not same as the disconnected recordset. Making the recordset structure externally creatable means that you can create a new recordset object anytime and anywhere in your code, and you can use it without a connection to a database. A disconnected recordset supports a static, client-side cursor that automates downloading the records on the client side. You can have disconnected recordsets with RDO but you can’t have connectionless recordsets.

A connectionless recordset is a recordset whose fields have been defined on the fly by the application to match the structure of the information you want it to manage. Previously this capability was reserved for the data object model such as ADO 1.x, RDO, or DAO.

For reader’s convenience, we are including here an example that demonstrates the display of data with a Recordset. There are two ways to do this, one is to create a connection object and then create a recordset object, and the other way is to create a recordset object without explicitly creating a connection object. Both ways are demonstrated below:

DISPLAYING DATA WITH A RECORDSET (USING A CONNECTION OBJECT)

Recordset is quite useful in real world applications. If anything, there are too many ways to do the same thing. The example below uses an explicit Connection object and is written to be used in Active Server Pages.

Set conn = server.createobject("ADODB.Connection")
Set objRec = server.createobject("ADODB.Recordset")

Conn.open "DSN=myDB;UID=sa;Password=;"
objRec.ActiveConnection = conn
objRec.open "select * from table1"

while not objRec.EOF
Response.write objRec("fname") & " "
Response.write objRec("Address")  & " "
ObjRec.MoveNext
Wend

ObjRec.Close
Conn.Close

DISPLAYING DATA WITH A RECORDSET (WITHOUT  A CONNECTION OBJECT)

StrConnect = "DSN=myDB;UID=sa;Password=;"

Set objRec = server.createobject("ADODB.Recordset")
objRec.Open "select * from table1", strConnect, adopenkeyset, adlockoptimistic

while not objRec.EOF
Response.write objRec("fname") &
Response.write objRec("Address") & " "
objRec.MoveNext
wend

objRec.Close

To use the above code in Visual Basic, simply change the syntax of the statement in which the objects are created like replace "server.createobject" with "createobject", the above syntax is specific to the ASP only.

Now that you have seen the examples demonstrating the usage of Recordset objects with the database, let’s concentrate on the issue of connectionless recordsets or rather should I say "Creatable Recordsets". Below is shown the code that creates a brand new recordset that has no relationship to an OLE DB data source. The code generates a recordset that reads drive information through the FileSystem Scripting Object. So, you will learn not only how to create a new recordset (connectionless recordset) but also, how to use the FileSystem Scripting Object. The code shown below is written in VBScript. The ASP version is also provided with this article. See the related documents.

CODE

'============================================================
'Name: ConnectionlessRS.vbs
'Description: Shows a connection less recordset with it’s own fields added.
'============================================================

'Constants
Const adUseClient = 3
const adCurrency = 6
const adBSTR = 8

'Local Variables
dim fso, rst, drives

'Creates the main objects of the script
set fso = CreateObject("scripting.filesystemobject")
set rst = CreateObject("ADODB.Recordset")

'Gets the collection of available drives
set drives = fso.Drives

'Prepares the Recordset structure: Root, Volume, Type
'FileSystem, FreeSpace

rst.CursorLocation = aduseclient
rst.Fields.Append "Root", adBSTR
rst.Fields.Append "Volume", adBSTR
rst.Fields.Append "Type",adBSTR
rst.Fields.Append "FileSystem",adBSTR
rst.Fields.Append "FreeSpace",adCurrency
rst.Open

'Fills the recordset out with drive information
for each drv in drives
rst.AddNew
if drv.isready then
rst.Fields("Root").value = drv.DriveLetter
rst.Fields("Volume").value = drv.VolumeName
rst.fields("Type").value = drv.DriveType
rst.Fields("FileSystem").value = drv.FileSystem
rst.Fields("FreeSpace").value = drv.FreeSpace/1024
end if
next

'Displays the recordset
s = ""
rst.movefirst
while not rst.EOF
for each fld in rst.Fields
s = s & Pad(rst.Fields(fld.name),14) & vbtab
next
s = s & vbcrlf
rst.MoveNext
wend

msgbox s

'============================================================
'Pad(str, numChars)
'Pads the specified string with the specified trailing blanks
'============================================================
Function Pad(str, numChars)
str = str & space(numChars)
Pad= Left(str,numchars)
end function

CODE DETAILS

Two objects are used in this code, one is the FileSystem Scripting Object and the other is the Recordset object. Drives property of the FileSystem Object is used to get the collection of all the drives in your computer. The next step is to create new fields of your recordset object. We have used the client side cursor during the process. Append property of the fields collection is used to add new fields in the recordset. Once the fields are appended, we scroll through the drives collection and add new record against each drive, each record contains information about the individual drive. Lastly, we display the drive information using the msgbox function. Trailing blanks are added to the strings simply to display the information more clearly on the screen. To display the same information in ASP, we use Response.Write method while navigating through the recordset. Also, note that to use the above code in ASP, you will have to create the objects using the "Server.CreateObject()" method.


Contact Us

Contact us for any help regarding the article or sample code, our email address is:

tarhoni@hotmail.com


blog comments powered by Disqus
ASP CODE ARTICLES

- ASP Forms
- ASP: The Beginning
- Getting Remote Files With ASP Continued
- Inbox and Outbox Manipulation in ASP
- Relational DropDownList Using VB.NET
- Ad Tracking URL Hits
- Use ViewState to display one record per page...
- Send Email using ASP.NET formatted in HTML
- ASP File Explorer
- ASP/XML Interview questions by Srivatsan Sri...
- Pressing RETURN won't submit the form
- This shows how you get the TEXT of a combo r...
- Group Data by Adrian Forbes
- Multiple checkbox select sample
- Multiple checkbox select with all values sam...

ASP Web Hosting ASP.Net Web Hosting Windows Web Hosting
 
 
 

ASP Free Forums 
 RSS  Tutorials RSS
 RSS  Forums RSS
 RSS  All Feeds
Site Map 
Request Media Kit
Write For Us Get Paid 
Weekly Newsletter
 
Developer Updates  
Free Website Content 
Privacy Policy 
Support 


© 2003-2012 by Developer Shed. All rights reserved. DS Cluster 5 - Follow our Sitemap
Most Popular Topics
All ASP.Net Tutorials