Tutorial: Building a simple Remote Data Services application

You can use the AdvancedDataControl object to create a simple Active Server Pages file that accesses a live database — and you don't have to write a lot of code to do it. You can also use it with ActiveX™ Data Objects to create robust applications that give you a high degree of programming control over the behavior of your application's data.

This tutorial takes the "minimal code" approach, and the Developer's Guide provides more information for using the AdvancedDataControl with ActiveX Data Objects and other advanced features. In this tutorial, you will connect to a Microsoft® Access database, display a resultset in a data-bound grid, and add the ability to navigate through the displayed recordset. The following procedure outlines the steps you'll take to create this simple application:

  1. Identify the database.

  2. Insert the grid and the AdvancedDataControl object.

  3. Add HTML controls.

  4. Add code to send a query to the database.

  5. Add code to submit changes to the database.

  6. Add code to move through the displayed recordset.

  7. See the code in action.

If you want to see a finished version of this sample application, you can go to http://<webservername>/MSADC/Samples/ADCTest.asp, where <webservername> is the name of your Web server.

Identify the Database

Before you create your .asp file, you must identify the database in the ODBC applet in Control Panel. In this example, you will use a Microsoft Access database that is provided with Remote Data Services.

  1. At the computer running your Web server, open Control Panel.

  2. Double-click the ODBC icon, and then click System DSN.

    There are two types of data sources: User, which is available only to you, and System, which is available to anyone using the computer. Data sources for use with the Web server need to be System data sources.

  3. Click Add, choose Microsoft Access Driver, and then click Finish.

  4. Specify the data source name. In the Data Source Name box, type AdvWorks, and click Select. Select the C:\Program Files\Common Files\System\MSADC\Samples\AdvWorks.mdb file, and click OK.

  5. Click OK to close the dialog boxes.

Insert the Grid and the AdvancedDataControl Object

  1. Use your text editor to open the Tutorial.asp file in the C:\Program Files\Common Files\System\MSADC\Samples\Tutorial folder.

  2. Search for the words "Tutorial: AdvancedDataControl Object." Copy the following script and paste it under this comment.
    <OBJECT classid="clsid:BD96C556-65A3-11D0-983A-00C04FC29E33"
        ID=ADC HEIGHT=1 WIDTH = 1>
    </OBJECT>
  3. Search for the words "Tutorial: Data Bound Grid Control." Copy the following script and paste it under this comment.
    <BR>
    <OBJECT ID="GRID" WIDTH=600 HEIGHT=200 Datasrc="#ADC"
     CODEBASE="http://<%=Request.ServerVariables("SERVER_NAME")%>/MSADC/Samples/sheridan.cab"
     CLASSID="CLSID:AC05DC80-7DF1-11d0-839E-00A024A94B3A">
    	<PARAM NAME="_Version"	    VALUE="131072">
    	<PARAM NAME="BackColor"     VALUE="-2147483643">
    	<PARAM NAME="BackColorOdd"  VALUE="-2147483643">
    	<PARAM NAME="ForeColorEven" VALUE="0">	
    </OBJECT>
    <BR>

    The DATASRC parameter in the grid control's OBJECT tag indicates that the data source will be the AdvancedDataControl object ADC. This binds the grid to the returned recordset and causes the grid to display data when the AdvancedDataControl gets a recordset. The AllowAddNew, AllowDelete, and AllowUpdate parameters enable the user to add, edit, and delete records displayed through the grid.

Add HTML Controls

  1. Search for the words "Tutorial: HTML Controls to Get Recordsets." Copy the following script and paste it under this comment.
    <BR>
    <table>
    	<tr><td>ADC Server:<td><INPUT NAME=Server SIZE=70>
    	<tr><td>Connection:<td><INPUT NAME=Connect SIZE=70>
    	<tr><td>Query:<td><INPUT NAME=SQL SIZE=70>
    </table>

    This adds some text boxes for users to specify an SQL query to send to a database (specified in the data source name) that will return a disconnected resultset to the client.

  2. Search for the words "Tutorial: HTML Controls to Navigate the Recordset and Run the Query." Copy the following script and paste it under this comment.
    <BR>
    <BR>
    <INPUT TYPE=BUTTON NAME="First" VALUE="First" onClick="MoveFirst">
    <INPUT TYPE=BUTTON NAME="Next" VALUE="Next" onClick="MoveNext">
    <INPUT TYPE=BUTTON NAME="Prev" VALUE="Previous" onClick="MovePrev">
    <INPUT TYPE=BUTTON NAME="Last" VALUE="Last" onClick="MoveLast">
    <INPUT TYPE=BUTTON NAME="Run" VALUE="Run!" onClick="Requery">
    <INPUT TYPE=BUTTON NAME="Submit" VALUE="Submit Changes" >
    </CENTER>

    The First, Next, Previous, and Last buttons will eventually provide a mechanism for users to navigate through the displayed recordset.

Add Code to Send a Query to the Database

Search for the words "Tutorial: Send a Query to the Database." Copy the following script and paste it under the comment.

SUB Init
	Server.Value = "http://<%=Request.ServerVariables("SERVER_NAME")%>"
   	Connect.Value = "DSN=AdvWorks"
   	SQL.Value = "Select * from Products"
END SUB

SUB Requery
	ADC.Server = Server.Value
	ADC.Connect = Connect.Value
	ADC.SQL = SQL.Value

	ADC.Refresh
END SUB

Note When you specify the data source through the Connection property, you will usually provide a data source name, user ID, and password (for example, "DSN=SalesDB;UID=Manager;PWD=secret;"). With Microsoft Access databases, you don't need to specify a user ID (UID) or password (PWD).

Add Code to Submit Changes to the Database

Search for the words "Tutorial - Submit Changes to the Database." Copy the following script and paste it under the comment.

SUB Submit_OnClick  ' Submit Changes button clicked.
  ADC.SubmitChanges ' Send changes to the DBMS.
  ADC.Refresh	     ' Update the recordset.
End Sub

When ADC.SubmitChanges executes, Remote Data Services packages all the update information and sends it to the server via HTTP. The update is all-or-nothing; if a part of the update isn't successful, none of the changes are made, and a status message is returned.

Add Code to Move through the Displayed Recordset

You can use the Move methods with the AdvancedDataControl object to navigate through the data records displayed in the data-bound controls on a Web page. For example, suppose you display a recordset in a grid by binding to an AdvancedDataControl object. You can then include First, Last, Next, and Previous buttons that users can click to move to the first, last, next, or last record in the displayed recordset. You do this by calling the MoveFirst, MoveLast, MoveNext, and MovePrevious methods of the AdvancedDataControl object in the OnClick procedures for the First, Last, Next, and Previous buttons, respectively.

Search for the words "Tutorial: Move Through the Displayed Recordset." Copy the following script and paste it under the comment.

SUB MoveFirst
	ADC.Recordset.MoveFirst
END SUB

SUB MoveNext
	On Error Resume Next
	ADC.Recordset.MoveNext
	IF ERR.Number <> 0 THEN
		'If already at end of recordset stay at end.
		ADC.Recordset.MoveLast  
	END IF
END SUB

SUB MovePrev
	On Error Resume Next
	ADC.Recordset.MovePrevious
	IF ERR.Number <> 0 THEN
		'If already at start of recordset stay at top.
		ADC.Recordset.MoveFirst  
	END IF
END SUB

SUB MoveLast
	ADC.Recordset.MoveLast
END SUB

See the Code in Action

  1. Save the changes to the ADCTest.asp file as a text file and exit your text editor. Be sure your text editor does not replace the .asp file name extension. An .asp file is an Active Server Pages script file.

  2. To verify the ASP page you've created works, point your browser to http://<webservername>/MSADC/Samples/Tutorial/Tutorial.asp, where <webservername> is the name of your Web server.

Note You must access your ASP page by pointing to the address. It will not open correctly if you use the Open command on the File menu, or double-click the .asp file.


© 1997 Microsoft Corporation. All rights reserved. Terms of Use.