This is code for a dynamically created, sortable, - Visual Basic , VB.NET

	Find Code:
Home : Code : Forums : Submit : Mailing List : About : Contact

All

VB.NET

ASP.NET

C#

Snippets

Popular

Forums

Articles

Tips

Links

Books

Contest

This is code for a dynamically created, sortable, ...
Author: Devin Garlit
E-mail: Click to e-mail author
Submitted: 4/25/2001
Version: ASP
Compatibility: ASP
Category: ASP
Views: 17110
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.
Declarations:
'none
Code:
************************************* ' ******************* 'Procedure: createSortableList(objConn,st ' rSQL, strDefaultSort, intPageSize, strLi ' nkedColumnName,strLink,strTableAttribute ' s) ' 'Returns: writes a sortable, pagable htm ' l table fill with records from a query ' 'Inputs: ' objConn = a connection object ' strSQL = a string of SQL ' strDefaultSort = a string of the defau ' lt 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 bgCo ' lor=steeleblue" ' 'Sample Call: ' createSortableList objConn,strSQL,"E ' mployeeID",3,"EmployeeID","employee_deta ' il.asp","border=1 bgcolor='#cccccc'" ' 'Notes: ' ' This is code for a dynamically creat ' ed, sortable, pageable HTML table, this ' is ' a pretty stripped down version. Real s ' imple, just call the procedure where you ' ' want the table, pass it a connection o ' bject and a SQL string, it will create a ' n ' ADO recordset and fill it into an HTML ' table, it will be fully pageable and sor ' table ' by clicking the column head. You can a ' lso have the values in one column linkab ' le to ' another page,(example being you have ' an offer number and you click it to go t ' o a details page) ' You input the records per page, defaul ' t sort order, and the HTML tables attrib ' utes. ' This can be easily made to incorporate ' images for column heads and for navigati ' on buttons(maybe i'll ' post that later if this get a good res ' ponse) Please email me with any question ' s. ' 'Programmer: Devin Garlit (dgarlit@hotma ' il.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 "<table " & strTableAttributes & " >" & vbcrlf 'table head Response.Write "<tr>" & vbcrlf for each field in RS.Fields 'loop through the fields in the recordset Response.Write "<td align=center>" & vbcrlf if instr(strSort, "desc") then 'check the sort order, if its currently ascending, make the link descending Response.Write "<a href=" & strPageName & "?sort="& field.name & "&page=" & intCurrentPage & ">" & field.name & "</a>" & vbcrlf else Response.Write "<a href=" & strPageName & "?sort="& field.name &"desc&page=" & intCurrentPage & ">" & field.name & "</a>" & vbcrlf end if Response.Write "<td>" & vbcrlf next Response.Write "<tr>" 'records for i = intCurrentRecord to RS.PageSize 'display from the current record to the pagesize if not RS.eof then Response.Write "<tr>" & vbcrlf for each field in RS.Fields 'for each field in the recordset Response.Write "<td align=center>" & vbcrlf if lcase(strLinkedColumnName) = lcase(field.name) then 'if this field is the "linked field" provide a link Response.Write "<a href=" & strLink & "?sort="& strSort &"&page=" & intCurrentPage & "&" & field.name & "=" & field.value & " >" & field.value & "</a>" & vbcrlf else Response.Write field.value end if Response.Write "<td>" & vbcrlf next Response.Write "<tr>" & vbcrlf RS.MoveNext end if next Response.Write "<table>" & vbcrlf 'page navigation select case cint(intCurrentPage) case cint(intTotalPages) 'if its the last page give only links to movefirst and move previous strMoveFirst = "<a href=" & strPageName & "?sort="& strSort &"&page=1 >"& "First" &"</a>" strMoveNext = "" strMovePrevious = "<a href=" & strPageName & "?sort="& strSort &"&page=" & intCurrentPage - 1 & " >"& "Prev" &"</a>" strMoveLast = "" case 1 'if its the first page only give links to move next and move last strMoveFirst = "" strMoveNext = "<a href=" & strPageName & "?sort="& strSort &"&page=" & intCurrentPage + 1 & " >"& "Next" &"</a>" strMovePrevious = "" strMoveLast = "<a href=" & strPageName & "?sort="& strSort &"&page=" & intTotalPages & " >"& "Last" &"</a>" case else strMoveFirst = "<a href=" & strPageName & "?sort="& strSort &"&page=1 >"& "First" &"</a>" strMoveNext = "<a href=" & strPageName & "?sort="& strSort &"&page=" & intCurrentPage + 1 & " >"& "Next" &"</a>" strMovePrevious = "<a href=" & strPageName & "?sort="& strSort &"&page=" & intCurrentPage - 1 & " >"& "Prev" &"</a>" strMoveLast = "<a href=" & strPageName & "?sort="& strSort &"&page=" & intTotalPages & " >"& "Last" &"</a>" 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