Search Tools Links Login

Dynamic, Sortable, Pageable, HTML table from a SQL statement


This is code for a dynamically created, sortable, pageable HTML table, this is
a pretty stripped down version. Real simple, just call the procedure where you
want the table, pass it a connection object and a SQL string, it will create an
ADO recordset and fill it into an HTML table, it will be fully pageable and sortable
by clicking the column head. You can also have the values in one column linkable to
another page,(example being you have an offer number and you click it to go to a details page) You input the records per page, default sort order, and the HTML tables attributes.
This can be easily made to incorporate images for column heads and for navigation buttons(maybe i'll post that later if this get a good response) Please email me with any questions.

Original Author: Devin Garlit

Inputs

objConn = a connection object
strSQL = a string of SQL
strDefaultSort = a string of the default sorting column (i.e "FirstName")
intPageSize = integer of the number of records per page
strLinkedColumnName = a string of the colum to place a link on
strLink = a string of the page link
strTableAttributes = a string of HTML table attributes i.e. "name=myTable bgColor=steeleblue"

Assumptions

This is not that visually appealing, just shows the basics.

Returns

writes a sortable, pagable html table fill with records from a query

Code

'**************************************************************
'Function: createSortableList(objConn,strSQL, strDefaultSort, intPageSize, strLinkedColumnName,strLink,strTableAttributes)
'
'Returns: writes a sortable, pagable html table fill with records from a query
'
'Inputs:
' objConn = a connection object
' strSQL = a string of SQL
' strDefaultSort = a string of the default sorting column (i.e "FirstName")
' intPageSize = integer of the number of records per page
' strLinkedColumnName = a string of the colum to place a link on
' strLink = a string of the page link
' strTableAttributes = a string of HTML table attributes i.e. "name=myTable bgColor=steeleblue"
'
'Sample Call:
' createSortableList objConn,strSQL,"EmployeeID",3,"EmployeeID","employee_detail.asp","border=1 bgcolor='#cccccc'"
'
'Notes:
'
' This is code for a dynamically created, sortable, pageable HTML table, this is
' a pretty stripped down version. Real simple, just call the procedure where you
' want the table, pass it a connection object and a SQL string, it will create an
' ADO recordset and fill it into an HTML table, it will be fully pageable and sortable
' by clicking the column head. You can also have the values in one column linkable to
' another page,(example being you have an offer number and you click it to go to a details page)
' You input the records per page, default sort order, and the HTML tables attributes.
' This can be easily made to incorporate images for column heads and for navigation buttons(maybe i'll
' post that later if this get a good response) Please email me with any questions.
'
'Programmer: Devin Garlit (dgarlit@hotmail.com) 4/25/01
'**************************************************************
sub createSortableList(objConn,strSQL, strDefaultSort, intPageSize, strLinkedColumnName,strLink,strTableAttributes)
dim RS,strSort, intCurrentPage, strPageName
dim strTemp, field, strMoveFirst, strMoveNext, strMovePrevious, strMoveLast
dim i, intTotalPages, intCurrentRecord, intTotalRecords
i = 0

strSort = request("sort")
intCurrentPage = request("page")
strPageName = Request.serverVariables("SCRIPT_NAME")

if strSort = "" then
strSort = strDefaultSort
end if

if intCurrentPage = "" then
intCurrentPage = 1
end if

set RS = server.CreateObject("adodb.recordset")

with RS
.CursorLocation=3
.Open strSQL & " order by " & replace(strSort,"desc"," desc"), objConn,3 '3 is adOpenStatic
.PageSize = cint(intPageSize)
intTotalPages = .PageCount
intCurrentRecord = .AbsolutePosition
.AbsolutePage = intCurrentPage
intTotalRecords = .RecordCount
end with
Response.Write "

" & vbcrlf

'table head
Response.Write "
" & vbcrlf
for each field in RS.Fields 'loop through the fields in the recordset
Response.Write "
"

'records
for i = intCurrentRecord to RS.PageSize 'display from the current record to the pagesize
if not RS.eof then
Response.Write "
" & vbcrlf
for each field in RS.Fields 'for each field in the recordset
Response.Write "
" & vbcrlf
RS.MoveNext
end if
next

Response.Write "
" & vbcrlf
if instr(strSort, "desc") then 'check the sort order, if its currently ascending, make the link descending
Response.Write "" & field.name & "" & vbcrlf
else
Response.Write "" & field.name & "" & vbcrlf
end if
Response.Write "
" & vbcrlf
next
Response.Write "
" & vbcrlf
if lcase(strLinkedColumnName) = lcase(field.name) then 'if this field is the "linked field" provide a link
Response.Write "" & field.value & "" & vbcrlf
else
Response.Write field.value
end if
Response.Write "
" & vbcrlf
next
Response.Write "
" & vbcrlf

'page navigation
select case cint(intCurrentPage)
case cint(intTotalPages) 'if its the last page give only links to movefirst and move previous
strMoveFirst = ""& "First" &""
strMoveNext = ""
strMovePrevious = ""& "Prev" &""
strMoveLast = ""
case 1 'if its the first page only give links to move next and move last
strMoveFirst = ""
strMoveNext = ""& "Next" &""
strMovePrevious = ""
strMoveLast = ""& "Last" &""
case else
strMoveFirst = ""& "First" &""
strMoveNext = ""& "Next" &""
strMovePrevious = ""& "Prev" &""
strMoveLast = ""& "Last" &""
end select

with Response
.Write strMoveFirst & " "
.Write strMovePrevious
.Write " " & intCurrentPage & " of " & intTotalPages & " "
.Write strMoveNext & " "
.Write strMoveLast
end with

if RS.State = &H00000001 then 'its open
RS.Close
end if
set RS = nothing
end sub

About this post

Posted: 2002-06-01
By: ArchiveBot
Viewed: 85 times

Categories

ASP/ HTML

Attachments

No attachments for this post


Loading Comments ...

Comments

No comments have been added for this post.

You must be logged in to make a comment.