Welcome to the Redevelopment Authority
of the County of Fayette, PA
Contact

 Main Menu

   Add New Project



<% ' Constants ripped from adovbs.inc: Const adOpenStatic = 3 Const adLockReadOnly = 1 Const adCmdText = &H0001 ' Our own constants: Const PAGE_SIZE = 5 ' The size of our pages. ' Declare our variables... always good practice! Dim strURL ' The URL of this page so the form will work ' no matter what this file is named. Dim cnnSearch ' ADO connection Dim rstSearch ' ADO recordset Dim strDBPath ' path to our Access database (*.mdb) file Dim strSQL ' The SQL Query we build on the fly Dim strSearch ' The text being looked for Dim iPageCurrent ' The page we're currently on Dim iPageCount ' Number of pages of records Dim iRecordCount ' Count of the records returned Dim I ' Standard looping variable ' Retreive the URL of this page from Server Variables strURL = Request.ServerVariables("URL") ' Retreive the term being searched for. I'm doing it on ' the QS since that allows people to bookmark results. ' You could just as easily have used the form collection. strSearch = Request.QueryString("search") strSearch = Replace(strSearch, "'", "''") ' Retrieve page to show or default to the first If Request.QueryString("page") = "" Then iPageCurrent = 1 Else iPageCurrent = CInt(Request.QueryString("page")) End If ' Since I'm doing this all in one page I need to see if anyone ' has searched for something. If they have we hit the DB. ' O/W I just show the search form and quit. %>

Search by Municipality, Project Name, Location, or keyword

  
<% If strSearch <> "" Then ' MapPath of virtual database file path to a physical path. ' If you want you could hard code a physical path here. strDBPath = Server.MapPath("../projects/db/projects.mdb") ' Create an ADO Connection to connect to the sample database. ' We're using OLE DB but you could just as easily use ODBC or a DSN. Set cnnSearch = Server.CreateObject("ADODB.Connection") ' This line is for the Access sample database: cnnSearch.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDBPath & ";" ' Build our query based on the input. strSQL = "SELECT Municipality, [Project Type], [Project Name], ID " _ & "FROM projects " _ & "WHERE Municipality LIKE '%" & Replace(strSearch, "'", "''") & "%' " _ & "OR [Project Type] LIKE '%" & Replace(strSearch, "'", "''") & "%' " _ & "OR Other LIKE '%" & Replace(strSearch, "'", "''") & "%' " _ & "OR [Project Name] LIKE '%" & Replace(strSearch, "'", "''") & "%' " _ & "OR Location LIKE '%" & Replace(strSearch, "'", "''") & "%' " _ & "OR [Estimated Cost] LIKE '%" & Replace(strSearch, "'", "''") & "%' " _ & "OR [Estimated Time] LIKE '%" & Replace(strSearch, "'", "''") & "%' " _ & "OR Status LIKE '%" & Replace(strSearch, "'", "''") & "%' " _ & "OR Description LIKE '%" & Replace(strSearch, "'", "''") & "%' " _ & "OR [State Representative] LIKE '%" & Replace(strSearch, "'", "''") & "%' " _ & "OR [State Senator] LIKE '%" & Replace(strSearch, "'", "''") & "%' " _ & "OR [US Representative] LIKE '%" & Replace(strSearch, "'", "''") & "%' " _ & "OR [US Senator] LIKE '%" & Replace(strSearch, "'", "''") & "%' " _ & "OR [First Name] LIKE '%" & Replace(strSearch, "'", "''") & "%' " _ & "OR [Last Name] LIKE '%" & Replace(strSearch, "'", "''") & "%' " _ & "OR Title LIKE '%" & Replace(strSearch, "'", "''") & "%' " _ & "OR Address LIKE '%" & Replace(strSearch, "'", "''") & "%' " _ & "OR City LIKE '%" & Replace(strSearch, "'", "''") & "%' " _ & "OR State LIKE '%" & Replace(strSearch, "'", "''") & "%' " _ & "OR Zip LIKE '%" & Replace(strSearch, "'", "''") & "%' " _ & "OR Phone LIKE '%" & Replace(strSearch, "'", "''") & "%' " _ & "OR Email LIKE '%" & Replace(strSearch, "'", "''") & "%' " _ & "ORDER BY Municipality;" ' Execute our query using the connection object. It automatically ' creates and returns a recordset which we store in our variable. Set rstSearch = Server.CreateObject("ADODB.Recordset") rstSearch.PageSize = PAGE_SIZE rstSearch.CacheSize = PAGE_SIZE ' Open our recordset rstSearch.Open strSQL, cnnSearch, adOpenStatic, adLockReadOnly, adCmdText ' Get a count of the number of records and pages ' for use in building the header and footer text. iRecordCount = rstSearch.RecordCount iPageCount = rstSearch.PageCount If iRecordCount = 0 Then ' Display no records error. %>

No records found. Please try again.

<% Else ' Move to the page we need to show. rstSearch.AbsolutePage = iPageCurrent ' Show a quick status line letting people know where they are: %>

<%= iRecordCount %> Records Found. Displaying page <%= iPageCurrent %> of <%= iPageCount %>:

<% ' Display a table of the data in the recordset. We loop through the ' recordset displaying the fields from the table and using MoveNext ' to increment to the next record. We stop when we reach EOF. ' For fun I'm combining some fields and showwing you can do more then ' just spit out the data in the form it is in in the table. %>
<% Do While Not rstSearch.EOF And rstSearch.AbsolutePage = iPageCurrent %> <% rstSearch.MoveNext Loop %>
<%= rstSearch.Fields("Municipality").Value %>
<%= rstSearch.Fields("Project Name").Value %>
<%= rstSearch.Fields("Project Type").Value %>
" method="post">
" method="post">

<% ' Now we need to show our navigation links: ' Show "previous" and "next" page links which pass the page to ' view our search parameter. You could also use form buttons ' but I find this looks better. If iPageCurrent > 1 Then %> [<< Prev] <% End If ' You can also show page numbers: For I = 1 To iPageCount If I = iPageCurrent Then %> <%= I %> <% Else %> <%= I %> <% End If Next 'I If iPageCurrent < iPageCount Then %> [Next >>] <% End If %>

<% End If rstSearch.Close Set rstSearch = Nothing cnnSearch.Close Set cnnSearch = Nothing End If %>

Redevelopment Authority of the County of Fayette, Pennsylvania
500 Court Plaza Tower, 45 East Main Street, Uniontown, Pennsylvania 15401
Telephone: (724) 437-1547     Facsimile: (724) 437-0731

All Rights Reserved. Copyright ©2003-2004 RACF