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!
|
Download a free trial version of IBM Rational Developer for System z, software that can help you deliver core development capabilities; the power of Java Platform, Enterprise Edition (Java EE); and rapid application development support to diverse enterprise application development teams. With comprehensive development tools to help create, deploy and maintain traditional enterprise and composite applications, Rational Developer for System z enables developers with different technical backgrounds to easily participate in important technology projects.
FREE! Go There Now!
| | | |
Join us for this web seminar to learn how you can defend your web applications from attack. Learn about the 3 most common web application attacks, including how they occur and what can be done to prevent them. We’ll also discuss manual versus automated approaches for scanning and identifying web application vulnerabilities and how IBM Rational AppScan, an automated vulnerability scanner, can help you automate more of what you are doing manually today.
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!
| | | |
You can now evaluate IBM Rational Asset Manager V7.0 online without installing or configuring it on your own system! Rational Asset Manager helps create, modify, govern, find, and reuse any type of development assets, including SOA and systems development assets. Rational Asset Manager helps you reduce software development costs and improve quality by facilitating the reuse of all types of software development-related assets. Visit developerWorks to learn more about this product and register to explore its capabilities online.
FREE! Go There Now!
| | | |
David Barnes, Lead Evangelist for IBM Emerging Internet Technologies will discuss aspects of Web 2.0 that bring value to corporations, academia, and government. He'll also discuss IBM's vision around Web 2.0, including the importance of remixability and consumability. The discussion will culminate with examples of various IBM Software Group solutions you can use to get ahead of the Web 2.0 adoption curve.
FREE! Go There Now!
| | | |
As organizations integrate software into every aspect of business, they are constantly pressured to deliver faster, better, and cheaper results. Unfortunately, a “dis-integrated” software delivery approach reduces returns while increasing costs. This IBM Rational White Paper shows how Integrated Requirements Management aligns organizations around maximizing value and keeping pace with change.
FREE! Go There Now!
| | | |
Download the Rational Application Developer (RAD) v7.5 open beta code and start developing applications for the JEE5 standard which features EJB3.0, JPA, JSF 1.2, JSP 2.1 and Servlet 2.5 standards. When you use this beta you will see how you can increase developer productivity for already existing applications with improved support for refactoring, as well as adding new features to existing applications. In addition, the beta provides tooling for JD Edwards, Oracle, SAP, Siebel and PeopleSoft to improve the developer productivity with these enterprise systems.
FREE! Go There Now!
| | | |
The discipline of assembling and delivering software is maturing beyond standard developer-centric compile/test software builds. The end-to-end software development lifecycle is emerging as the new focus moves “Beyond the Build.” Join this on demand webcast to learn about methods for streamlining software delivery and key capabilities of the IBM Rational Build Forge framework for automating build and release management in environments of any size.
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!
| | | |
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!
| | | |
All FREE IBM® developerWorks Tools!
| |
