Microsoft KB Archive/189406

= How To Populating a FlexGrid Control with ADO and ASP =

Article ID: 189406

Article Last Modified on 3/2/2005

-

APPLIES TO


 * Microsoft Active Server Pages 4.0
 * Microsoft Internet ActiveX Controls 1.0
 * Microsoft ActiveX Data Objects 2.0
 * Microsoft ActiveX Data Objects 2.1 Service Pack 2

-



This article was previously published under Q189406



SUMMARY
The FlexGrid ActiveX control is a powerful control to display and manipulate data from a database in Internet Explorer. This sample uses Visual InterDev's DataCommand Design-time control to access the datasource. The client-side script uses a call to a subroutine to actually build the HTML source code that will add rows and columns to the FlexGrid control before the control is displayed by Internet Explorer.



MORE INFORMATION
To use the Flexgrid ActiveX control in a new .asp script follow these steps:


 * 1) Create a new .asp file for your project in Visual InterDev.
 * 2) Replace the default source code with the ASP script below.
 * 3) Right mouse click on the "DesignerControl" METADATA tag at the top of the script. Select a data connection and build the necessary SQL statement with which to populate the recordset that will be produced at runtime.
 * 4) Save the file and preview in browser.

Additional explanatory comments follow the sample script.

<%@ LANGUAGE="VBSCRIPT" %>

    Document Title

<%  Set MagisterPubs = Server.CreateObject("ADODB.Connection") MagisterPubs.ConnectionTimeout = Session("MagisterPubs_ConnectionTimeout") MagisterPubs.CommandTimeout = Session("MagisterPubs_CommandTimeout") MagisterPubs.Open Session("MagisterPubs_ConnectionString"), Session("MagisterPubs_RuntimeUserName"), Session("MagisterPubs_RuntimePassword") Set cmdTemp = Server.CreateObject("ADODB.Command") Set objRst = Server.CreateObject("ADODB.Recordset") cmdTemp.CommandText = "SELECT authors.* FROM authors" cmdTemp.CommandType = 1 Set cmdTemp.ActiveConnection = MagisterPubs objRst.Open cmdTemp,, 3, 1 %>  <%      Sub FillCells Dim ctRow, ctCol, vbCrLF vbCrLf= Chr(13) & Chr(10) ctCol=0 ' Note: added 1 to rows so that we can print field headings Response.Write "MSFlexGrid1.Rows=" & objRst.RecordCount+1 & vbCrLf Response.Write "MSFlexGrid1.Cols=" & objRst.Fields.Count & vbCrLf

' Print field headings Response.Write "MSFlexGrid1.Row=0" & vbCrLf For Each fld in objRst.Fields Response.Write "MSFlexGrid1.Col=" & ctCol & vbCrLf Response.Write "MSFlexGrid1.Text=" & Chr(34) & Trim(fld.Name) & Chr(34) & vbCrLf ctCol=ctCol+1 Next

' Print data ctRow=1 Do Until objRst.eof Response.Write "MSFlexGrid1.Row=" & ctRow & vbCrLf ctCol=0 For Each fld in objRst.Fields Response.Write "MSFlexGrid1.Col=" & ctCol & vbCrLf Response.Write "MSFlexGrid1.Text=" & Chr(34) & Trim(fld) & Chr(34) & vbCrLf ctCol=ctCol+1 Next

objRst.MoveNext ctRow=ctRow+1 Loop End Sub %>

 

 

   <PARAM NAME="_Version" VALUE="65541"> <PARAM NAME="Rows" VALUE="2"> <PARAM NAME="Cols" VALUE="1"> <PARAM NAME="FixedRows" VALUE="1"> <PARAM NAME="FixedCols" VALUE="0"> <PARAM NAME="AllowUserResizing" VALUE="1"> </OBJECT>

</BODY> </HTML>

This script has four main parts:


 * 1) The Design-time conrol that creates a recordset on the server.
 * 2) ADO source code that runs on the server and writes HTML and VBScript to be used by the client-side script to populate the ActiveX control.
 * 3) The client-side script that calls the "FillCells" subroutine during the window_onload event. This subroutine is the one that actually contains the VBScript that populates the FlexGrid control. The subroutine is called during the windows on_load event to ensure that Internet Explorer has already created an instance of the FlexGrid control.
 * 4) The actual FlexGrid ActiveX control. If you right-mouse click this OBJECT tag the key thing to remember when configuring the FlexGrid control is that the number of rows and columns must exceed the number of Fixed Rows and columns by at least one.

Several of these components interact with each other. For example, the recordset uses a cursor of type 3; viz., a static cursor. Choose your own cursor wisely, but choose one that supports the RecordCount property because the ADO script needs to use the number of records returned by the data source to specify how many rows the FlexGrid control needs to display at runtime.

However, when you also want to print the field names, you need to add one to the recordcount property, otherwise you will raise a runtime error when the client-side script tries to write beyond the size of the buffer it uses to store row values.

This same ADO script is also dependent on the values specified in the FlexGrid control. If this script does not appear in your Internet Explorer with the first row being fixed and displaying the field names from the recordset, then you need to ensure that the FlexGrid control is set with 1 Fixed Row and 0 Fixed Cols. As noted above at item 4, if Fixed Rows are set to 1 then Rows must be at least 2.

Additional query words: FlexGrid ActiveX control ADO CursorType window_onload client-side server-side script

Keywords: kbhowto kbscript KB189406

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.