How To Populating a FlexGrid Control with ADO and ASP

This article was previously published under Q189406
This article has been archived. It is offered "as is" and will no longer be updated.
The FlexGrid ActiveX control is a powerful control to display andmanipulate data from a database in Internet Explorer. This sample usesVisual InterDev's DataCommand Design-time control to access the datasource.The client-side script uses a call to a subroutine to actually build theHTML source code that will add rows and columns to the FlexGrid controlbefore the control is displayed by Internet Explorer.
More information
To use the Flexgrid ActiveX control in a new .asp script follow thesesteps:

  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" %>   <HTML>   <HEAD>   <META NAME="GENERATOR" Content="Microsoft Visual InterDev 1.0">   <META HTTP-EQUIV="Content-Type" content="text/html; charset=iso-8859-1">   <TITLE>Document Title</TITLE>   <!--METADATA TYPE="DesignerControl" startspan      <OBJECT ID="objRst" WIDTH=383 HEIGHT=24       CLASSID="CLSID:7FAEED80-9D58-11CF-8F68-00AA006D27C2">         <PARAM NAME="_Version" VALUE="65536">         <PARAM NAME="_Version" VALUE="65536">         <PARAM NAME="_ExtentX" VALUE="10125">         <PARAM NAME="_ExtentY" VALUE="635">         <PARAM NAME="_StockProps" VALUE="0">         <PARAM NAME="DataConnection" VALUE="MagisterPubs">         <PARAM NAME="CommandText" VALUE="SELECT authors.* FROM authors">         <PARAM NAME="CursorType" VALUE="3">      </OBJECT>   -->   <%   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   %>   <!--METADATA TYPE="DesignerControl" endspan-->   <%      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   %>      <SCRIPT LANGUAGE="VBScript">      <!--      Sub window_onLoad()         <%FillCells%>      End Sub      -->      </SCRIPT>   </HEAD>   <BODY>   <OBJECT ID="MSFlexGrid1" WIDTH=668 HEIGHT=156    CLASSID="CLSID:6262D3A0-531B-11CF-91F6-C2863C385E30">      <PARAM NAME="_ExtentX" VALUE="17674">      <PARAM NAME="_ExtentY" VALUE="4128">      <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, therecordset uses a cursor of type 3; viz., a static cursor. Choose your owncursor wisely, but choose one that supports the RecordCount propertybecause the ADO script needs to use the number of records returned by thedata source to specify how many rows the FlexGrid control needs to displayat runtime.

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

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

Article ID: 189406 - Last Review: 10/26/2013 01:04:00 - Revision: 3.0

  • Microsoft Active Server Pages 4.0
  • kbnosurvey kbarchive kbhowto kbscript KB189406