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: | Object | Functionality | | Connection | Manages the connection with the data source | | Command | Defines the commands that will be executed against the data source | | Recordset | Contains 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
|
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
More ASP Code Articles
More By aspfree  developerWorks - FREE Tools!
|
This whitepaper presents the benefits of successfully introducing static analysis into your organization using IBM Rational Software Analyzer. Additionally, it identifies some common pitfalls that can hinder the effective use of static analysis tooling as well as presents 10 simple strategies designed to help you quickly realize the value of static analysis using Rational Software Analyzer.
FREE! Go There Now!
| | | |
Visit IBM developerWorks to download a free trial version of Lotus Quickr 8.0, which enables collaboration by transforming the way everyday business content such as documents, rich media, photos, and video can be shared. Lotus Quickr makes it faster and easier to share content of all types (not just documents) within virtual teams. It is designed to make it easier to collaborate across organizational boundaries, while continuing to work within the context of familiar desktop applications.
FREE! Go There Now!
| | | |
Download a free trial version of IBM Rational Developer for System i V7.1, which provides a complete development environment for traditional i5/OS application development. IBM Rational Developer for System i is a new eclipse-based workstation offering for i5/OS application development that provides a comprehensive Integrated Development Environment for edit/compile/debug of traditional RPG/COBOL/C/C++ i5/OS applications.
FREE! Go There Now!
| | | |
Visit IBM developerWorks to download a free trial version of WebSphere Extended Deployment Compute Grid, which lets you schedule, execute, and monitor batch jobs. Because online transaction processing and batch jobs execute simultaneously on the same server resources, you can avoid costly duplication of resources. Compute Grid supports job types of Java transactional batch, compute-intensive and a new type called "native execution", which enables non-Java workloads to run on distributed end points.
FREE! Go There Now!
| | | |
Listen to this webcast to get an overview of Info 2.0 and a technical demo of how to quickly build an enterprise mashup. IBM's Info 2.0 technology leverages emerging Web 2.0 technologies such as mashups, feeds, AJAX, and JSON in order to simplify assembly of information using feeds and services. Come learn about the technical elements of Info 2.0 including the Feed Generation framework, Mashup Engine, and mashup assembly components. Learn how to pull information from databases, departmental information, and the Web to create mashups critical to your company’s success. We will also discuss best practices to help you get started.
FREE! Go There Now!
| | | |
Join this Rational Talks to You teleconference on November 29 at 1:00 pm ET to participate in an interactive discusssion with Grady Booch around architecture and reuse. Get your questions answered!
FREE! Go There Now!
| | | |
This whitepaper provides areas to consider when evaluating any software configuration management solution. It addresses how the IBM solutions (Rational ClearCase and Rational ClearQuest) meet the needs and requirements of both project leaders and developers to provide successful Software Change and Configuration Management.
FREE! Go There Now!
| | | |
Get a free trial download of the latest version of IBM Rational Functional Tester V7.0.1. Rational Functional Tester is an automated functional and regression testing solution for QA teams concerned with the quality of their Java, Microsoft Visual Studio .NET, and Web-based applications.
FREE! Go There Now!
| | | |
Attend this launch webcast with Scott Hebner, Vice President of IBM Rational Marketing and Strategy, where he will overview Rational’s new offerings and programs to help customers accelerate software innovation on System z. He will discuss how these solutions help organizations extend their core business processes toward modern architectures such as SOA and web technologies to deliver business improvements that stand the test of time.
FREE! Go There Now!
| | | |
User communities play an important role in communication and collaboration around products, solutions and other areas of special interest to members. Successful communities are able to provide the right mix of content and services to deliver a value proposition that resonates with each audience. Join Tom Inman, VP of Marketing for Information and Platform Solutions as he introduces the new LeverageINFORMATION community. During this webcast, learn about the value provided by the community and how customers and partners derive value from the community in addressing their own technical and business challenges.
FREE! Go There Now!
| | | |
All FREE IBM® developerWorks Tools!
| |
