Know Your Councillor
<% Const MODE_DEFAULT = 1 Const MODE_RESULTS = 2 Const DB_NAME = "../D4T4B453/DocStore.mdb" ' Name of our database file Const SCRIPT_NAME = "council.asp" ' Name of this script Const RECORDS_PER_PAGE = 10 ' Number of records per page Dim nMode ' Current Mode Dim intDisplayPage ' Find out what mode we are in nMode = MODE_RESULTS ' Depending on our mode we will do different things Select Case nMode Case MODE_RESULTS ' This is where all the results will show ShowResults Case Else ' This one is for MODE_DEFAULT or invalid modes all the same ' By default display the search form ShowSearchForm End Select ' This function will generate our connection string ' it assumes that Access database is in the same folder as this script Private Function GetConnectionString() GetConnectionString = "Driver={Microsoft Access Driver (*.mdb)};" & _ "DBQ=" & Server.MapPath(DB_NAME) & ";" & _ "UID=;PWD=;" End Function ' This function will display the search form Private Function ShowSearchForm() %>
<% End Function ' This function will display the results of the search Private Function ShowResults() Dim strConn ' Database connection string Dim SQL ' String that will have our SQL statments Dim RS ' Recordset object Dim Keyword ' Keyword for search Dim nRecCount ' Number of records found Dim nPageCount ' Number of pages of records we have Dim nPage ' Current page number ' Let's see what page are we looking at right now nPage = CLng(Request.QueryString("Page")) ' Let's see what user wants to search for today :) Keyword = "asylum" ' define our SQL statment ' we will be looking for all the records in tblDocumentStore table ' where docName and Description contains our Keyword ' do not forget to fix tick marks (single quotes) in our Keyword SQL = "SELECT * FROM tblCouncillor ORDER BY tblCouncillor.ID_No;" ' Create our connection string strConn = GetConnectionString() ' Time to create and open recordset Set RS = Server.CreateObject("ADODB.Recordset") RS.CursorLocation = 3 ' adUseClient RS.Open SQL, strConn ' adOpenKeyset CursorType ' Start outputing HTML ' Did we find anything? If Not RS.Eof Then ' Let's deal with our findings ' Get records count nRecCount = RS.RecordCount ' Tell recordset to split records in the pages of our size RS.PageSize = RECORDS_PER_PAGE ' How many pages we've got nPageCount = RS.PageCount ' Make sure that the Page parameter passed to us is within the range If nPage < 1 Or nPage > nPageCount Then ' Ops - bad page number ' let's fix it nPage = 1 End If ' Time to tell user what we've got so far Response.Write "There are " & nPageCount & " page/s of results." Response.Write "You are on page " & nPage & "." ' Start Results Response.Write "
" & String(20,"-") ' Position recordset to the page we want to see RS.AbsolutePage = nPage ' Let's output our records ' Loop through records until it's a next page or End of Records Do While Not (RS.Eof OR RS.AbsolutePage <> nPage) ' All we do here is just show the records 'Response.Write "
" & RS("ItemName") Response.Write "
" & RS("ConName") Response.Write " - " & RS("ConEmail") Response.Write "
" & RS("ConAdd1") Response.Write "
" & RS("ConCompany") Response.Write "
" & RS("ConTel") & "
" ' Move on to the next record RS.MoveNext Loop Response.Write("
Pages: [ ") FOR intDisplayPage = 1 TO nPageCount IF Cint(intDisplayPage) = Cint(nPage) THEN Response.Write intDisplayPage ELSE Response.Write " " & intDisplayPage &_ " " END IF NEXT Response.Write (" ]") Else ' We did not find anything Response.Write "We have not found any Councillors containing your search word/s "" " & Keyword & " "".
" Response.Write "Please enter a new search word." End If RS.Close End Function %>
| 