<%@ LANGUAGE="VBSCRIPT" %> <% Option Explicit '--------------- ORGANISATION INFORMATION WEBSITE ---------------- ' File Name: ClubDisplayFormOracle.asp ' ' Purpose: ' Accesses Selective Organisation Information and displays it ' ' Arguments: ' Comments ' ' Author: Gurpreet Khaira ' DBC IT Delivery ' Date Created: ' 23/04/2007 ' ' Modification History: ' Sandi (09/05/07): Clean up HTML, moved inside folder /organisations, change the links from /forms to /organisations, removed the base link, add
, customize submit id) ' '---------------------------------------------------------------------- '---------------------------------------------------------------------- ' ASP preprocessing code '---------------------------------------------------------------------- ' -- Declare Variables Dim strOrgname, strStreet, intPass 'variables for Orgname, street and PASS Dim intDisplaySQL 'Should we Display the SQL Areament? ' -- Get values from Form strOrgname = "Sports and Social Clubs" strStreet = Request("txtStreet") 'set intPass to 1 so the form thinks it's a repeat visit intPass = "1" intDisplaySQL = Request("chkSQL") ' -- Take Action based on how we came into this page Select Case Trim(intPass) Case Trim("1") ' -- Repeat Visit, display database data DisplayDatabaseData Case Else ' -- First Time Visit, display HTML Form DisplayHTMLForm End Select ' -- Make sure nothing else gets processed Response.End '---------------------------------------------------------------------- ' ASP post processing code and sub routines and functions '---------------------------------------------------------------------- '---------------------------------------------------------------------- ' Sub Routine: DisplayHTMLForm ' Display HTML Form '---------------------------------------------------------------------- Sub DisplayHTMLForm() ' -- Get the list of organisations from the database. Dim strOrgnamesList 'String to hold the organisations list strOrgnamesList = GetOrganisationsListForComboBox () %> Organisations

Community Connects - Search for Organisations

Please choose filter criteria (or click the Search button to see all Clubs):


 
Copyright © 2007 Dartford Borough Council If only all councils were like Dartford
<% End Sub '---------------------------------------------------------------------- ' Sub Routine: DisplayDatabaseData ' Displays the data from the database '---------------------------------------------------------------------- Sub DisplayDatabaseData() ' -- Declare Variables Dim objConn ' Connection Object Dim objRS ' Recordset Object Dim strSQL ' SQL String to access the database Dim strConnection ' Connection string to access the database Dim i ' counter variable Dim RecordCount ' counter variable Dim strWhereOrgname ' Where clause for Area Dim strWhereStreet ' Where clause for Street Dim strFilter ' Filter String for Display ' -- Create objects Set objConn = Server.CreateObject("ADODB.Connection") Set objRS = Server.CreateObject("ADODB.Recordset") ' -- Connection String Value strConnection = "dsn=Cconnects;uid=cconnects;pwd=cconnects;" objConn.mode = 3 ' adModeReadWrite ' -- Open the Connection objConn.Open strConnection ' -- SQL Area now needs to include Area the user asked for strFilter = "FILTER CRITERIA: " If strOrgname = "0" Then strOrgname = "" If Trim(strOrgname) = "" and Trim(strStreet) = "" Then ' user did not type anything, display all clubs strSQL = "SELECT * FROM CCONNECTS WHERE AUTHORISED = 1 ORDER BY ORGNAME" strFilter = strFilter & " NONE " Else ' -- we have one or the other filled in. strSQL = "SELECT * FROM CCONNECTS" strWhereOrgname = "" strWhereStreet = "" ' -- Check the Organisation if Trim(strOrgname) <> "" Then strWhereOrgname = " CLASSID = '" & strOrgname & "' " strFilter = strFilter & " Classid = " & strOrgname End if ' -- Check the Street If Trim(strStreet) <> "" Then ' -- do we already have one where clause before this? if strWhereOrgname <> "" Then strWhereStreet = " AND " strFilter = strFilter & " and " end if strWhereStreet = strWhereStreet & _ " UPPER(ADDITIONALINFO) LIKE UPPER('%" & strStreet & "%') " strFilter = strFilter & " AdditionalInfo is like: *" & strStreet & "* " End if ' -- Now concatenate to get the real SQL Area strSQL = strSQL & " WHERE " & strWhereOrgname & strWhereStreet & " ORDER BY ORGNAME" End if ' -- Populate our Recordset with data set objRS = objConn.Execute (strSQL) if (objRS.BOF and objRS.EOF) then response.write "No records found" response.end End if '---------------------------------------------------------------------- ' Begin HTML output '---------------------------------------------------------------------- %> Organisations

Organisations list

<% ' -- Now output contents of the Recordset objRS.MoveFirst RecordCount = 0 Do While Not objRS.EOF RecordCount = RecordCount + 1 ' -- output the contents Response.Write "" Response.Write ("") Response.Write "" if IsNull(objRS.Fields(13)) then Response.Write "" else Response.Write "" End if if IsNull(objRS.Fields(25)) then Response.Write "" else Response.Write "" End if if IsNull(objRS.Fields(45)) then Response.Write "" else Response.Write "" End if Response.write "" ' -- move to the next record objRS.MoveNext Loop objRS.Close set objRS = Nothing objConn.Close set objConn = Nothing %>
  Name Telephone Email address Additional Information
" & objRS.Fields(2) & "" & "None" & "" & objRS.Fields(13) & "" & "None" & "" & objRS.Fields(25) & "" & "None" & "" & objRS.Fields(45) & "
Copyright © 2007 Dartford Borough Council If only all councils were like Dartford
<% '---------------------------------------------------------------------- ' End HTML Output '---------------------------------------------------------------------- End Sub '---------------------------------------------------------------------- ' Function : GetOrganisationsListForComboBox ' Gets a list of organisations from the database '---------------------------------------------------------------------- Function GetOrganisationsListForComboBox() ' -- Declare Variables Dim objConn ' Connection Object Dim objRS ' Recordset Object Dim strSQL ' SQL String to access the database Dim strConnection ' Connection string to access the database Dim i ' counter variable Dim strResult ' Function Return Value ' -- Do we have the value in our Application Object already? 'strResult = Application("OrganisationsList") 'If strResult = "" Then ' -- No, so get it from the database -- removed because the combo was not working with this ' -- Create objects Set objConn = Server.CreateObject("ADODB.Connection") Set objRS = Server.CreateObject("ADODB.Recordset") ' -- Connection String Value strConnection = "dsn=Cconnects;uid=cconnects;pwd=cconnects;" ' -- Open the Connection objConn.Open strConnection ' -- Populate our Recordset with data strSQL = "SELECT DISTINCT CLASSID FROM CCONNECTS" set objRS = objConn.Execute (strSQL) if (objRS.BOF and objRS.EOF) then response.write "Sorry: No Records Found." response.end End if ' -- Start building the result string strResult = "" Do While Not objRS.EOF strResult = strResult & _ "" objRS.MoveNext Loop ' -- Close objects objRS.Close set objRS = Nothing objConn.Close set objConn = Nothing ' -- Store the value in the Application object for next visit Application.Lock Application("OrganisationsList") = strResult Application.UnLock 'End If -- endif bit of if statement above, removed because the combo was not working with this ' -- return the value GetOrganisationsListForComboBox = strResult End Function %>