Hamilton-Halton Home Builder's Association

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%> <% 'assemble MS Access database connection string Dim strConn, strPath strPath = Server.Mappath("database\hhhba.mdb") strConn="Provider=Microsoft.Jet.OLEDB.4.0;Data Source="&strPath 'start SQL string Dim strSQL strSQL = "SELECT CompanyID, CompanyName, CoContactLN, (CoContactFN+' '+CoContactLN) AS [CoContact1], CoAddress1, CoAddress2, CoCity, CoProvince, CoPostalCode, CoPhone, CoFax, CoWebSiteURL FROM MemberList" 'parse form request object Dim strWHERE, intCategoryID, strTxtQuery, strTxtQuery2, intSortBy strWHERE = "" strTxtQuery = "" strTxtQuery2 = "" intCategoryID = Cint(Request.Form("categorylist")) If Request.Form("txtquery") <> "" Then strTxtQuery = Cstr(Replace(Request.Form("txtquery"),"'","''")) End If intSortBy = Cint(Request.Form("sortby")) If intCategoryID <> 0 Then strWHERE = " WHERE (CategoryID_1 = "&intCategoryID&" OR CategoryID_2 = "&intCategoryID&" OR CategoryID_3 = "&intCategoryID&")" End If 'deal with text query and wildcards NOTE: wildcard option has been turned off... automatic wild cards are in If strTxtQuery <> "" Then 'If InStr(strTxtQuery,"*") > 0 Then 'strTxtQuery2 = Lcase(Replace(strTxtQuery,"*","%")) strEquiv = " LIKE " 'Else 'strTxtQuery2 = strTxtQuery 'strEquiv = " = " 'End If If Len(strTxtQuery) = 1 Then strTxtQuery2 = strTxtQuery&"%" Else strTxtQuery2 = "%"&strTxtQuery&"%" End If If strWHERE = "" Then strWHERE = " WHERE (CompanyName"&strEquiv&"'"&strTxtQuery2&"' OR CoContactLN"&strEquiv&"'"&strTxtQuery2&"')" Else strWHERE = strWHERE&" AND (CompanyName"&strEquiv&"'"&strTxtQuery2&"' OR CoContactLN"&strEquiv&"'"&strTxtQuery2&"')" End If End If 'deal with sort order Select Case intSortBy Case 1 strORDERBY = " ORDER BY CompanyName" Case 2 strORDERBY = " ORDER BY CoContactLN, CompanyName" Case 3 strORDERBY = " ORDER BY CoCity, CompanyName" Case Else strORDERBY = " ORDER BY CompanyName" End Select 'assemble SQL statement strSQL = strSQL&strWHERE&strORDERBY 'open database connection Dim Conn set Conn=Server.CreateObject("ADODB.Connection") Conn.ConnectionString = strConn Conn.Open 'get member list from database4 Dim rsMemberList Set rsMemberList = Server.CreateObject("ADODB.Recordset") Set rsMemberList.ActiveConnection = Conn rsMemberList.Open strSQL 'put results in an array If Not rsMemberList.EOF Then arrMemList = rsMemberList.GetRows() End If 'close recordset rsMemberList.Close() Set rsMemberList = Nothing 'get list of categories from database Dim rsCategories Set rsCategories = Server.CreateObject("ADODB.Recordset") Set rsCategories.ActiveConnection = Conn rsCategories.Open "SELECT CategoryID, CategoryName FROM Categories ORDER BY CategoryName" 'put results in an array If Not rsCategories.EOF Then arrCatList = rsCategories.GetRows() End If 'close recordset rsCategories.Close() Set rsCategories = Nothing 'close database connection Conn.Close() Set Conn = Nothing %> Hamilton-Halton Home Builder's Association - Membership Directory

Browse Member Directory by Category:	Search Membership:

Member List

Sort results by:

<%If IsArray(arrMemList) Then For x=0 To Ubound(arrMemList,2)%>

<%=arrMemList(1,x)%> <%=arrMemList(3,x)%>	<%=arrMemList(4,x)%><%If arrMemList(5,x) <> "" Then Response.Write(" "&arrMemList(5,x)) End If%> <%=arrMemList(6,x)%>, <%=arrMemList(7,x)%> <%=arrMemList(8,x)%>	ph: <%=arrMemList(9,x)%> <%If arrMemList(10,x) <> "" Then%>fax: <%=arrMemList(10,x)%> <%End If%><%=arrMemList(11,x)%>

<%Next Else%> There are no results to display. <%End If%>