%
'-- 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
%>