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:

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.
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.
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:

As you can see from the above code, our RDC object is declared using 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

for each column. Within the

tag.

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.

devx-admin

Share the Post:

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

devx-admin

Westinghouse Builds Polish Power Plant

EV Industry Hurting For Skilled Labor

Soaring EV Quotas Spark Battle Against Time

Cybersecurity Crisis: Breach Exposes Sensitive Customer Data

Tesla Rivals Make Bold Moves

Inside the Climate Battle: Make Sunsets’ Technique

Westinghouse Builds Polish Power Plant

EV Industry Hurting For Skilled Labor

Soaring EV Quotas Spark Battle Against Time

Cybersecurity Crisis: Breach Exposes Sensitive Customer Data

Tesla Rivals Make Bold Moves

Inside the Climate Battle: Make Sunsets’ Technique

AI Algorithm Predicts Treatment Adherence

Here’s Why You Need to Use JavaScript and Cookies

Scientists Dimming the Sun: It’s a Good Thing

The Top Reasons Why Startups Succeed

Intel’s Bold Comeback

Semiconductor Slump: Rebound on the Horizon

Elevate Your Content Creation with Amazing Deals

An Easy Way to Learn Web Security

Military Drones: New Mobile Command Centers

US and Vietnam: The Next Tech Leaders?

Score Massive Savings on Portable Gaming

Unbreakable: Cloudflare One Data Protection Suite

Cool Drone Tech Unveiled at London Event

Disrupting Electronics with 2D Semiconductors

Cisco Cuts Jobs To Optimize Growth

FAA Approves Drone Deliveries

Prop-Tech Firms Face Mortgage Rate Challenges

Microsoft 365 Lighthouse: Powerful Updates

Mysterious Website Blockage Sparks Concern