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!
|
Hear how IBM Rational Project and Portfolio Management integrated solutions help teams put the right tools and processes in place to maximize the effectiveness and efficiency of project teams and ensure that the business vision is being executed correctly. Learn how to automate and integrate requirements prioritization, top-down project planning, communications and controls, and methodology deployment to keep your scope, costs, and schedules under control. Tackle with an end-to-end approach the management of scope and scope changes, usage of methodology to control and empower project teams, and optimization of resources to align activity costs with the overall project plan.
FREE! Go There Now!
| | | |
You probably have thousands of lines of COBOL code loaded with business intelligence and being used to run your business, along with an army of developers maintaining these applications. Learn how to prepare your applications and developers so you can keep that competitive edge and move to a service-oriented architecture with the IBM Rational Enterprise Modernization solutions. Replay is available for 9 months.
FREE! Go There Now!
| | | |
Visit IBM developerWorks to download the latest trial version of IBM Data Studio V1.1 at no cost. IBM Data Studio is a comprehensive data management solution that helps you effectively design, develop, deploy and manage your data, databases, and database applications throughout the data management life cycle utilizing a consistent and integrated user interface. Unlike other client-side data management solutions that focus on only one aspect of the application lifecycle or database administration, Data Studio complements the Rational Software Delivery platform, providing unparalleled flexibility for a heterogeneous data server environment across platforms.
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!
| | | |
Learn how to implement a build management system that uses and extends your existing automation technologies. This tutorial shows, step-by-step, how to install and configure IBM Rational Build Forge to manage builds for Jakarta Tomcat from source code.
FREE! Go There Now!
| | | |
This paper is about the critical role that a discipline called integrated requirements management can play in helping to ensure that your business goals and IT investments are continuously aligned—whether you are sourcing, integrating, building or maintaining software. It also looks at ways that automated IBM Rational® products can work together to help you use requirements in the very best way.
FREE! Go There Now!
| | | |
Get a free trial download of the latest version of IBM Rational Performance Tester V7.0.1, a load and performance testing solution for teams concerned about the scalability of their Web-based applications. Combining multiple ease-of-use features with granular detail, Rational Performance Tester simplifies the test-creation, load-generation and data-collection processes that help teams ensure the ability of their applications to accommodate required user loads.
FREE! Go There Now!
| | | |
Visit IBM developerWorks to try the IBM SOA Sandbox for people. The SOA Sandbox for people provides a trial environment with the necessary tooling and components required to enable consistent human and process interaction and collaboration, showing how you can improve user experience and business productivity.
FREE! Go There Now!
| | | |
Join the IBM Watchfire team for an informative discussion on techniques and best practices to proactively manage Web application security and how to effectively build application security testing into the software development lifecycle (SDLC). In this Software Delivery Platform webcast you will learn: How to better understand potential web application security vulnerabilities, best practices and how to effectively integrate application security testing into the software development lifecycle, the importance of detecting and removing software vulnerabilities during application development.
FREE! Go There Now!
| | | |
With IBM Rational Systems Development Solution, you can deliver products faster with higher quality. Within this kit, Read the “Model Driven Systems Development” white paper to see how to improve product quality and communication. Then check out the rest of the e-Kit to learn more about important topics that can affect the success of any software project through customer examples, tutorials, informative Webcasts, and best practices for designing, building and managing systems. From start to finish, at every stage in your projects, Rational Systems Development Solution can help your company reach its full potential.
FREE! Go There Now!
| | | |
All FREE IBM® developerWorks Tools!
| |
