Community discussion forum

Creating a Datagrid Class in classic ASP

• • Reply

  9 years ago

  by Developer Fusion Bot

  Developer Fusion Bot

  , United Kingdom

  Joined 2 years ago

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

  Report abuse
• Advertisement

  Simply the fastest line-level profiler for .NET ever

  “The low overhead means it has minimal impact on the execution of my program”
  Mark Everest, Development Team Leader, Renault F1 Team Ltd.

  Try out the new ANTS Profiler 4 for yourself. Download your 14-day trial now

• • Reply

  6 years ago

  by asd

  asd

  , United States

  Joined 6 years ago

  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
    
  Report abuse
• • Reply

  6 years ago

  by musician

  Brian O'Connell

  Dublin, Ireland, Ireland

  Joined 6 years ago

  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.

  Report abuse
• • Reply

  6 years ago

  by moose

  John Taylor

  Oxford, England, United Kingdom

  Joined 6 years ago

  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.

  Report abuse
• • Reply

  5 years ago

  by lotiejam

  lotiejam

  , United States

  Joined 5 years ago

  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?

  Report abuse
• • Reply

  4 years ago

  by ChristianCalderon

  Christian Calderon

  Chicago, IL, United States

  Joined 4 years ago

  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>

  Report abuse
• • Reply

  3 years ago

  by trish

  trish

  , United States

  Joined 3 years ago

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

  
  Thanks

  Report abuse
• • Reply

  2 years ago

  by dibley1973

  Duane Wingett

  South West, United Kingdom

  Joined 2 years ago

  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
  %>
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  

   

  Report abuse