<% 'Database Variables dim conn dim rs dim ConStr dim sql dim sqlcount dim rsnum 'other variables dim addor dim addwhere dim bgcolor dim celltext dim curdate dim curdatetxt dim CurPage dim CurRec dim daycount dim failed dim failmsg dim FinalNum dim NumPages dim RecCount dim startdate dim expirydate dim thedate dim adate dim pdate dim ndate dim linktext 'constants const DaysInWeek = 7 const WeeksInMonth = 6 const MaxItems = 7 'initialise variables failmsg = "" CurPage = CInt(Request.QueryString("Page")) if CurPage < 1 then CurPage = 1 FinalNum = 0 CurRec = 0 RecCount = 0 NumPages = 0 adate = cdate(Request.QueryString("Date")) TheDate = CDate(DatePart("m",adate)&"/"&DatePart("d",adate)&"/"&DatePart("yyyy",adate)) linktext = "" ConStr = "DRIVER={Microsoft Access Driver (*.mdb)};DBQ="&server.MapPath("items.mdb") set conn=Server.CreateObject("ADODB.Connection") conn.open ConStr if request.Form("filter") = "Search" then addor = false addwhere = true sqlcount = "select Count(*) AS RecCount from Content" sql = "SELECT * FROM Content" if trim(request.Form("username")) <> "" then if addwhere then sql = sql& " WHERE" sqlcount = sqlcount & " WHERE" addwhere = false end if sql = sql & " UserName LIKE '%"&trim(request.Form("username"))&"%'" sqlcount = sqlcount & " UserName LIKE '%"&trim(request.Form("username"))&"%'" addor = true end if if request.Form("rgpItemType") <> "none" then if addwhere then sql = sql& " WHERE" sqlcount = sqlcount & " WHERE" addwhere = false end if if addor then sql = sql& " OR " sqlcount = sqlcount & " OR " end if sql = sql & " Type = '"&request.Form("rgpItemType")&"' OR Type = 'Both'" sqlcount = sqlcount & " Type = '"&request.Form("rgpItemType")&"' OR Type = 'Both'" addor = true end if if trim(request.Form("title")) <> "" then if addwhere then sql = sql& " WHERE" sqlcount = sqlcount & " WHERE" addwhere = false end if if addor then sql = sql& " OR " sqlcount = sqlcount & " OR " end if sql = sql & " Title LIKE '%"&trim(request.Form("title"))&"%'" sqlcount = sqlcount & " Title LIKE '%"&trim(request.Form("title"))&"%'" addor = true end if if isdate(trim(request.Form("date"))) then if addwhere then sql = sql& " WHERE" sqlcount = sqlcount & " WHERE" addwhere = false end if if addor then sql = sql& " OR " sqlcount = sqlcount & " OR " end if sql = sql & " StartDate <= #"&trim(request.Form("date"))&"# AND ExpiryDate >= #"&trim(request.Form("date"))&"#" sqlcount = sqlcount & " StartDate <= #"&trim(request.Form("date"))&"# AND ExpiryDate >= #"&trim(request.Form("date"))&"#" end if sql = sql & " ORDER BY StartDate DESC" else sqlcount = "select Count(*) AS RecCount from Content" sql = "SELECT * FROM Content ORDER BY StartDate DESC" end if set rsnum = Server.CreateObject("ADODB.RecordSet") rsnum.Open sqlcount, conn 'get number of records set rs = Server.CreateObject("ADODB.RecordSet") rs.Open sql, conn 'get records if not rs.BOF then 'check that there are records' rsnum.MoveFirst rs.MoveFirst RecCount = cint(rsnum("RecCount")) rs.MoveFirst CurRec = MaxItems * (CurPage -1) + 1 NumPages = int(RecCount/MaxItems) + 1 FinalNum = RecCount mod MaxItems for i = 1 to CurRec-1 rs.MoveNext next else failed = true failmsg = "There are no items in this category" end if %> Edit Items

Items List

<% if failed then %> <% else 'response.Write(sql) while ((not rs.EOF)and(CurRec <= MaxItems*CurPage)) startdate = datepart("m",rs("StartDate"))&"/"&datepart("d",rs("StartDate"))&"/"&datepart("yyyy",rs("StartDate")) expirydate = datepart("m",rs("ExpiryDate"))&"/"&datepart("d",rs("ExpiryDate"))&"/"&datepart("yyyy",rs("ExpiryDate")) response.Write(chr(9)&"") response.Write("") response.Write(""&chr(13)) response.Write(chr(9)&chr(9)&chr(9)&chr(9)&"") response.Write("") response.Write(""&chr(13)) response.Write(chr(9)&chr(9)&chr(9)&chr(9)&"") response.Write("") response.Write("") response.Write(""&chr(13)) response.Write(chr(9)&chr(9)&chr(9)&chr(9)&"") response.Write("") response.Write("") response.Write(""&chr(13)) response.Write(chr(9)&chr(9)&chr(9)&chr(9)&"") response.Write("") response.Write("") response.Write(""&chr(13)) response.Write(chr(9)&chr(9)&chr(9)&chr(9)&"") response.Write("") response.Write("") response.Write(""&chr(13)) n = 0 for i = 1 to 3 if rs("FileName"&i) = "No Attachment" then n = n + 1 else response.Write(chr(9)&chr(9)&chr(9)&chr(9)) response.write("") response.Write(chr(13)) end if next if n = 3 then response.Write(chr(9)&chr(9)&chr(9)&chr(9)) response.Write("") response.Write(chr(13)) end if response.Write(chr(9)&chr(9)&chr(9)&chr(9)&"") response.Write("") response.Write(""&chr(13)&chr(13)&chr(9)&chr(9)&chr(9)) response.Write(chr(9)&chr(9)&chr(9)&chr(9)&"") response.Write("") response.Write(""&chr(13)&chr(13)&chr(9)&chr(9)&chr(9)) rs.MoveNext CurRec = CurRec + 1 wend end if %>
<%= failmsg %>
Title: "&rs("Title")&"
Author: "&rs("UserName")&"
Details: "&rs("Header")&"
Start Date:"&startdate&"
End Date"&expirydate&"
Type"&rs("Type")&"
"&rs("FileName"&i)&"
No attached files.
Delete  Edit
 
Pages: <% for i = 1 to NumPages if i > 1 then response.Write(", ") if i = CurPage then response.Write(cstr(i)) else linktext = ""&cstr(i)&"" response.Write(linktext) end if next rs.Close set rs = nothing rsnum.Close set rsnum = nothing conn.Close set conn = nothing %>

Filter Items

Main Page

Add Item