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
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
for each column. Within the
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
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
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.
Picture this: your team working seamlessly, completing tasks efficiently, and achieving goals with ease. Sounds like too good to be true? Not at all! With our productivity hacks, you can
General Motors (GM) recently announced its entry into the Eclipse Foundation. The Eclipse Foundation is a prominent open-source software foundation. In addition, GMC announced its contribution of “uProtocol” to facilitate
What is metadata? Well, It’s an odd concept to wrap your head around. Metadata is essentially the secondary layer of data that tracks details about the “regular” data. The regular
The earliest cell phones included boxy designs full of buttons and antennas, and they only made calls. Needless to say, we’ve come a long way from those classic brick phones
When it comes to programming, a good mechanical keyboard can make all the difference. Naturally, you would want one of the best mechanical keyboards for programmers. But with so many
In the age of digital transformation, the internet has become a ubiquitous part of our lives. From socializing, shopping, and learning to more sensitive activities such as banking and healthcare,
The world of software development is changing drastically with the introduction of Artificial Intelligence and Machine Learning technologies. In the past, software developers were in charge of the entire development
Cybercriminals constantly adapt their strategies, developing newer, more powerful, and intelligent ways to attack your network. Since security professionals must innovate as well, more conventional endpoint detection solutions have evolved
Artificial intelligence – commonly known as AI – means a form of technology with multiple uses. As a result, it has become extremely valuable to a number of businesses across
Artificial intelligence (AI) has been transforming industries and revolutionizing business operations. AI’s potential to enhance efficiency and productivity has become crucial to many businesses. As we move into 2023, several
Creating a website is not easy, but protecting your website is equally important. Implementing copyright laws ensures that the substance of your website remains secure and sheltered. Copyrighting your website
One of the biggest trends of the 21st century is the massive surge in analytics. Analytics is the process of utilizing data to drive future decision-making. With so much of
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. 
