HOWTO: Populating a FlexGrid Control with ADO and ASP

ID: Q189406

The information in this article applies to:

Active Server Pages
Microsoft Active Server Pages
Microsoft Internet ActiveX Controls, version 1.0
ActiveX Data Objects (ADO), versions 2.0, 2.1 SP2

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:

Create a new .asp file for your project in Visual InterDev.

Replace the default source code with the ASP script below.

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.

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:

The Design-time conrol that creates a recordset on the server.

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.

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.

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 : kbActiveX kbADO kbASP kbScript kbVBp kbVBScript kbGrpASP kbGrpMDAC kbADO210sp2 kbiis400 kbiis500
Version : WINDOWS:1.0,2.0,2.1 SP2; winnt:; :
Platform : WINDOWS winnt
Issue type : kbhowto