<%@LANGUAGE="JavaScript"%>
<%
'use strict';
//*****WORSHIP DB INTERFACE - Don A. Milnor - 2019*****
// This will probably be a work in progress for quite some time.
// begin global variables
var gsConn;
// NOTE THE EMPTY STRING PUT IN FRONT OF MOST OF THE STRING SERVER VARIABLES BELOW.
// (We would have problems later otherwise.)
var gbEditAdmin;
var gsAltSongTitle1 = "" + Request.Form("rfAltSongTitle1");
var gsAltSongTitle2 = "" + Request.Form("rfAltSongTitle2");
var gsBookID1 = "" + Request.Form("rfBook1");
var gsBookID2 = "" + Request.Form("rfBook2");
var gsBookID3 = "" + Request.Form("rfBook3");
var gsBookName = "" + Request.Form("rfBookName");
var gsFirstName = "" + Request.Form("rfFirstName");
var gsDemo = "" + Request.Form("rfDemo");
var gsDemoText;
var gsDescription = "" + Request.Form("rfDescription");
var gsEditOriginalID = "" + Request.Form("rfEditOriginalID");
var gsEditPassword = "" + Request.Form("rfEditPassword");
var gsEditPersonID = "" + Request.Form("rfEditPersonID");
var gsEditUserName = "" + Request.Form("rfEditUserName");
var gsID = "" + Request.Form("rfID");
var gsInstance = "" + Request.Form("rfInstance");
var gsKey = "" + Request.Form("rfKey");
var gsLastName = "" + Request.Form("rfLastName");
var gsLyrics = "" + Request.Form("rfLyrics");
var gsNotes = "" + Request.Form("rfNotes");
var gsNotForGuests;
var gsOnlyForAdmins;
var gsPageNo1 = "" + Request.Form("rfPageNo1");
var gsPageNo2 = "" + Request.Form("rfPageNo2");
var gsPageNo3 = "" + Request.Form("rfPageNo3");
var gsPassword = "" + Request.Form("rfPassword");
var gsPassword2 = "" + Request.Form("rfPassword2");
var gsSongbookID = "" + Request.Form("rfSongbookID");
var gsSongbookMaintType = "" + Request.Form("rfSongbookMaintType");
var gsSongID = "" + Request.Form("rfSongID");
var gsSongTitle = "" + Request.Form("rfSongTitle");
var gsUserRecMaintType = "" + Request.Form("rfUserRecMaintType");
var gsUserType = "" + Request.Form("rfUserType");
var gsWorshipDate = "" + Request.Form("rfWorshipDate");
// end global variables
// begin main function
if(Request.Form("rfEditAdmin") == "on") // checked checkbox
gbEditAdmin = true;
else
gbEditAdmin = false;
if(Request.QueryString("Demo") == "Yes")
gsDemo = "Yes";
gsConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
DBPath() + ";Jet OLEDB:Database Password=''"; // connection string
if(gsDemo == "Yes")
gsDemoText = " - Demo Version";
else
gsDemoText = "";
if(gsUserType == "Guest")
gsNotForGuests = " disabled";
else
gsNotForGuests = "";
if(gsUserType == "Normal")
gsOnlyForAdmins = " disabled";
else
gsOnlyForAdmins = "";
switch(gsInstance)
{
case "iAdminFunctions":
AdminFunctions();
break;
case "iChangePassword":
ChangePassword();
break;
case "iCheckAndSaveSong":
CheckAndSaveSong();
break;
case "iCheckChangedPassword":
CheckChangedPassword();
break;
case "iCheckPassword":
CheckPassword();
break;
case "iDisplayWorshipService":
DisplayOneWorshipService();
break;
case "iDisplaySongUsage":
DisplaySongUsage();
break;
case "iUserRecordPart2":
UserRecordPart2();
break;
case "iEditSong":
EditSong();
break;
case "iEditSongs":
EditSongs();
break;
case "iListSongUsage":
ListSongUsage();
break;
case "iMainMenu":
MainMenu();
break;
case "iUserRecordPart3":
UserRecordPart3();
break;
case "iSongbookPart1":
SongbookPart1();
break;
case "iSongbookPart2":
SongbookPart2();
break;
case "iSongbookPart3":
SongbookPart3();
break;
case "iSuccessfulChangedPassword":
SuccessfulChangedPassword();
break;
case "iUserRecordPart1":
UserRecordPart1();
break;
case "iWorshipServiceByDate":
WorshipServiceByDate();
break;
default:
LogIn();
break;
}
// end main function
// ALL THE FUNCTIONS BELOW ARE IN ALPHABETICAL ORDER
function AdminChecked(psAdmin)
{
var sOut;
if(psAdmin == true)
sOut = " checked";
else
sOut = "";
return sOut;
} // AdminChecked
function AdminFunctions()
{
%>
Worship DB Interface - <%=EndTitleText()%>
Admin Functions<%=gsDemoText%>
<%
} // AdminFunctions
function BlankIsNull(psInput)
{
var sOutput;
if(psInput == '')
sOutput = "NULL";
else
sOutput = psInput;
return sOutput;
} // BlankIsNull
function ChangePassword()
{
%>
Worship DB Interface - <%=EndTitleText()%>
Change Password<%=gsDemoText%>
<%
} // ChangePassword
function CheckAndSaveSong()
{
var sErrorMessage;
if((gsPageNo1 != ""
&& gsBookID1 == "")
||(gsPageNo2 != ""
&& gsBookID2 == "")
||(gsPageNo3 != ""
&& gsBookID3 == "")
||(gsPageNo1 == ""
&& gsBookID1 != "")
||(gsPageNo2 == ""
&& gsBookID2 != "")
||(gsPageNo3 == ""
&& gsBookID3 != ""))
sErrorMessage = "Page number has no book ID, and/or book ID has no page number. Please correct.";
else if(gsSongTitle == "")
sErrorMessage = "Song title required. Please correct.";
else
sErrorMessage = ""
if(sErrorMessage != "") // if there's an error
{
%>
Worship DB Interface - <%=EndTitleText()%>
<%=sErrorMessage%><%=gsDemoText%>
<%
return;
}
// if we get here, no issues with book ID, page number or title; save...
var cn = new ActiveXObject("ADODB.Connection");
cn.Open(gsConn);
cn.Execute("UPDATE Songs SET SongTitle = " + Literal(gsSongTitle) +
", AltSongTitle1 = " + Literal(gsAltSongTitle1) +
", AltSongTitle2 = " + Literal(gsAltSongTitle2) +
", Description = " + Literal(gsDescription) +
", [Key] = " + Literal(gsKey) +
", PageNo1 = " + Literal(gsPageNo1) +
", BookID1 = " + BlankIsNull(gsBookID1) +
", PageNo2 = " + Literal(gsPageNo2) +
", BookID2 = " + BlankIsNull(gsBookID2) +
", PageNo3 = " + Literal(gsPageNo3) +
", BookID3 = " + BlankIsNull(gsBookID3) +
", Lyrics = " + Literal(gsLyrics) +
" WHERE ID = " + gsSongID);
cn.Close;
%>
Worship DB Interface - <%=EndTitleText()%>
Song "<%=gsSongTitle%>" Updated<%=gsDemoText%>
<%
} // CheckAndSaveSong
function CheckChangedPassword()
{
var sErrorMessage;
if(gsPassword != gsPassword2)
sErrorMessage = "Passwords do not match.";
else if(gsPassword == "")
sErrorMessage = "Password cannot be blank.";
else
sErrorMessage = "";
if(sErrorMessage == "") // password fine
{
var cn = new ActiveXObject("ADODB.Connection");
cn.Open(gsConn);
cn.Execute("UPDATE Users SET UserPW = " + Literal(gsPassword) +
" WHERE UserName = " + Literal(gsID));
cn.Close;
%>
Worship DB Interface - <%=EndTitleText()%>
<%
}
} // CheckChangedPassword
function CheckPassword()
{
var cn = new ActiveXObject("ADODB.Connection");
var rs = new ActiveXObject("ADODB.Recordset");
var sID = "" + Request.Form("rfID");
var sPassword = "" + Request.Form("rfPassword");
cn.Open(gsConn);
rs.Open("SELECT p.LastName, p.FirstName, u.Admin" +
" FROM Persons AS p" +
" INNER JOIN Users AS u" +
" ON u.PersonID = p.ID" +
" WHERE UserName = " + Literal(sID) +
" AND UserPW = " + Literal(sPassword), cn,
2, // ADODB.CursorTypeEnum.adOpenDynamic
3); // ADODB.LockTypeEnum.adLockOptimistic
if(rs.BOF && rs.EOF) // ID/PW not found
InvalidIDPassword();
else
{
rs.MoveFirst;
gsFirstName = rs("FirstName");
gsLastName = rs("LastName");
if(rs("Admin")==true)
{
gsUserType = "Admin";
gsOnlyForAdmins = "";
}
else
{
gsUserType = "Normal";
gsOnlyForAdmins = " disabled";
}
MainMenu();
}
rs.Close;
cn.Close;
} // CheckPassword
function DBPath()
{
// This reads in the local path to the .asp file, and calculates a path
// to the Access file, which is up one level, then in the folder "DB".
// NOTE THE EMPTY STRING PUT IN FRONT OF THE SERVER VARIABLE BELOW.
var sInputPath = "" + Request.ServerVariables("PATH_TRANSLATED");
var sOutput;
var x = sInputPath.length - 1;
// find 2nd to last backslash in input path
while (sInputPath.substr(x, 1) != "\\")
x--;
x--;
while (sInputPath.substr(x, 1) != "\\")
x--;
if(gsDemo == "Yes"
|| Request.QueryString("Demo") == "Yes")
sOutput = sInputPath.substr(0, x + 1) + "db\\worship_demo.mdb";
else
sOutput = sInputPath.substr(0, x + 1) + "db\\worship.mdb";
return sOutput;
} // DBPath
function DisplayOneWorshipService()
{
%>
Worship DB Interface - <%=EndTitleText()%>
Worship Service - <%=gsWorshipDate%><%=gsDemoText%>
Leader Name
Activity
Song Title
Key
Page #
<%
var cn = new ActiveXObject("ADODB.Connection");
var rs = new ActiveXObject("ADODB.Recordset");
cn.Open(gsConn);
rs.Open("SELECT p.FirstName + ' ' + p.LastName AS LeaderName, wa.Activity," +
" IIf(ISNULL(s.SongTitle),'',s.SongTitle) AS SongTitle," +
" IIf(ISNULL(s.Key),'',s.Key) AS [Key]," +
" IIf(ISNULL(s.PageNo1),'',s.PageNo1) AS PageNo" +
" FROM ((WorshipActivityInstances AS wai" +
" INNER JOIN WorshipActivities AS wa" +
" ON wai.ActivityID = wa.ID)" +
" INNER JOIN Persons AS p" +
" ON wai.PersonID = p.ID)" +
" LEFT JOIN Songs AS s" +
" ON wai.SongID = s.ID" +
" WHERE Format(wai.ActivityDate,'yyyy-mm-dd') = " + Literal(gsWorshipDate) +
" ORDER BY wai.ActivityDate DESC, wai.WorshipOrder", cn,
2, // ADODB.CursorTypeEnum.adOpenDynamic
3); // ADODB.LockTypeEnum.adLockOptimistic
rs.MoveFirst;
while(!rs.EOF)
{
Response.Write("
\n" +
"
" + rs("LeaderName") + "
\n" +
"
" + rs("Activity") + "
\n" +
"
" + rs("SongTitle") + "
\n" +
"
" + rs("Key") + "
\n" +
"
" + rs("PageNo") + "
\n
\n");
rs.MoveNext;
}
%>
<%
} // DisplayOneWorshipService
function DisplaySongUsage()
{
%>
Worship DB Interface - <%=EndTitleText()%>
Song Usage - <%=SongTitleOfID(gsSongID)%> (<%=gsSongID%>)<%=gsDemoText%>
Date
Worship Leader
<%
var cn = new ActiveXObject("ADODB.Connection");
var rs = new ActiveXObject("ADODB.Recordset");
cn.Open(gsConn);
rs.Open("SELECT Format(wai.ActivityDate, 'yyyy-mm-dd') AS ActivityDate_YYYYMMDD," +
" p.FirstName + ' ' + p.LastName AS Name" +
" FROM WorshipActivityInstances AS wai" +
" INNER JOIN Persons AS p" +
" ON p.ID = wai.PersonID" +
" WHERE wai.SongID = " + gsSongID +
" ORDER BY wai.ActivityDate DESC", cn,
2, // ADODB.CursorTypeEnum.adOpenDynamic
3); // ADODB.LockTypeEnum.adLockOptimistic
rs.MoveFirst;
while(!rs.EOF)
{
Response.Write("
\n" +
"
" + rs("ActivityDate_YYYYMMDD") + "
\n" +
"
" + rs("Name") + "
\n
\n");
rs.MoveNext;
}
%>
<%
} // DisplaySongUsage
function EditSong()
{
var cn = new ActiveXObject("ADODB.Connection");
var rs = new ActiveXObject("ADODB.Recordset");
cn.Open(gsConn);
rs.Open("SELECT *" +
" FROM Songs" +
" WHERE ID = " + gsSongID, cn,
2, // ADODB.CursorTypeEnum.adOpenDynamic
3); // ADODB.LockTypeEnum.adLockOptimistic}
rs.MoveFirst;
%>
Worship DB Interface - <%=EndTitleText()%>
Edit Song - <%=SongTitleOfID(gsSongID)%> (<%=gsSongID%>)<%=gsDemoText%>
<%
rs.Close;
cn.Close;
} // EditSong
function EditSongs()
{
var cn = new ActiveXObject("ADODB.Connection");
var rs = new ActiveXObject("ADODB.Recordset");
cn.Open(gsConn);
rs.Open("SELECT ID, SongTitle" +
" FROM Songs" +
" ORDER BY SongTitle", cn,
2, // ADODB.CursorTypeEnum.adOpenDynamic
3); // ADODB.LockTypeEnum.adLockOptimistic}
%>
Worship DB Interface - <%=EndTitleText()%>
Edit Songs<%=gsDemoText%>
<%
} // EditSongs
function EndTitleText()
{
// This function creates the text for the end of the title tag.
// If the user is an admin, it will display the name, followed by
// admin. Example: "Schmoe, Joe (admin)". If the user is not an
// admin, just the name, e.g., "Schmoe, Joe". If the user is a
// guest, just "(guest)".
var sTextString = "";
if(gsUserType == "Guest")
sTextString = "(guest)";
else if(gsUserType == "Admin")
sTextString = gsLastName + ", " + gsFirstName + " (admin)";
else
sTextString = gsLastName + ", " + gsFirstName;
sTextString += gsDemoText;
return sTextString;
} // EndTitleText
function InvalidIDPassword()
{
%>
Worship DB Interface - Invalid ID and/or password<%=gsDemoText%>
Invalid ID and/or password; please try again.
<%
} // InvalidIDPassword
function LeadingTrailingSpaces(poField, piTotalSpaces)
{
// This function figures out how many spaces are needed
// to put in front of or after psField, so that between the
// characters of psField, and the spaces, the total
// number of characters will add up to piTotalSpaces.
// NOTE: The "spaces" are HTML spaces. One HTML space consists
// of the characters " " (not counting the quotation marks).
var sOutput = "";
var x = piTotalSpaces - String(poField).length;
while(x > 0)
{
//Alert("Gets here 1; x = " + x);
sOutput += " ";
x--;
}
return sOutput;
} // LeadingTrailingSpaces
function ListSongUsage()
{
%>
Worship DB Interface - <%=EndTitleText()%>
List Song Usage<%=gsDemoText%>
<%
} // ListSongUsage
function Literal(psInput)
{
// This function receives a string, and outputs
// the same string, except it has single quotes
// added to both ends, and also doubles any
// single quotes in the text, to make it clear
// they are single quote characters, and not
// delimiters. Example: "can't" becomes "'can''t'".
// It also does the same thing to "irregular" apostrophes
// from Word, converting them to "normal" apostrophes.
var sOutput = "'";
var x;
for (x = 0; x <= psInput.length - 1; x++)
switch(psInput.substr(x, 1))
{
case "'": // "normal" apostrophe
case "’": // "irregular" apostrophe from Word
sOutput += "''";
break;
default:
sOutput += psInput.substr(x, 1);
break;
}
sOutput += "'";
return sOutput;
} // Literal
function LogIn()
{
%>
Worship DB Interface<%=gsDemoText%>
(The names are ficticious, except for Don Milnor, creator of this application.)
<%
}
%>
<%
if(gsDemo != "Yes")
{
%>
Click here for the demo version, test data.
<%
}
else
{
%>
Click here for the production version, real data.
<%
}
%>
<%
} // LogIn
function MainMenu()
{
%>
Worship DB Interface - <%=EndTitleText()%>
Main Menu<%=gsDemoText%>
<%
} // MainMenu
function MatchingPersonID(psUserName)
{
// This function receives a username, and returns
// the matching person ID.
var cn = new ActiveXObject("ADODB.Connection");
var rs = new ActiveXObject("ADODB.Recordset");
var sPersonID;
cn.Open(gsConn);
rs.Open("SELECT PersonID" +
" FROM Users" +
" WHERE UserName = " + Literal(psUserName), cn,
2, // ADODB.CursorTypeEnum.adOpenDynamic
3); // ADODB.LockTypeEnum.adLockOptimistic}
rs.MoveFirst;
sPersonID = "" + rs("PersonID");
rs.Close;
cn.Close;
return sPersonID;
} // MatchingPersonID
function NextAvailableIDNumber(psTableName)
{
// DO NOT USE THIS WITH TABLE "Users"!!
// This function receives a table name, and returns the next
// available ID number which should be used. I don't like the way the
// default Access AutoNumber process leaves holes in the numbering
// if something is removed; this will fill them in.
var cn = new ActiveXObject("ADODB.Connection");
var rs = new ActiveXObject("ADODB.Recordset");
var sPersonID;
var bAvailableNumberFound;
var iNextIDNumber;
cn.Open(gsConn);
rs.Open("SELECT ID FROM " + psTableName, cn,
2, // ADODB.CursorTypeEnum.adOpenDynamic
3); // ADODB.LockTypeEnum.adLockOptimistic}
rs.MoveFirst;
iNextIDNumber = 1;
// if no records, it will stay at 1, else code below
if(!rs.BOF || !rs.EOF) // find first hole, if any, or tack to end
{
bAvailableNumberFound = false;
rs.MoveFirst;
while(!rs.EOF && !bAvailableNumberFound)
{
if (rs("ID") > iNextIDNumber) // found a hole
bAvailableNumberFound = true;
else
{
iNextIDNumber++;
rs.MoveNext;
}
}
}
rs.Close;
return iNextIDNumber;
} // NextAvailableIDNumber
function RecordSelected(piID, piCurrentID)
{
// This function reads in piID (the ID we're looking for),
// and piCurrentID (the current ID we're on as we're looping
// through them all). If they're the same, this function
// will output " Selected". Otherwise, it will output "".
// (an empty string). This will determine whether or not the
// current record is selected in the HTML world.
var sReturnText;
if(String(piID) == String(piCurrentID))
sReturnText = " Selected";
else
sReturnText = "";
return sReturnText;
} // RecordSelected
function SongbookNameOfID(piID)
{
// This function returns the songbook name
// matching the ID entered.
var cn = new ActiveXObject("ADODB.Connection");
var rs = new ActiveXObject("ADODB.Recordset");
var sSongbookName;
cn.Open(gsConn);
rs.Open("SELECT BookName" +
" FROM Songbooks" +
" WHERE ID = " + piID, cn,
2, // ADODB.CursorTypeEnum.adOpenDynamic
3); // ADODB.LockTypeEnum.adLockOptimistic
rs.MoveFirst;
sSongbookName = "" + rs("BookName");
rs.Close;
cn.Close;
return sSongbookName;
} // SongbookNameOfID
function SongbookPart1()
{
var cn = new ActiveXObject("ADODB.Connection");
var rs = new ActiveXObject("ADODB.Recordset");
cn.Open(gsConn);
rs.Open("SELECT ID, BookName" +
" FROM Songbooks" +
" ORDER BY ID", cn,
2, // ADODB.CursorTypeEnum.adOpenDynamic
3); // ADODB.LockTypeEnum.adLockOptimistic}
%>
Worship DB Interface - <%=EndTitleText()%>
Songbook Maintenance<%=gsDemoText%>
Existing songbooks:
<%
} // SongbookPart1
function SongbookPart2()
{
// First, check data validataion; they need to
// have chosen Add, Delete or Edit. If they chosen
// Delete or Edit, they need to have chosen
// which existing record they want to delete or
// edit.
var sErrorMessage;
if(gsSongbookMaintType == "undefined")
sErrorMessage = "Please choose Add, Delete or Edit."
else if(gsSongbookMaintType == "delete"
&& gsSongbookID == "")
sErrorMessage = "Please choose a songbook to delete."
else if(gsSongbookMaintType == "edit"
&& gsSongbookID == "")
sErrorMessage = "Please choose a songbook to edit."
else
sErrorMessage = "";
if(sErrorMessage != "")
{
%>
Worship DB Interface - <%=EndTitleText()%>
<%=sErrorMessage%><%=gsDemoText%>
<%
return;
}
// If we get here, no issues with the last screen; the user chose
// Add, Delete or Edit, and if they didn't choose Add, they chose
// which existing record to delete or edit. Now either have user
// add/edit information for Add or Edit, or confirm Delete.
switch(gsSongbookMaintType)
{
case "add":
{
gsSongbookID = NextAvailableIDNumber("Songbooks");
%>
Worship DB Interface - <%=EndTitleText()%>
Add Songbook<%=gsDemoText%>
<%
break;
} // add
case "delete":
{
%>
Worship DB Interface - <%=EndTitleText()%>
Delete Songbook<%=gsDemoText%>
Are you SURE you want to delete this songbook?
<%=SongbookNameOfID(gsSongbookID)%>
<%
break;
} // delete
case "edit":
{
var cn = new ActiveXObject("ADODB.Connection");
var rs = new ActiveXObject("ADODB.Recordset");
cn.Open(gsConn);
rs.Open("SELECT BookName, Notes" +
" FROM Songbooks" +
" WHERE ID = " + gsSongbookID, cn,
2, // ADODB.CursorTypeEnum.adOpenDynamic
3); // ADODB.LockTypeEnum.adLockOptimistic
rs.MoveFirst;
%>
Worship DB Interface - <%=EndTitleText()%>
Edit Songbook<%=gsDemoText%>
<%
break;
} // edit
} // switch
} // SongbookPart2
function SongbookPart3()
{
switch(gsSongbookMaintType)
{
case "add":
{
if(gsBookName == "")
{
%>
Worship DB Interface - <%=EndTitleText()%>
Songbook name required. Please correct this.<%=gsDemoText%>
<%
return;
} // book name empty
// if we get this far, go ahead and save
var cn = new ActiveXObject("ADODB.Connection");
cn.Open(gsConn);
cn.Execute("INSERT INTO Songbooks VALUES (" +
gsSongbookID +
", " + Literal(gsBookName) +
", " + Literal(gsNotes) + ")");
cn.Close;
%>
Worship DB Interface - <%=EndTitleText()%>
Songbook added.<%=gsDemoText%>
<%
break;
} // add
case "delete":
{
var cn = new ActiveXObject("ADODB.Connection");
cn.Open(gsConn);
cn.Execute("DELETE FROM Songbooks" +
" WHERE ID = " + gsSongbookID);
cn.Close;
%>
Worship DB Interface - <%=EndTitleText()%>
Songbook deleted.<%=gsDemoText%>
<%
break;
} // delete
case "edit":
{
if(gsBookName == "")
{
%>
Worship DB Interface - <%=EndTitleText()%>
Songbook name required. Please correct this.<%=gsDemoText%>
<%
return;
} // book name empty
// if we get this far, go ahead and save
var cn = new ActiveXObject("ADODB.Connection");
cn.Open(gsConn);
cn.Execute("UPDATE Songbooks SET" +
" BookName = " + Literal(gsBookName) +
", Notes = " + Literal(gsNotes) +
" WHERE ID = " + gsSongbookID);
cn.Close;
%>
Worship DB Interface - <%=EndTitleText()%>
Songbook updated.<%=gsDemoText%>
<%
break;
} // edit
} // switch
} // SongbookPart3
function SongTitleOfID(piID)
{
// This function returns the song title matching
// the ID entered.
var cn = new ActiveXObject("ADODB.Connection");
var rs = new ActiveXObject("ADODB.Recordset");
var sSongTitle;
cn.Open(gsConn);
rs.Open("SELECT SongTitle" +
" FROM Songs" +
" WHERE ID = " + piID, cn,
2, // ADODB.CursorTypeEnum.adOpenDynamic
3); // ADODB.LockTypeEnum.adLockOptimistic
rs.MoveFirst;
sSongTitle = "" + rs("SongTitle");
rs.Close;
cn.Close;
return sSongTitle;
} // SongTitleOfID
function ThisFileName()
{
// This function retrieves this file name, extracting it
// from Request.ServerVariables("URL"). It's everything
// to the right of the last forward slash ("/").
// This file calls itself, and if I ever change the file name,
// it will still work, without having to change code in the file,
// because of the use of this function.
// NOTE THE EMPTY STRING PUT IN FRONT OF THE SERVER VARIABLE BELOW.
var sInFilePath = "" + Request.ServerVariables("URL");
var sOutFileName = sInFilePath.substr(sInFilePath.lastIndexOf("/") + 1);
return sOutFileName;
} // ThisFileName
function UserRecordPart1()
{
var cn = new ActiveXObject("ADODB.Connection");
var rs = new ActiveXObject("ADODB.Recordset");
cn.Open(gsConn);
rs.Open("SELECT UserName" +
" FROM Users", cn,
2, // ADODB.CursorTypeEnum.adOpenDynamic
3); // ADODB.LockTypeEnum.adLockOptimistic
%>
Worship DB Interface - <%=EndTitleText()%>
User Record Maintenance<%=gsDemoText%>
Existing user records:
<%
rs.Close;
cn.Close;
} // UserRecordPart1
function UserRecordPart2()
{
// First, check data validataion; they need to
// have chosen Add, Delete or Edit. If they chosen
// Delete or Edit, they need to have chosen
// which existing record they want to delete or
// edit.
var sErrorMessage;
if(gsUserRecMaintType == "undefined")
sErrorMessage = "Please choose Add, Delete or Edit."
else if(gsUserRecMaintType == "delete"
&& gsEditUserName == "")
sErrorMessage = "Please choose a user record to delete."
else if(gsUserRecMaintType == "edit"
&& gsEditUserName == "")
sErrorMessage = "Please choose a user record to edit."
else
sErrorMessage = "";
if(sErrorMessage != "")
{
%>
Worship DB Interface - <%=EndTitleText()%>
<%=sErrorMessage%><%=gsDemoText%>
<%
return;
}
// If we get here, no issues with the last screen; the user chose
// Add, Delete or Edit, and if they didn't choose Add, they chose
// which existing record to delete or edit. Now either have user
// add/edit information for Add or Edit, or confirm Delete.
switch(gsUserRecMaintType)
{
case "add":
{
%>
Worship DB Interface - <%=EndTitleText()%>
Add User Record<%=gsDemoText%>
<%
break;
} // add
case "delete":
{
%>
Worship DB Interface - <%=EndTitleText()%>
Delete User Record<%=gsDemoText%>
Are you SURE you want to delete this user record?
<%=gsEditUserName%>
<%
break;
} // delete
case "edit":
{
%>
Worship DB Interface - <%=EndTitleText()%>
Edit User Record<%=gsDemoText%>
<%
rs.Close;
cn.Close;
// begin added lines
break;
} // edit
} // switch
// end added lines
} // UserRecordPart2
function UserRecordPart3()
{
switch(gsUserRecMaintType)
{
case "add":
{
var cn = new ActiveXObject("ADODB.Connection");
cn.Open(gsConn);
cn.Execute("INSERT INTO Users VALUES (" +
Literal(gsEditUserName) +
", " + Literal(gsEditPassword) +
", " + gsEditPersonID +
", " + gbEditAdmin + ")");
cn.Close;
%>
Worship DB Interface - <%=EndTitleText()%>
User Record Added<%=gsDemoText%>
<%
break;
} // add
case "delete":
{
var cn = new ActiveXObject("ADODB.Connection");
cn.Open(gsConn);
cn.Execute("DELETE FROM Users" +
" WHERE UserName = " + Literal(gsEditUserName));
cn.Close;
%>
Worship DB Interface - <%=EndTitleText()%>
User Record Deleted<%=gsDemoText%>
<%
break;
} // delete
case "edit":
{
var cn = new ActiveXObject("ADODB.Connection");
cn.Open(gsConn);
cn.Execute("UPDATE Users SET UserName = " + Literal(gsEditUserName) +
", UserPW = " + Literal(gsEditPassword) +
", PersonID = " + gsEditPersonID +
", Admin = " + gbEditAdmin +
" WHERE UserName = " + Literal(gsEditOriginalID));
cn.Close;
%>
Worship DB Interface - <%=EndTitleText()%>
User Record Updated<%=gsDemoText%>
<%
break;
} // edit
}
} // UserRecordPart3
function WorshipServiceByDate()
{
var cn = new ActiveXObject("ADODB.Connection");
var rs = new ActiveXObject("ADODB.Recordset");
cn.Open(gsConn);
rs.Open("SELECT DISTINCT Format(ActivityDate, 'yyyy-mm-dd') AS ActivityDateYYYYMMDD" +
" FROM WorshipActivityInstances" +
" ORDER BY Format(ActivityDate, 'yyyy-mm-dd') DESC", cn,
2, // ADODB.CursorTypeEnum.adOpenDynamic
3); // ADODB.LockTypeEnum.adLockOptimistic
%>
Worship DB Interface - <%=EndTitleText()%>