<% '-- function to clean apostrophies, brackets and leading/trailing spaces in an entry function CleanUp (targetVariable) Cleaned = targetVariable if isNull (Cleaned) then else Cleaned = Replace(Cleaned, "'", "''") Cleaned = Replace(Cleaned, "+", " ") Cleaned = Replace(Cleaned, "[", "") Cleaned = Replace(Cleaned, "]", "") Cleaned = Replace(Cleaned, "%5B","") Cleaned = Replace(Cleaned, "%5D","") cleaned = Ltrim (cleaned) cleaned = Rtrim (cleaned) 'response.write """"&cleaned&"""
" 'response.Flush() end if Cleanup = Cleaned end function function CleanData (targetVariable) Cleanedup = targetVariable if isNull (Cleanedup) then else Cleanedup = Ltrim (Cleanedup) Cleanedup = Rtrim (Cleanedup) 'response.write """"&cleaned&"""
" 'response.Flush() end if CleanData = Cleanedup end function ' IF SQL SERVER USE SOMETHING LIKE THIS 'Data Source = Server Name 'Initial Catalog = Database 'Use your own user name and password 'sConnString = "Provider=SQLOLEDB;User ID=sa;password='';Initial Catalog=BB50;Data Source = blackboard.grinnell.edu;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096" 'Response.Write (sConnString&"
") 'on error resume next 'IN ACCESS USE SOMETHING LIKE THIS: 'Change Data Source to full path of database databasesource = server.MapPath(".\obitdatabase\obituaryproject.mdb") sConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="&databasesource set objRS=Server.CreateObject ("ADODB.Recordset") set objConn=Server.CreateObject("ADODB.Connection") objConn.Open sConnString 'Creates a read-only, forward only recordset selectstatement = "SELECT * from [Obituary Table] WHERE 1=1 " searchstatement = "Your search for records with a " 'Last_Name Like 'h%' ORDER BY Last_Name, First_Name;" if request.form("firstname") <> "" then selectstatement = selectstatement &"AND UCase([First_Name]) LIKE '"&UCase(CleanUp(request.form("FirstName")))&"%' " searchstatement = searchstatement & "first name starting with "&trim(request.form("firstname"))& ", " end if if request.form("lastname") <> "" then selectstatement = selectstatement &"AND UCase([Last_Name]) LIKE '"&UCase(CleanUp(request.form("lastName")))&"%' " searchstatement = searchstatement & "last name starting with "&trim(request.form("lastname"))& ", " end if if request.form("death") <> "" then deathyear = trim(request.form("death")) deathyearearly = CInt(deathyear) - CInt(request.form("date_range")) deathyearlate = CInt(deathyear) + CInt(request.form("date_range")) selectstatement = selectstatement &"AND (Date_of_Death between #1/1/"&deathyearearly&"# and #12/31/"&deathyearlate&"#) " searchstatement = searchstatement & "year of death in "&trim(request.form("death"))& ", " if request.form("date_range") = "0" then else searchstatement = Left(searchstatement, len(searchstatement)-2) & " (plus or minus "&trim(request.form("date_range"))& " years), " end if end if if request.form("birth") <> "" then birthyear = trim(request.form("birth")) birthyearearly = CInt(birthyear)- CInt(request.form("date_range")) birthyearlate = CInt(birthyear) + CInt(request.form("date_range")) selectstatement = selectstatement &"AND (Date_of_birth between #1/1/"&birthyearearly&"# and #12/31/"&birthyearlate&"#) " searchstatement = searchstatement & "year of birth in "&trim(request.form("death"))& ", " if request.form("date_range") = "0" then else searchstatement = Left(searchstatement, len(searchstatement)-2) & " (plus or minus "&trim(request.form("date_range"))& " years), " end if end if if request.form("maidenName") <> "" then selectstatement = selectstatement &"AND UCase([Maide_ Name]) LIKE '"&UCase(CleanUp(request.form("maidenName")))&"%' " searchstatement = searchstatement & "maiden name starting with "&trim(request.form("maidenName"))& ", " end if if request.form("spousename") <> "" then selectstatement = selectstatement & " AND ( UCase([spouse-1]) LIKE '%"&UCase(CleanUp(request.form("spousename")))&"%' OR UCase([spouse-2]) LIKE '%"&UCase(CleanUp(request.form("spousename")))&"%' OR UCase([spouse-3]) LIKE '%"&UCase(CleanUp(request.form("spousename")))&"%' ) " searchstatement = searchstatement & "spouse's last name starting with "&trim(request.form("spousename"))& ", " end if if request.form("otherinfo") <> "" then selectstatement = selectstatement &"AND UCase([Accomplishments]) LIKE '%"&UCase(CleanUp(request.form("otherinfo")))&"%' " searchstatement = searchstatement & "other information including "&trim(request.form("otherinfo"))& ", " end if selectstatement = selectstatement &"ORDER BY Last_Name, First_Name;" searchstatement = Left(searchstatement, len(searchstatement)-2) & ", found the following records.

" if len (selectstatement) < 95 then selectstatement = "SELECT last_name, first_name from [Obituary Table] WHERE 1=2;" searchstatement = "Please select some search fields and try again.

" else end if ' date range query 'SELECT [Obituary Table].Last_Name, [Obituary Table].Date_of_Birth, [Obituary Table].Date_of_Death FROM [Obituary Table] WHERE ((([Obituary Table].Date_of_Birth) Between #2/2/1999# And #12/1/1999#)); Set rs = objConn.execute (selectstatement) 'response.write ("got past execute : " &selectstatement) response.flush () 'response.End() ' = objConn.execute ("SELECT POSTED_NAME, sum(HIT_COUNT) AS hits FROM MSG_MAIN Group BY COURSE order by hits desc;") 'Response.write (rs.fields.count&" there
") 'rs.movefirst Response.write searchstatement Do While Not rs.EOF 'Response.write ("" & rs("COURSE_ID").Value&" "& rs("COURSE_NAME").Value &vbCrLf) Response.write (""&CleanData(rs("Last_Name").Value)&", "& CleanData(rs("First_Name").Value)& "") if not isnull(rs("date_of_birth").Value) then Response.write "   birth: "&CleanData(rs("date_of_birth").Value) else end if if not isnull(rs("date_of_death").Value) then Response.write "   death: "&CleanData(rs("date_of_death").Value) if rs("DOD_not_listed").Value then Response.write "* " footnote = true else end if else end if if not isnull(rs("Maide_ Name").Value) then Response.write "   maiden name:  "&CleanData(rs("Maide_ Name").Value) else end if if not isnull(rs("spouse-1").Value) then Response.write "   spouse: "&CleanData(rs("spouse-1").Value) if not isnull(rs("spouse-2").Value) then Response.write "; "& CleanData(rs("spouse-2").Value) if not isnull(rs("spouse-3").Value) then Response.write "; "& CleanData(rs("spouse-3").Value) else end if else end if else end if if not isnull(rs("Residence_at_time_of_death").Value) then Response.write "   residence: "&CleanData(rs("Residence_at_time_of_death").Value) else end if if not isnull(rs("Accomplishments").Value) then Response.write "   other: "&CleanData(rs("Accomplishments").Value) else end if 'Response.write "   record: "&rs("ID").Value&"

" Response.write "   record: "&CleanData(rs("ID").Value)&"

" response.Flush() rs.MoveNext Loop if footnote then response.Write("* Date was not in obituary. Used newspaper publication date.

") else end if Set rs = Nothing objConn.Close Set objConn = Nothing %>