This thread is for discussions of Creating a Datagrid Class in classic ASP.

To achieve the goal of greater efficiency the author should:

• avoid using string concatenation operator (&) completely (use more efficient alternatives)
  
• take a look at Recordset.getString method to get rid of the VBScript cycle
  

Good point. It could be much more efficient and much more feature rich. The real point is to explain the use of classes and to show asp developers what they could do with a little imagination. You could explain in more detail the OVERLOOKED issues and help others at the same time.

I thought the class was groovy. It will save a lot of time setting up data driven pages.

Well done mate!

I've ammended the class to parameterise the table formatting. Very easy to do.
Thanks for the excellent starting point.

I have used the datagrid class code to great success but would like to format one of the columns so the data displayed is can be used as a link(url). Each url would point to the same page and session variables would carry the identifying data the rest would be passed by form parameters. Can anyone help?

When looping and printing from a recordset it is better to get a reference to the fields that we want to print.
For instance, this code would be more efficient (specially true when printing large sets of date):

<table>
<%
Dim fld1,fld2,fld3
Set fld1 = objRec("name")
Set fld2 = objRec("address")
Set fld3 = objRec("email")

Do Until objRec.EOF
%>
  <tr>
       <td><%=fld1.value %></td>
       <td><%=fld2.value %></td>
       <td><%=fld3.value %></td>
  </tr>
<%
  objRec.movenext
loop
Set fld1 = Nothing
Set fld2 = Nothing
Set fld3 = Nothing

%>
</table>

Did you ever find out on how to do this..........?  I'm try to accomplish the same...

Thanks

What a great article!!! I have just been starting to use .Net at work but still use classic ASP for home projects so liked the similarity of a near OO approach. I needed a DropDownMenu on a project so used this classic asp DataGrid class as a base for my DDM class.

I have enclosed the code below in case any one would like to make use of it. The class will take a correctly formatted array as a datasource or you can pass in a connection string, DataMember( table name), DataValueField and DataTextField and the class will use that.

In a future revision I would like to be able to pass an active database connection into the class as the DataSource as an another DataSource option. 

Properties of the class are:

  .ID
  .DataSource
  .CssClass
  .DataMember
  .DataValueField
  .DataTextField
  .Multiple
  .SelectedValue
  .Size
  .OrderByField
  .Enabled
  .TabIndex
  .Script











 

Hope someone finds this useful as is or a s a base.

Class Code

<%
'clsDropDownMenu
'Intended to be similar to a .Net combobox
Class clsDropDownMenu

 '~~~~~~~~~~~~~~
 'Private Fields
 Private m_ID 'As String
 Private m_strCssClass 'As String
 Private m_strDataSource 'As String
 Private m_strDataMember 'As String
 Private m_strDataTextField 'As String
 Private m_strDataValueField 'As String
 Private m_blnEnabled 'AS Boolean
 Private m_blnMultiple 'As Boolean
 Private m_strOrderByField  'As Boolean
 Private m_strScript 'As String
 Private m_strSelectedValue 'As String
 Private m_intSize 'As Integer
 Private m_intTabIndex 'As Integer
 Private m_intWidth 'As String

 Private m_intDataSourceType 'As Integer
 Private arrTable 'As Array

 Private m_cnDDM 'As Adodb.Connection
 Private m_rsDDM 'As Adodb.Recordset
 Private m_strSQL 'As String
 Private m_strOutput 'As String
 
 Private m_intInvalidDataSource 'As Integer
 Private m_intNoDataSource 'As Integer
 Private m_intNoDataMember 'As Integer
 Private m_intNoDataValueField 'As Integer
 Private m_intNoDataTextField 'As Integer
 
 '~~~~~~~~~~~~~~~~~
 'Public Properties

 Public Property Get ID()
  ID = m_ID
 End Property
 Public Property Let ID(Value)
  m_ID = Value
 End Property

 Public Property Get CssClass()
  CssClass = m_strCssClass
 End Property
 Public Property Let CssClass(Value)
  m_strCssClass = Value
 End Property
 
 Public Property Get DataSource()
  DataSource = m_strDataSource
 End Property
 Public Property Let DataSource(Value)

  Select Case VarType(Value)
   Case vbArray
    m_intDataSourceType = vbArray
   Case vbString
    m_intDataSourceType = vbString
   Case Else
    m_intDataSourceType = vbError
    Err.Raise (vbObjectError + m_intInvalidDataSource), "clsDropDownMenu", "DataSource property must be set to a database connection string"
  End Select

  m_strDataSource = Value
 End Property

 Public Property Get DataMember()
  DataMember = m_strDataMember
 End Property
 Public Property Let DataMember(Value)
  m_strDataMember = Value
 End Property

 Public Property Get DataTextField()
  DataTextField = m_strDataTextField
 End Property
 Public Property Let DataTextField(Value)
  m_strDataTextField = Value
 End Property

 Public Property Get DataValueField()
  DataValueField = m_strDataValueField
 End Property
 Public Property Let DataValueField(Value)
  m_strDataValueField = Value
 End Property

 Public Property Get Enabled()
  Enabled = m_blnEnabled
 End Property
 Public Property Let Enabled(Value)
  m_blnEnabled = Value
 End Property

 Public Property Get Multiple()
  Multiple = m_blnMultiple
 End Property
 Public Property Let Multiple(Value)
  m_blnMultiple = Value
 End Property

 Public Property Get OrderByField()
  OrderByField = m_strOrderByField
 End Property
 Public Property Let OrderByField(Value)
  m_strOrderByField = Value
 End Property
 
 Public Property Get Script()
  Script = m_strScript
 End Property
 Public Property Let Script(Value)
  m_strScript = Value
 End Property

 Public Property Get SelectedValue()
  SelectedValue = m_strSelectedValue
 End Property
 Public Property Let SelectedValue(Value)
  m_strSelectedValue = Value
 End Property
 
 Public Property Get Size()
  Size = m_intSize
 End Property
 Public Property Let Size(Value)
  m_intSize = Value
 End Property
 
 Public Property Get TabIndex()
  TabIndex = m_intTabIndex
 End Property
 Public Property Let TabIndex(Value)
  m_intTabIndex = Value
 End Property

 Public Property Get Width()
  Width = m_intWidth
 End Property
 Public Property Let Width(Value)
  m_intWidth = Value
 End Property
 
 '~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 'Contructor and deconstructor
 
 'Class_Initialize
 'Purpose: Fires when reference to this class is created
 Private Sub Class_Initialize()
  'Set up the custom error numbers
  m_intInvalidDataSource = 6
  m_intNoDataSource  = 7
  m_intNoDataMember  = 8
  m_intNoDataValueField = 9
  m_intNoDataTextField = 10
   
    'Create a connection and recordset object
  Set m_cnDDM = server.createobject("Adodb.Connection")
       Set m_rsDDM = server.createobject("Adodb.Recordset")
 End Sub
 
 'Class_Terminate
 'Purpose: Fires when reference to this class is destroyed
 '   Closes Recordset and connections if they were left open after an error
 Private Sub Class_Terminate()
   
  m_strOutput = ""

  'Determine if recordset object was created
  If IsObject(m_rsDDM) And (Not (m_rsDDM is Nothing)) Then
  
   'Determine if the recordset is open...
   If m_rsDDM.State = adStateOpen then
    '...it is so close it
    m_rsDDM.Close
   End If
   
   'Kill the reference to the recordset
   Set m_rsDDM = Nothing
  End If

  'Determine if connection object was created
  If IsObject(m_cnDDM)  And (Not (m_cnDDM is Nothing)) Then
   'Determine if the connection is open...
   If m_cnDDM.State = adStateOpen then
    '...it is so close it
    m_cnDDM.Close
   End If
   'Kill the reference to the connection
   Set m_cnDDM = Nothing
  End If
 End Sub 'Class_Terminate
 

 '~~~~~~~~~~~~~~
 'Public Methods

 'Bind
 'Purpose: Binds the data in the datasource to the control and renders it
 Public Sub Bind()
  Dim intTableCols, intTableRows 'As Integer
  Dim intCurrentRow, Columns 'As Integer

  'Determine if necessary properties have been set
  If (m_strDataSource = "") Then
   Err.Raise (vbObjectError + m_intNoDataSource), "clsDropDownMenu", "DataSource field property have not been set"
   Exit Sub
  End If

  'Determine the datasource and populate an array from it
  Select Case m_intDataSourceType
   
   Case vbArray
    arrTable = m_strDataSource
    
   Case vbString

    If (m_strDataMember = "") Then
     Err.Raise (vbObjectError + m_intNoDataMember), "clsDropDownMenu", "DataMember field property have not been set"
     Exit Sub
    End If

    If (m_strDataTextField = "") Then
     Err.Raise(vbObjectError + m_intNoDataTextField), "clsDropDownMenu", "DataText field property have not been set"
     Exit Sub
    End If

    If (m_strDataValueField = "") Then
     Err.Raise(vbObjectError + m_intNoDataValueField), "clsDropDownMenu", "DataValue field property have not been set"
     Exit Sub
    End If

    'Use GetRows in function to create array
    arrTable = PopulateArrayFromDataSource(m_strDataSource)
    
  End Select
  
  m_strOutput = "    <select name=""" & m_ID & """ "
  
  'Add event scripting if property is set.
  If Lenb(m_strScript) > 0 Then
   m_strOutput = m_strOutput & m_strScript & " "
  End If

  'Determine if enabled property is set
  If m_blnEnabled <> True Then
   m_strOutput = m_strOutput & " disabled "
  End If

  'Determine if multiple property is set
  If m_blnMultiple <> True Then
   m_strOutput = m_strOutput & " multiple "
  End If

  'Determine if TabIndex property is set
  If (IsNumeric(m_intTabIndex)) And (m_intTabIndex > 0) Then
   m_strOutput = m_strOutput & " tabindex=""" & m_intTabIndex & """ "
  End If

  'Determine if Size property is set
  If (IsNumeric(m_intSize)) And (m_intSize > 0) Then
   m_strOutput = m_strOutput & " size=""" & m_intSize & """ "
  End If

  'Add Css class if property is set.
  If Lenb(m_strCssClass) > 0 Then
   m_strOutput = m_strOutput & " class=""" & m_strCssClass & """ "
  End If
  
  'Add closing tag to output
  m_strOutput = m_strOutput & ">" & vbNewLine
  
  intTableCols = UBound(arrTable, 1)
  intTableRows = UBound(arrTable, 2)

  'Loop through all "Rows" in array
  For intCurrentRow = 0 to intTableRows

   m_strOutput = m_strOutput & "      <option value=""" & arrTable(0, intCurrentRow) & """ "
   
   If SelectedValue = arrTable(0, intCurrentRow) Then
     m_strOutput = m_strOutput & "selected "
    End If
   
   m_strOutput = m_strOutput & "> " & arrTable(1, intCurrentRow) & "</option>" &vbNewLine

  Next

  'Add closing select tag
  m_strOutput = m_strOutput & "    </select>" & vbNewLine
  
  'Write out the output
  Response.Write m_strOutput  

 End Sub

 '~~~~~~~~~~~~~~~
 'Private Methods

 'PopulateArrayFromDataSource
 'Inputs: Datasource as connection string
 'Returns: An array populated using GetRows
 Private Function PopulateArrayFromDataSource(ByVal DataSource) 'As Array
  Dim arrTemp 'As Array
  
  'Populate SQL statement
  m_strSQL = GetDropDownMenuSQL()
  'DebugPrintSQL(m_strSQL)
  'Response.End
 
  'Open the connection
  m_cnDDM.Open(m_strDataSource)

  'Populate recordset
  Set m_rsDDM = m_cnDDM.Execute(m_strSQL)

  arrTemp = m_rsDDM.GetRows

  'Determine if recordset object was created
  If IsObject(m_rsDDM) And (Not (m_rsDDM is Nothing)) Then
  
   'Determine if the recordset is open...
   If m_rsDDM.State = adStateOpen then
    '...it is so close it
    m_rsDDM.Close
   End If
   
   'Kill the reference to the recordset
   Set m_rsDDM = Nothing
  End If

  'Determine if connection object was created
  If IsObject(m_cnDDM)  And (Not (m_cnDDM is Nothing)) Then
   'Determine if the connection is open...
   If m_cnDDM.State = adStateOpen then
    '...it is so close it
    m_cnDDM.Close
   End If
   'Kill the reference to the connection
   Set m_cnDDM = Nothing
  End If

  PopulateArrayFromDataSource = arrTemp
 End Function
 
 
 Private Function GetDropDownMenuSQL()
  Dim strTempSQL 'As String
  Dim strOrderBySQL 'As String
  
  'Detect if OrderBy property has been poulated...
  If LenB(m_strOrderByField) > 0 Then
   '...if so use it for ordering
   strOrderBySQL = m_strOrderByField
  Else
   '...if not use the text field
   strOrderBySQL = m_strDataTextField
  End If

  'Build SQL string
  strTempSQL = _
   "SELECT " & _
    m_strDataValueField & ", " & _
    m_strDataTextField & " " & _
   "FROM " & _
    m_strDataMember & " " & _
   "ORDER BY " & _
    strOrderBySQL  & " ASC"
  
  GetDropDownMenuSQL = strTempSQL
 End Function
 
End Class
%>

Example page

<%Option Explicit%>
<!--#Include File="classes/clsDropDownMenu.asp" -->
<%

 Dim TitleDropDown 'As clsDropDownMenu

 Set TitleDropDown = New clsDropDownMenu
 With TitleDropDown
  .ID = "cboTitle"
  .DataSource  = m_strConnMain
  .CssClass  = "DropDown"
  .DataMember  = "TITLE"
  .DataValueField = "intID"
  .DataTextField = "strDescription"
  .Multiple  = True
  .SelectedValue = 3
  .Size   = 5
  .OrderByField = .DataValueField
  .Enabled  = True
  .TabIndex  = 1
  .Script   = "onChange='dothis();'"
  
  .Bind()
 End With
 Set TitleDropDown = Nothing
%>