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:
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.
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.
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.
<OBJECT classid="clsid:BD96C556-65A3-11D0-983A-00C04FC29E33"
ID=ADC HEIGHT=1 WIDTH = 1>
</OBJECT>
<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.
<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.
<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.
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).
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.
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
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.