clsSortableTable.asp

clsSortableTable.asp

--------------------------------------------------------------------------------

Description : This is a VBScript class that will generate and write an HTML table filled by a RS. All you have to do is pass set its connection and SQL properties. The table will be pageable and sortable by clicking the column heads. You can set the column heads to text or images. You can also set on of the columns to have a link (like a tradenum going to a trade details page). This is a nice example of how to use a VBScript class.

<%
'''''''''''''''''''''''''''''''''''''
'clsSortableTable
'
'PURPOSE:
'This is a VBScript class that will gene
' rate and write an HTML table filled by a
' RS.
'All you have to do is pass set its conn
' ection and SQL properties. The table wil
' l be pageable
'and sortable by clicking the column hea
' ds. You can set the column heads to text
' or images.
'You can also set on of the columns to h
' ave a link (like a tradenum going to a t
' rade details page).
'This is a nice example of how to use a
' VBScript class.
'
'how to use it
'FIRST: include this file and create a n
' ew clsSortableTable object
' dim objSortableTable
' set objSortableTable = new clsSortable
' Table
'SECOND: set properties
' MUSTHAVES
'set the connection Property To a connection object
' objSortableTable.Connection = objConn
'set the SQL property to a string of SQL
'
' objSortableTable.SQL = "select mem_id,
' mem_last_name, mem_first_name, mem_name,
' mem_last_updated from mfiMember"
'set the default sort property to a stri
' ng of the field you want the default sor
' t on
' objSortableTable.DefaultSort = "mem_la
' st_name"
' OPTIONAL PROPERTIES
'the displayedfields Property can be Set To an array of fields that you would like display,
'if you don't Set this To an array, it will use all the Select fields from the query
' objSortableTable.DisplayedFields = arr
' ay("mem_last_name", "mem_first_name", "m
' em_name", "mem_last_updated")
'you can Set this variable To the current page name, if you choose
'not To Set this variiable it will default and request the script name from the serverVariables collection
' objSortableTable.PageName = "memberLis
' t.asp"
'use the PageSize property to set the nu
' mber of records per page, if none select
' ed it will default to 10
' objSortableTable.PageSize = 7
'the TableAttributes property can be set
' to a string of HTML table attributes
' objSortableTable.TableAttributes = "bo
' rder=1 cellpadding=1 cellspacing=0"
'the HeaderRowAttributes property can be
' set to a string of HTML row attributes t
' o be used for the header
' objSortableTable.HeaderRowAttributes =
' "bgcolor='#cccccc'"
'the HeaderCellAttributes property can b
' e set to a string of HTML cell attribute
' s to be used for the header
' objSortableTable.HeaderCellAttributes
' = "align=center valign=bottom"
'the RowAttributes property can be set t
' o a string of HTML row attributes to be
' used for the records
' objSortableTable.RowAttributes = "bgco
' lor=white"
'the CellAttributes property can be set
' to a string of HTML cell attributes to b
' e used for the records
' objSortableTable.CellAttributes = "val
' ign=top"
'the FontTagAttributes property can be s
' et to a string of HTML font tag attribut
' es to be used for the records
' objSortableTable.FontTagAttributes = "
' color=blue face='tahoma'"
'the ImagePath property is the path to y
' our image files, it defaults to "../img/
' " assuming
'that the images are located in an folde
' r called Img up a level from the current
' folder
' objSortableTable.ImagePath = "../img/"
'
'set the TextLink property to false if y
' ou want images,else it will default to t
' ext links that are based on
'the DisplayedFields property
' objSortableTable.TextLink = true
'PlainImages property can be set to an s
' tring array of images to be used in the
' header
'objSortableTable.PlainImages = array("&
' lt;img src=firstname.gif>", "...
'AscImages property can be set to an str
' ing array of images to be used in the he
' ader for Ascending sort
'objSortableTable.AscImages = array("<
' ;img src=firstname_asc.gif>", "...
'DscImages Property can be Set To an String array of images to be used in the header For descending sort
'objSortableTable.DscImages = array("<
' ;img src=firstname_dsc.gif>", "...
'NavString property can be set to an str
' ing array of images to be used for the n
' avigation buttons
'where index 0=first, 1=previous, 2=next
' , 3=last
' you could also set this to an array of
' strings like array("first", "previous",
' "next", "last")
'objSortableTable.NavString = array("<
' ;img src=first.gif>", "<img src=pr
' evious.gif>", "<img src=next.gif",
' "<img src=last.gif")
'*the clsSortableTable has two propertie
' s it needs to sort and navigate, they ar
' e .Sort and .CurrentPage
'the sorting and navigating works by pas
' sing the querystring vairables page and
' sort to itself, so, if
'you were going to change these properti
' es youd probably make em equal to reques
' t("sort") for sort and
'request("page") for currentPage, howeve
' r if you don't touch these the object wi
' ll take care of it on its own.
'the point being though, that you can re
' ad these values

'the LinkedColumnName Property can Set one of the columns To have a link in it
'objSortableTable.LinkedColumnName = "la
' stname"

'the Link property is the link to be use
' d on the column that is the LinkedColumn
' Name
' objSortableTable.Link = "myDetailPage.
' asp"
'the FieldToAppendToLink property is the
' field whos value you want passed with th
' e link
'objSortableTable.FieldToAppendToLink =
' "employeeid" 'EX: &employeeid=5 will be
' appended
'THIRD: call the writeTable method where
' you want your table to be written
' objSortableTable.writeTable
'SAMPLE CALL:

'dim objST 'for sortable table
'dim objConn, strSQL
'Set objConn = server.CreateObject("ADODB.Connection")
'objConn.Open "northwind","",""
'strSQL = "select employeeID, firstName, lastName, 'Northwind Traders' as comp_name, notes as comments from employees" '"Select * From workouts"
'set objST = new clsSortableTable

'objST.Connection = objConn
'objST.SQL = strSQL
'objST.DefaultSort = "lastName"
'objST.DisplayedFields = array("lastname", "firstname", "comp_name", "comments")
'objST.PageSize = 3
'objST.TableAttributes = "border=1 cellpadding=1 cellspacing=0"
'objST.HeaderRowAttributes = "bgcolor='#cccccc'"
'objST.HeaderCellAttributes = "align=center valign=bottom"
'objST.RowAttributes = "bgcolor=white"
'objST.TextLink = False
'objST.LinkedColumnName = "lastname"
'objST.FieldToAppendToLink = "employeeid"
'objST.Link = "myDetailPage.asp"
'objST.WriteTable
'
''''''''''''
Class clsSortableTable
'class level Private variable
Private m_objConn
Private m_strSQL
Private m_strDefaultSort
Private m_strSort
Private m_arrDisplayedFields
Private m_intCurrentPage
Private m_strPageName
Private m_intPageSize
Private m_strLinkedColumnName
Private m_strLink
Private m_strFieldToAppendToLink
Private m_strTableAttributes
Private m_strHeaderRowAttributes
Private m_strHeaderCellAttributes
Private m_strRowAttributes
Private m_strCellAttributes
Private m_strImagePath
Private m_arrPlainImages
Private m_arrAscImages
Private m_arrDscImages
Private m_arrNavString
Private m_strFontTagAttributes
Private m_blnTextLink
Private m_CellStyle
Private RS 'recordset
Private strTemp, field, strMoveFirst, strMoveNext, strMovePrevious, strMoveLast
Private i, j,intTotalPages, intCurrentRecord, intTotalRecords, intTotalFields, intDisplayedFields
'properties
Public Property Get DefaultSort
DefaultSort = m_strDefaultSort
End Property
Public Property Let DefaultSort(strDefaultSort)
m_strDefaultSort = strDefaultSort
End Property
Public Property Get Sort
Sort = m_strSort
End Property
Public Property Let Sort(strSort)
m_strSort = strSort
End Property
Public Property Get Connection
Connection = m_objConn
End Property
Public Property Let Connection(objConn)
m_objConn = objConn
End Property
Public Property Get SQL
SQL = m_strSQL
End Property
Public Property Let SQL(strSQL)
m_strSQL = strSQL
End Property
Public Property Get CurrentPage
CurrentPage = m_intCurrentPage
End Property
Public Property Let CurrentPage(intCurrentPage)
m_intCurrentPage = intCurrentPage
End Property
Public Property Get DisplayedFields
DisplayedFields = m_arrDisplayedFields
End Property
Public Property Let DisplayedFields(arrDisplayedFields)
m_arrDisplayedFields = arrDisplayedFields
End Property
Public Property Get PageName
PageName = m_strPageName
End Property
Public Property Let PageName(strPageName)
m_strPageName = strPageName
End Property
Public Property Get PageSize
PageSize = m_intPageSize
End Property
Public Property Let PageSize(intPageSize)
m_intPageSize = intPageSize
End Property

Public Property Get FieldToAppendToLink
FieldToAppendToLink = m_strFieldToAppendToLink
End Property
Public Property Let FieldToAppendToLink(strFieldToAppendToLink)
m_strFieldToAppendToLink = strFieldToAppendToLink
End Property

Public Property Get LinkedColumnName
LinkedColumnName = m_strLinkedColumnName
End Property
Public Property Let LinkedColumnName(strLinkedColumnName)
m_strLinkedColumnName = strLinkedColumnName
End Property
Public Property Get Link
Link = m_strLink
End Property
Public Property Let Link(strLink)
m_strLink= strLink
End Property

Public Property Get TableAttributes
TableAttributes = m_strTableAttributes
End Property
Public Property Let TableAttributes(strTableAttributes)
m_strTableAttributes = strTableAttributes
End Property

Public Property Get HeaderRowAttributes
HeaderRowAttributes = m_strHeaderRowAttributes
End Property
Public Property Let HeaderRowAttributes(strHeaderRowAttributes)
m_strHeaderRowAttributes = strHeaderRowAttributes
End Property

Public Property Get HeaderCellAttributes
HeaderCellAttributes = m_strHeaderCellAttributes
End Property
Public Property Let HeaderCellAttributes(strHeaderCellAttributes)
m_strHeaderCellAttributes = strHeaderCellAttributes
End Property

Public Property Get CellAttributes
CellAttributes = m_strCellAttributes
End Property
Public Property Let CellAttributes(strCellAttributes)
m_strCellAttributes = strCellAttributes
End Property

Public Property Get RowAttributes
RowAttributes = m_strRowAttributes
End Property
Public Property Let RowAttributes(strRowAttributes)
m_strRowAttributes = strRowAttributes
End Property

Public Property Get ImagePath
ImagePath = m_strImagePath
End Property
Public Property Let ImagePath(strImagePath)
m_strImagePath = strImagePath
End Property

Public Property Get PlainImages
PlainImages = m_arrPlainImages
End Property
Public Property Let PlainImages(arrPlainImages)
m_arrPlainImages = arrPlainImages
End Property

Public Property Get DscImages
DscImages = m_arrDscImages
End Property
Public Property Let DscImages(arrDscImages)
m_arrDscImages = arrDscImages
End Property

Public Property Get AscImages
AscImages = m_arrAscImages
End Property
Public Property Let AscImages(arrAscImages)
m_arrAscImages = arrAscImages
End Property

Public Property Get NavString
NavString = m_arrNavString
End Property
Public Property Let NavString(strNavString)
m_arrNavString = strNavString
End Property

Public Property Get FontTagAttributes
FontTagAttributes = m_strFontTagAttributes
End Property
Public Property Let FontTagAttributes(strFontTagAttributes)
m_strFontTagAttributes = strFontTagAttributes
End Property
Public Property Get TextLink
TextLink = m_blnTextLink
End Property
Public Property Let TextLink(blnTextLink)
m_blnTextLink = blnTextLink
End Property

Public Property Get CellStyle
CellStyle = m_CellStyle
End Property
Public Property Let CellStyle(strCellStyle)
m_CellStyle = strCellStyle
End Property

Private Sub Class_Initialize()
m_strDefaultSort = ""
m_strSort = request("sort")
m_arrDisplayedFields = ""
m_intCurrentPage = request("page")
m_strPageName = Request.ServerVariables("SCRIPT_NAME")
m_intPageSize = 10
m_strLinkedColumnName = ""
m_strLink = ""
m_strFieldToAppendToLink = ""
m_strTableAttributes = ""
m_strHeaderRowAttributes = ""
m_strHeaderCellAttributes = ""
m_strRowAttributes = ""
m_strCellAttributes = ""
m_strImagePath = "../img/"
m_arrPlainImages = ""
m_arrAscImages = ""
m_arrDscImages = ""
m_arrNavString = ""
m_strFontTagAttributes = ""
m_blnTextLink = True
m_cellStyle = ""
i = 0
j = 0
Set RS = Server.CreateObject("adodb.recordset")

End Sub

Public Sub WriteTable()
'if no sort Then use default sort
If m_strSort = "" Then
m_strSort = m_strDefaultSort
End If
'if no page given use page 1
If m_intCurrentPage = "" Then
m_intCurrentPage = 1
End If
'open recordset
createRecordset
'if no array of fields is given Then use all the fields
If IsArray(m_arrDisplayedFields) Then
intDisplayedFields = UBound(m_arrDisplayedFields)
Else
intDisplayedFields = intTotalFields - 1
ReDim m_arrDisplayedFields(intDisplayedFields)
For i = 0 To intDisplayedFields
m_arrDisplayedFields(i) = RS.Fields(i).Name
Next
End If

'fill image arrays
setUpImageArrays

Response.Write "<TABLE " & m_strTableAttributes & " >" & vbCrLf
'display the headers
writeHeaders()
'end table header
If RS.EOF Then
Response.Write "<TR " & m_strRowAttributes & " >" & vbCrLf
Response.Write "<TD " & m_strCellAttributes & " Colspan='100%' align=center >" & vbCrLf
Response.Write "There are no records."
Response.Write "</TD>" & vbCrLf
Response.Write "</TR>" & vbCrLf
Else
'start records
For i = intCurrentRecord To RS.PageSize
If Not RS.EOF Then
Response.Write "<TR " & m_strRowAttributes & " >" & vbCrLf
'start record cells
For j = 0 To intDisplayedFields
'''''''''''''''''''''''''''''''''
'
If LCase(m_arrDisplayedFields(j)) = LCase(m_strLinkedColumnName) Then
Response.Write "<TD " & m_strCellAttributes & " ><FONT " & m_strFontTagAttributes & " > <A href='" & m_strLink & "?page="&m_intCurrentPage & _
"&sort=" & m_strSort & "&" & m_strFieldToAppendToLink & "=" & RS(m_strFieldToAppendToLink) & "' >" & RS(m_arrDisplayedFields(j)) & "</FONT></TD>"
Else
Response.Write "<TD " & m_strCellAttributes & " ><FONT " & m_strFontTagAttributes & " > " & RS(m_arrDisplayedFields(j)) & "</FONT></TD>"
End If
''''''''''''''''''''''''''''''''
'
Next
'end cells
Response.Write "</TR>" & vbCrLf
RS.MoveNext
End If
Next
End If
'end records
'end table
Response.Write "</TABLE>" & vbCrLf

'write the navigation buttons
writeNavButtons

End Sub

Private Sub Class_Terminate()
If RS.State = &H00000001 Then 'its open
RS.Close
End If
Set RS = Nothing
End Sub

'''CREATERECORDSET
Private Sub createRecordset()
With RS
.CursorLocation=3
.Open m_strSQL & " order by " & Replace(m_strSort,"desc"," desc"), m_objConn,3
If Not rs.EOF Then
.PageSize = CInt(m_intPageSize)
intTotalPages = .PageCount
intCurrentRecord = .AbsolutePosition
.AbsolutePage = m_intCurrentPage
intTotalRecords = .RecordCount
intTotalFields = .Fields.Count
Else
intTotalFields = 0
intTotalRecords = 0
intTotalPages = 0
intCurrentRecord = 0
End If
End With
End Sub

'''WRITEHEADERS
Private Sub writeHeaders()
Response.Write "<TR " & m_strHeaderRowAttributes & ">" & vbCrLf
For i = 0 To intDisplayedFields
Response.Write "<TD " & m_strHeaderCellAttributes & " >" & vbCrLf
If InStr(LCase(m_strSort),LCase(m_arrDisplayedFields(i))) Then 'if we come across the current sort in the Loop
If InStr(m_strSort, "desc")Then 'if the sort is descending, write the link For ascending, but give a descending image
Response.Write "<A href=" & m_strPageName & "?sort="& m_arrDisplayedFields(i) &"&page="&m_intCurrentPage&">" & m_arrDscImages(i) & "</A>" & vbCrLf
Else
Response.Write "<A href=" & m_strPageName & "?sort="& m_arrDisplayedFields(i) &"desc&page="&m_intCurrentPage&">" & m_arrAscImages(i) & "</A>" & vbCrLf
End If
Else 'if Not the current sort
Response.Write "<A href=" & m_strPageName & "?sort="& m_arrDisplayedFields(i) &"&page="&m_intCurrentPage&">" & m_arrPlainImages(i) & "</A>" & vbCrLf
End If
Response.Write "</TD>" & vbCrLf
Next
Response.Write "</TR>"
End Sub

'''WRITENAVBUTTONS
Private Sub writeNavButtons()
If intTotalPages = 0 Then intTotalPages = 1
Select Case CInt(m_intCurrentPage)
Case CInt(intTotalPages) 'last page
If CInt(intTotalPages) = 1 Then 'its the only page
strMoveFirst = m_arrNavString(0) '"<A href=" & m_strPageName & "?sort="& m_strSort &"&page=1 >"
strMovePrevious = m_arrNavString(1) '"<A href=" & m_strPageName & "?sort="& m_strSort &"&page=" & m_intCurrentPage - 1 & " >"
strMoveNext = m_arrNavString(2)
strMoveLast = m_arrNavString(3) '"<A href=" & m_strPageName & "?sort="& m_strSort &"&page=" & intTotalPages & " >"
Else
strMoveFirst = "<A href=" & m_strPageName & "?sort="& m_strSort &"&page=1 >"& m_arrNavString(0) &"</A>"
strMovePrevious = "<A href=" & m_strPageName & "?sort="& m_strSort &"&page=" & m_intCurrentPage - 1 & " >"& m_arrNavString(1) &"</A>"
strMoveNext = m_arrNavString(2)
strMoveLast = m_arrNavString(3) '"<A href=" & m_strPageName & "?sort="& m_strSort &"&page=" & intTotalPages & " >"
End If
Case 1 'first page
strMoveFirst = m_arrNavString(0) '"<A href=" & m_strPageName & "?sort="& m_strSort &"&page=1 >"
strMovePrevious = m_arrNavString(1) '"<A href=" & m_strPageName & "?sort="& m_strSort &"&page=" & m_intCurrentPage - 1 & " >"
strMoveNext = "<A href=" & m_strPageName & "?sort="& m_strSort &"&page=" & m_intCurrentPage + 1 & " >"& m_arrNavString(2) &"</A>"
strMoveLast = "<A href=" & m_strPageName & "?sort="& m_strSort &"&page=" & intTotalPages & " >"& m_arrNavString(3) &"</A>"
Case Else
strMoveFirst = "<A href=" & m_strPageName & "?sort="& m_strSort &"&page=1 >"& m_arrNavString(0) &"</A>"
strMovePrevious = "<A href=" & m_strPageName & "?sort="& m_strSort &"&page=" & m_intCurrentPage - 1 & " >"& m_arrNavString(1) &"</A>"
strMoveNext = "<A href=" & m_strPageName & "?sort="& m_strSort &"&page=" & m_intCurrentPage + 1 & " >"& m_arrNavString(2) &"</A>"
strMoveLast = "<A href=" & m_strPageName & "?sort="& m_strSort &"&page=" & intTotalPages & " >"& m_arrNavString(3) &"</A>"
End Select

With response
.Write strMoveFirst & " "
.Write strMovePrevious
.Write " " & m_intCurrentPage & " of " & intTotalPages & " "
.Write strMoveNext & " "
.Write strMoveLast
End With
End Sub

'''SETUPIMAGEARRAYS
'If the image arrays are empty Fill them With .gif images based on fieldnames
Private Sub setUpImageArrays()
If Not IsArray(m_arrPlainImages)Or IsNull(m_arrPlainImages) Then
ReDim m_arrPlainImages( intDisplayedFields )
For i = 0 To intDisplayedFields
If CBool(m_blnTextLink) Then
m_arrPlainImages(i) = m_arrDisplayedFields(i)
Else
m_arrPlainImages(i) = "<Img src='" & m_strImagePath & m_arrDisplayedFields(i) & ".gif" & "' border=0>"
End If
Next
End If
If Not IsArray(m_arrAscImages) Or IsNull(m_arrAscImages) Then
ReDim m_arrAscImages( intDisplayedFields )
For i = 0 To intDisplayedFields
If CBool(m_blnTextLink) Then
m_arrAscImages(i) = m_arrDisplayedFields(i)
Else
m_arrAscImages(i) = "<Img src='" & m_strImagePath & m_arrDisplayedFields(i) & "_asc.gif" & "' border=0>"
End If
Next
End If
If Not IsArray(m_arrDscImages) Or IsNull(m_arrDscImages) Then
ReDim m_arrDscImages( intDisplayedFields )
For i = 0 To intDisplayedFields
If CBool(m_blnTextLink) Then
m_arrDscImages(i) = m_arrDisplayedFields(i)
Else
m_arrDscImages(i) = "<Img src='" & m_strImagePath & m_arrDisplayedFields(i) & "_desc.gif" & "' border=0>"
End If
Next
End If
If Not IsArray(m_arrNavString) Or IsNull(m_arrNavString) Then
ReDim m_arrNavString(3)
m_arrNavString(0) = "<Img src='" & m_strImagePath & "first.gif' border=0>"
m_arrNavString(1) = "<Img src='" & m_strImagePath & "prev.gif' border=0>"
m_arrNavString(2) = "<Img src='" & m_strImagePath & "next.gif' border=0>"
m_arrNavString(3) = "<Img src='" & m_strImagePath & "last.gif' border=0>"
End If
End Sub

End Class

%>