Library tutorials & articles
Creating a Datagrid Class in classic ASP
Microsoft Certified Applications Developer with 10 years experience developing web based applications using asp, asp.net for a Local Authority in Dublin. Clings to a firm belief that a web applicat...
- Introduction
- 2-Dimensional Arrays
- Creating the class
- Analysis of the Class
- More Analysis of the Class
- Using the Class in an asp page
Using the Class in an asp page
Paste the class code into notepad and save it as caDataGrid.asp in your wwwroot
folder. I use the nwind.mdb database as an example here which is found with plenty
of MS products, in my case in my Visual Basic folder. I copied the database to
the wwwroot folder for simplicity. Now create an asp page in your wwwroot folder
with the following:- <%@ LANGUAGE="VBSCRIPT" %>
<% option explicit %>
<% response.buffer=true %>
<html>
<head>
<title>caDataGrid Test</title>
</head>
<!-- #include file="caDataGrid.asp"-->
<body>
<%
dim cDataGrid, mapPath
Set cDataGrid = New caDataGrid
mapPath = "nwind.mdb"
'Access 2000 connection
cDataGrid.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source="&
Server.MapPath(mapPath)
cDataGrid.SqlString = "select firstname, lastname, title from employees"
cDataGrid.Bind
set cDataGrid = nothing
'now lets set our own columns
response.write "<br><br>"
Set cDataGrid = New caDataGrid
cDataGrid.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source="&
Server.MapPath(mapPath)
cDataGrid.SqlString = "select firstname, lastname, title from employees"
cDataGrid.AutoColumns = false
cDataGrid.AddColumn("First Name")
cDataGrid.AddColumn("Last Name")
cDataGrid.AddColumn("Job Title")
cDataGrid.Bind
set cDataGrid = nothing
%>
</body>
</html>
You 'reference' the class using the include capability of asp. Of course theres
nothing to say you can't just paste your class code into the same page but it
wouldn't be nice and neat would it? Then you declare a variable and set it as
a caDataGrid object/class, assign the relevent values and bind. All of
5/6 lines of code. Very handy.
If you have been paying attention you might have realised how much more you could
do with this. First on the list should be better error code. For example if I
set cDataGrid.AutoColumns = false and then forget to add column names then I'd
have get bad output. On the more interesting side of things I could add alot
of similar stuff to this class as you find with the .net datagrid - paging, background
colours or even class names from a stylesheet all with some more properties and
methods. More work obviously but once it's done it'll be there to use many many
times. Until tomorrow when you install the .net framework and forget all about
it. Anyway hope this is of use to you all. Happy Coding.
Related articles
Related discussion
-
Datagrid footer
by janetb (0 replies)
-
Header and Footer in Web page print
by fhajaj (4 replies)
-
help me to get simple requirement
by Slicksim (1 replies)
-
Gridview -> Template Field -> Button
by antti.simonen (1 replies)
-
Classic ASP : Page expires
by chezhian_in05 (0 replies)
Related podcasts
-
ASP.NET Caching and Performance
Steve Smith, owner of ASP Alliance and Lake Quincy Media joins us today to teach us about some hidden gems in ASP.NET caching and performance. Steve’s expertise in this area comes from first-hand experience as Lake Quincy’s ad system serves over 60 requests per second and handles over 150 million...
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
%>
Did you ever find out on how to do this..........? I'm try to accomplish the same...
Thanks
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>
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?
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.
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.
To achieve the goal of greater efficiency the author should:
This thread is for discussions of Creating a Datagrid Class in classic ASP.