<%@ Language="VBScript"%> <% ' Declare variables Dim iPageSize 'How big our pages are Dim strPageCount 'The number of pages we get back Dim strRecordCount 'The number of records returned Dim strPageCurrent 'The page we want to show Dim strSql 'SQL select to limit fields Dim conn 'The connection object Dim rs 'The recordset object Dim x 'Standard looping var Dim strSqlWhere Dim strSqlGroupBy Dim strSqlOrderBy Dim strSearchCriteria Dim strSearchSort Dim strSearch_authorize Dim strSearch_state Dim srtErrMsg Dim ConnString Dim strBackURL Dim strForwardURL Dim strFirstURL Dim strLastURL Dim strNumPages Dim strOrderBy_authorize Dim strOrderBy_state ' using a DSN-less connection. ConnString = "DRIVER=Microsoft Access Driver (*.mdb);DBQ=" & Server.MapPath("data\gsi.mdb") 'set page size iPageSize = 200 If Request("page") = "" Then strPageCurrent = 1 Else strPageCurrent = CInt(Request("page")) End If ' default sql statements strSql = "SELECT DISTINCT state " strSql = strsql & "FROM survey" strSqlWhere = " WHERE authorize = 'YES'" strSqlGroupBy = " GROUP BY state " strSqlOrderBy = "" ' default back and forward URLs strBackURL = "index.asp?page=" & strPageCurrent - 1 strForwardURL = "index.asp?page=" & strPageCurrent + 1 strSearch_authorize = CStr(Request("authorize")) strSearch_state = CStr(Request("state")) strSearchSort = CStr(Request("sortby")) strSearchStartPage = "index.asp" If len (strSearchSort) > 0 Then strSqlOrderBy = " Order by " & strSearchSort End If If strSearchSort = "authorize asc" Then strOrderBy_authorize = "index.asp?sortby=authorize desc" Else strOrderBy_authorize = "index.asp?sortby=authorize asc" End If If strSearchSort = "state asc" Then strOrderBy_state = "index.asp?sortby=state desc" Else strOrderBy_state = "index.asp?sortby=state asc" End If strBackURL = strBackURL & "&sortby=" & strSearchSort & "&authorize" & strSearch_authorize & "&state" & strSearch_state strForwardURL = strForwardURL & "&sortby=" & strSearchSort & "&authorize" & strSearch_authorize & "&state" & strSearch_state strOrderBy_authorize = strOrderBy_authorize& "&authorize" & strSearch_authorize & "&state" & strSearch_state strOrderBy_state = strOrderBy_state& "&authorize" & strSearch_authorize & "&state" & strSearch_state 'Open the connection string Set conn = Server.CreateObject("ADODB.Connection") conn.Open ConnString set cmd = server.CreateObject("adodb.command") set cmd.ActiveConnection = conn ' set where and order by clauses if necessary strSql = strSql & strSqlWhere & strSqlGroupBy If len(strSqlOrderBy) > 0 Then strSql = strSql & strSqlOrderBy End If 'Response.Write ("
" & strSql & "
") With cmd .CommandType = adCmdText .CommandText = strSQL End With Set rs = Server.CreateObject("ADODB.Recordset") ' Set cursor location and pagesize rs.CursorLocation = adUseClient rs.PageSize = iPageSize ' Open Recordset object and rs.Open cmd 'Disconnect from database cmd.ActiveConnection = nothing Set conn = Nothing If (rs.eof and rs.bof) Then 'No Data rs.Close Set rs = Nothing %> No Data Available...
 
There is no data available for your search selection.

<% Response.Flush Response.End End If ' Get the count of the pages using the given page size strPageCount = rs.PageCount strRecordCount = rs.RecordCount strCurrentPageMinRecord = strPageCurrent * iPageSize - (iPageSize -1) strCurrentPageMaxRecord = strPageCurrent * iPageSize If strRecordCount < strCurrentPageMaxRecord Then strCurrentPageMaxRecord = strRecordCount strFirstURL = "index.asp?page=1" strLastURL = "index.asp?page=" & strPageCount ' If the request page falls outside the range, ' give them the closest match (1 or max) If 1 > strPageCurrent Then strPageCurrent = 1 If strPageCurrent > strPageCount Then strPageCurrent = strPageCount ' Move to the selected page rs.AbsolutePage = strPageCurrent ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''' Error on line above with empty recordset ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' %> Green Suites Certified Green Hotels
 
Please select a STATE or PROVINCE below for a listing of Green Suites Certified Green Hotels:

 
<% ' Loop through our records Do While rs.AbsolutePage = strPageCurrent And Not rs.EOF %>

<% 'Move to the next record! rs.MoveNext Loop %>
<% ' Close all objects and clear from Memory rs.Close Set rs = Nothing %>