Displaying Recordset Data in a Paged Fashion, Part IIIb: Using Remote Data Services

Displaying Recordset Data in a Paged Fashion, Part IIIb: Using Remote Data Services

ast month, we saw how we could use the Tabular Data Control (TDC) to display recordset data in a paged fashion in Internet Explorer. The TDC is useful when you want to display static lists that are stored as text files on your Web server?for example, price lists, contact lists, phone directories, etc.

This month, we are going to be looking at Microsoft’s Remote Data Services. As you will see, a number of steps that we used last month are identical using this technique. I have repeated some of the steps so that you don’t have to look up last month’s Solution each time.

Microsoft’s Remote Data Services (RDS) is a component that ships with the Microsoft Data Access Components (MDAC), version 2.0. RDS provides a nearly identical functionality to the TDC but with one major difference. RDS can be used to access live data from a database and send a recordset object back to the browser where it can then be manipulated. Thus, you can send a recordset object to the browser and enable a paging mechanism where the user navigates from page to page without making a round trip back to the server for the data.

In order to use RDS, remember:

  1. To use RDS, you need to install RDS as a component on your Web server. This is normally installed on IIS as part of the Windows NT option pack. This process installs two special components on the Web Server?the RDS DataFactory object and the RDS DataSpace object. Both of these are COM components that are invoked by the RDS data control that you will be using in your HTML page.
  2. On your Web site, a virtual directory called MSADC must exist on the root of each Web site. This virtual directory must point to the Program FilesCommon FilesSystemMSADC folder. The permissions of this folder must be set to Execute (Including Script) (Internet Information Server [IIS] 4.0) or Scripts and Executables (IIS 5.0). The MSADC virtual directory must exist as a subdirectory of any Web site that uses RDS.
  3. On your Web server, make sure you have an ODBC System DSN created with a specific user name and password to access your database. Make this user’s permissions as restrictive as possible?that is, if all you are interested in is for this user to read data from a specific table within the database, then make sure that this is all the permissions the user has. If you are using Access as your database, make sure you create a new user with very little access permissions, and not use the default Admin user. If you are using SQL Server or Oracle, create a new user login with very restrictive permissions. We will see later why we need to make sure the user has very restrictive permissions. This solution does not provide details on how to set up users and access permissions on your database. Consult the documentation on your database or contact your database administrator for help in this area.

I am going to assume that you have the above set up on your Web server. For my demonstration, I am going to use the NorthWind database that ships with SQL Server 7, which is a duplicate of the Access NorthWind database. I have created a new user login in my server called ‘asppro’ and have given access permissions to only one table ‘Customers’ within the Northwind database. This is because we are only going to do a read of data from that table for this solution.

Using the Remote Data Control on Your Page
If you use an ActiveX control in your Web pages, you need to provide information about the control as well as information about where the control can be found for downloading. The user is usually prompted with a dialog box before the ActiveX control is downloaded. The control then needs to be installed before the Web page can use it. All this makes the use of ActiveX controls on Web pages less appealing. However, what if you could use ActiveX controls that are built into the browser itself? Then there is nothing to download, and your Web page simply starts using the control.

When you install Internet Explorer on your machine, it silently installs a number of different ActiveX controls. These are lightweight controls that are similar to the common controls that ships with Windows?the treeview control, the listview control, etc?that are used by Windows Explorer.

One of the ActiveX controls that is automatically installed on your machine by IE is the Remote Data Control (RDC). The RDC acts as an invisible container that holds a set of records on your Web page that you can manipulate within your code. If you have programmed with Visual Basic, the RDC is very similar to the Visual Basic Data Control, except it is not visible. Like the Visual Basic Data Control, you can manipulate it using your code?you can move from record to record and access its data.

Consider the following scenario: suppose you had a list of data you wanted to display on your Web page; for example, a list of customer information. You could display the list as rows and columns using the HTML

structure. For each row in the list, you would require one set of tags, and for each column in the list, you would need a pair. If you had a hundred rows in the list, you would need to code a hundred row tags for your HTML table. The RDC allows you to do it with a single row tag. In addition, it enables you to provide navigation capabilities.

Let’s take a look at the RDC in action. To use the RDC, you need to follow three steps (two of which are identical to the steps used with the TDC): declare the RDC object and set its data source, display the data from the RDC using an HTML construct like a table or a text box, and initiate the data download by using client-side scripting.

1. Declare the RDC Object and Set Its Data Source
To declare the RDC object, use the following code:

ID=objRDC WIDTH=1 HEIGHT=1>  VALUE="http://www.ct.org">  

As you can see from the above code, our RDC object is declared using the tag. Its name (given by the ID attribute) is ‘objRDC’. The Web browser knows which ActiveX control this refers to by its Class ID (a GUID – Globally Unique Identifier). When the browser parses this piece of code, it looks in the Windows registry to locate the Class ID. Based on that, it tracks down the fact that the ActiveX control is located within the file ‘MSRDC20.ocx’ installed by IE on your machine. It then loads the RDC within your Web page. No downloads needed, no user prompting. The CLASS ID for the RDC is a fixed value and you cannot change it.

Within the RDC declare, we specify two parameters?the server and the connection data. We specify the server on which the RDC will find its counterpart components and from which data can be extracted by using the SERVER parameter.


We then specify the connection string to access the database on the server. This can be a standard ODBC connection string or an OLEDB connection string. In our case, we are specifying the DSN name, the user name and the password. (Yes, the password is going to be visible within the HTML file on the browser!)


2. Display the Data From the RDC Using an HTML Construct Like a TABLE Or a Text Box.
Suppose you want to display this list in the form of a table. You would normally use code like that shown in Listing 1 to display the table. I have just shown the first three rows out of about 100 rows in our list.

As you know, this code will result in a three-row table with a header row at the top. To use the data from the RDC however, we will need to write only one row for the data and one row for the headings. See Listing 2.

We first begin by declaring the

tag and informing the browser that the data for the table is going to be provided by our RDC object (objRDC). We do this by using the DATASRC (Data Source) attribute:

The pound (#) character before the name of the RDC tells the browser that the data source is declared within the same HTML file.

The table is then split into two sections,

and . The section contains the headings?these are hard coded, one section, we code a single row using a single pair. For each column of data we wish to display, we use a tag pair. Within the cell, the TD, we use the HTML container tag
to place our data. We use the DATAFLD (Data Field) attribute to point to the field name within our list that will populate this cell.

3. Initiate the Data Download Using Client-Side Scripting.
As of now, we have a place to hold the data (the RDC) and we have a place to display the data (our Table). Now we are ready to actually execute a SQL statement and download the live data from the remote database. We do this by using client-side scripting.

We are going to download all the data from the table ‘Customers’. Our SQL statement will therefore be ‘Select * from Customers’. We could have been more specific and only downloaded the columns we need?but remember, this is only supposed to be a 10-Minute solution!

We then write a piece of JavaScript client-side function to initiate the download. To do this, we access the RDC and invoke its SQL property to set the SQL statement to execute. Then we initiate the action by calling the Refresh method.

We want the above script to be executed when the browser has finished loading the HTML on the page. Thus, we hook the function to the BODY tag’s ONLOAD attribute:

And voila! When the page loads, the browser magically fills in all the columns for each row of the list.

If you had 500 rows in your list, you would get a 500-row table automatically as shown in Figure 1. This was produced using Listing 3. I added a stylesheet to make the table look neater.

(Note: It is not necessary to use client-side scripting to initiate the download separately. You could have added the SQL parameter to the tag that declared the RDC in addition to the SERVER and CONNECT parameters and provided your SQL select statement within it. However, if your HTML page has lots of data unrelated to the RDC recordset, it is better to wait for the page to download and then invoke the data download.)

If you have been following all along, you will notice that there is no page navigation involved in this process. The Web browser simply dumps all the contents of the list on to the page?if there were 500 rows, you get all 500 rows, period. The next step is to ensure that the user can see only 20 rows at a time and allow for page navigation. To do that, we make use of some of the properties of a table as well as some methods of the RDC.

To make sure the table displays only 20 records at a time, we use the DATAPAGESIZE attribute of the

for each column. Within the

And lo and behold, now the page displays data from the RDC but only the first 20 rows as shown in Figure 2.

In order to provide page navigation, we need to manipulate the table by code. We simply use the NextPage, PreviousPage, FirstPage and LastPage methods of the table to move from page to page. To begin, provide a name for the table so that you can access it from client-side script. Use the ID attribute to provide a name.

Once you have the table named, you can create four buttons to help the user navigate.

We use four buttons to help the user navigate. All you are doing is invoking the appropriate method to move to the next or previous pages. The results of this coding can be quite amazing as you will see if you take a look at Figure 3. Remember, for each page that is made visible, the browser is not making a round trip to the server?it is loading the data from the local recordset in its memory. The code that produces this page is in Listing 4.

To recap what we did with the Remote Data Control, click here for the steps that you and the browser undertook to achieve your results.

This 10-Minute Solution did not explain the details of the RDS architecture. If you want to know more, check out the documentation at the ADO section of the MSDN Library.

Beyond Page Navigation, Sorting and Filtering
In addition to providing page navigation by using the TABLE’s NextPage, PreviousPage methods, the RDC itself provides sorting and filtering capabilities. To sort the data in the recordset, you simply use the ‘SortColumn’ property to set the fields to sort by and follow it up with the reset method to repaint the screen.

objRDC.sortColumn = 'CustomerName'objTDC.reset();

To see this in action, let us modify the code in our previous example. In place of column headers as plain text, we will make the column headers links so that clicking on the link will sort the data by that column. All we need to do is change the code that displays the headings from Listing 5.As you can see, each heading is now wrapped around a call to a JavaScript function called ‘SortBy’. Each call passes the name of the field to sort by.

Later on in the page, we have defined the JavaScript function that simply uses the sort property and reset method of the TDC to perform its task:

And voila! You have instant sorting on your table. Take a look at this in action in Figure 4. Remember that this code is different than the code we used in the TDC example. The TDC supports the Sort property. Within the sort property, a plus sign (+) indicates sort direction. The Remote Data Control uses the SortColumn property to indicate the name of the column to sort by. In addition, it uses a Boolean SortDirection property to indicate ascending or descending sorting.

Filtering is achieved by using a similar technique. To use filtering, you need to set the FilterColumn, FilterCriterion, and FilterValue properties, and then use the reset method to initiate the filter and repaint the screen. So, to initiate a filter such as “field1=value1”, you would need to set FilterColumn = “field1”, FilterCriterion = “=”, and FilterValue = “value1”.

Check the MSDN online documentation for excellent examples on manipulating the Remote Data Control.

Caveat Programmer!
Before you open the floodgates to your database and provide everybody access using the Remote Data Services, understand the issues involved. You are exposing the connection string and possibly user name and password information to your users. Any user smart enough to do a ‘View Source’ on your HTML page can read the user id and password being used. If you use the same user id and password everywhere, you are handing it out to a hacker on a silver platter. Microsoft has taken steps to prevent unauthorized use of the RDS by enforcing stricter security rules with the latest version of MDAC, but that assumes you have the latest version on your Web server.

I can guarantee that when you use RDS the very first time, it will not work! I had to go through a number of iterations to get all the pieces in place and working. So don’t give up.

Here are some additional links to help you troubleshoot and solve your RDS problems, if you have any:

Apart from that, what this Solution showed you is another technique to transport a recordset object to the client browser and offload all manipulations to the client, leaving your server free.



Share the Post:
Poland Energy Future

Westinghouse Builds Polish Power Plant

Westinghouse Electric Company and Bechtel have come together to establish a formal partnership in order to design and construct Poland’s inaugural nuclear power plant at

EV Labor Market

EV Industry Hurting For Skilled Labor

The United Auto Workers strike has highlighted the anticipated change towards a future dominated by electric vehicles (EVs), a shift which numerous people think will

Soaring EV Quotas

Soaring EV Quotas Spark Battle Against Time

Automakers are still expected to meet stringent electric vehicle (EV) sales quotas, despite the delayed ban on new petrol and diesel cars. Starting January 2023,

Affordable Electric Revolution

Tesla Rivals Make Bold Moves

Tesla, a name synonymous with EVs, has consistently been at the forefront of the automotive industry’s electric revolution. The products that Elon Musk has developed

Poland Energy Future

Westinghouse Builds Polish Power Plant

Westinghouse Electric Company and Bechtel have come together to establish a formal partnership in order to design and construct Poland’s inaugural nuclear power plant at the Lubiatowo-Kopalino site in Pomerania.

EV Labor Market

EV Industry Hurting For Skilled Labor

The United Auto Workers strike has highlighted the anticipated change towards a future dominated by electric vehicles (EVs), a shift which numerous people think will result in job losses. However,

Soaring EV Quotas

Soaring EV Quotas Spark Battle Against Time

Automakers are still expected to meet stringent electric vehicle (EV) sales quotas, despite the delayed ban on new petrol and diesel cars. Starting January 2023, more than one-fifth of automobiles

Affordable Electric Revolution

Tesla Rivals Make Bold Moves

Tesla, a name synonymous with EVs, has consistently been at the forefront of the automotive industry’s electric revolution. The products that Elon Musk has developed are at the forefront because

Sunsets' Technique

Inside the Climate Battle: Make Sunsets’ Technique

On February 12, 2023, Luke Iseman and Andrew Song from the solar geoengineering firm Make Sunsets showcased their technique for injecting sulfur dioxide (SO₂) into the stratosphere as a means

AI Adherence Prediction

AI Algorithm Predicts Treatment Adherence

Swoop, a prominent consumer health data company, has unveiled a cutting-edge algorithm capable of predicting adherence to treatment in people with Multiple Sclerosis (MS) and other health conditions. Utilizing artificial

Personalized UX

Here’s Why You Need to Use JavaScript and Cookies

In today’s increasingly digital world, websites often rely on JavaScript and cookies to provide users with a more seamless and personalized browsing experience. These key components allow websites to display

Geoengineering Methods

Scientists Dimming the Sun: It’s a Good Thing

Scientists at the University of Bern have been exploring geoengineering methods that could potentially slow down the melting of the West Antarctic ice sheet by reducing sunlight exposure. Among these

why startups succeed

The Top Reasons Why Startups Succeed

Everyone hears the stories. Apple was started in a garage. Musk slept in a rented office space while he was creating PayPal with his brother. Facebook was coded by a

Bold Evolution

Intel’s Bold Comeback

Intel, a leading figure in the semiconductor industry, has underperformed in the stock market over the past five years, with shares dropping by 4% as opposed to the 176% return

Semiconductor market

Semiconductor Slump: Rebound on the Horizon

In recent years, the semiconductor sector has faced a slump due to decreasing PC and smartphone sales, especially in 2022 and 2023. Nonetheless, as 2024 approaches, the industry seems to

Elevated Content Deals

Elevate Your Content Creation with Amazing Deals

The latest Tech Deals cater to creators of different levels and budgets, featuring a variety of computer accessories and tools designed specifically for content creation. Enhance your technological setup with

Learn Web Security

An Easy Way to Learn Web Security

The Web Security Academy has recently introduced new educational courses designed to offer a comprehensible and straightforward journey through the intricate realm of web security. These carefully designed learning courses

Military Drones Revolution

Military Drones: New Mobile Command Centers

The Air Force Special Operations Command (AFSOC) is currently working on a pioneering project that aims to transform MQ-9 Reaper drones into mobile command centers to better manage smaller unmanned

Tech Partnership

US and Vietnam: The Next Tech Leaders?

The US and Vietnam have entered into a series of multi-billion-dollar business deals, marking a significant leap forward in their cooperation in vital sectors like artificial intelligence (AI), semiconductors, and

Huge Savings

Score Massive Savings on Portable Gaming

This week in tech bargains, a well-known firm has considerably reduced the price of its portable gaming device, cutting costs by as much as 20 percent, which matches the lowest

Cloudfare Protection

Unbreakable: Cloudflare One Data Protection Suite

Recently, Cloudflare introduced its One Data Protection Suite, an extensive collection of sophisticated security tools designed to protect data in various environments, including web, private, and SaaS applications. The suite

Drone Revolution

Cool Drone Tech Unveiled at London Event

At the DSEI defense event in London, Israeli defense firms exhibited cutting-edge drone technology featuring vertical-takeoff-and-landing (VTOL) abilities while launching two innovative systems that have already been acquired by clients.

2D Semiconductor Revolution

Disrupting Electronics with 2D Semiconductors

The rapid development in electronic devices has created an increasing demand for advanced semiconductors. While silicon has traditionally been the go-to material for such applications, it suffers from certain limitations.

Cisco Growth

Cisco Cuts Jobs To Optimize Growth

Tech giant Cisco Systems Inc. recently unveiled plans to reduce its workforce in two Californian cities, with the goal of optimizing the company’s cost structure. The company has decided to

FAA Authorization

FAA Approves Drone Deliveries

In a significant development for the US drone industry, drone delivery company Zipline has gained Federal Aviation Administration (FAA) authorization, permitting them to operate drones beyond the visual line of

Mortgage Rate Challenges

Prop-Tech Firms Face Mortgage Rate Challenges

The surge in mortgage rates and a subsequent decrease in home buying have presented challenges for prop-tech firms like Divvy Homes, a rent-to-own start-up company. With a previous valuation of

Lighthouse Updates

Microsoft 365 Lighthouse: Powerful Updates

Microsoft has introduced a new update to Microsoft 365 Lighthouse, which includes support for alerts and notifications. This update is designed to give Managed Service Providers (MSPs) increased control and

Website Lock

Mysterious Website Blockage Sparks Concern

Recently, visitors of a well-known resource website encountered a message blocking their access, resulting in disappointment and frustration among its users. While the reason for this limitation remains uncertain, specialists

©2023 Copyright DevX - All Rights Reserved. Registration or use of this site constitutes acceptance of our Terms of Service and Privacy Policy.