private void updateDB(Dictionary<string, string> data, bool update)
{
if (update)
{
StudentRecordsDAL.Command("UPDATE Course SET Course_Code='" + data["Course_Code"] + "', Course_Title='" + data["Course_Title"] + "', " +
" Course_Coordinator=" + data["Course_Coordinator"] + ", Course_Type=" + data["Course_Type"] + " WHERE Course_ID=" + data["Course_ID"]);
}
else
StudentRecordsDAL.Command("INSERT INTO Course (Course_Code, Course_Title, Course_Coordinator, Course_Type) VALUES ('" + data["Course_Code"] + "', '" + data["Course_Title"] + "', " + data["Course_Coordinator"] + ", " + data["Course_Type"] + ")");
}
private void updateDB(Dictionary <string, string> data, bool update)
{
if (update)
{
StudentRecordsDAL.Command("UPDATE Staff SET Staff_Name='" + data["Staff_Name"] + "' WHERE Staff_ID=" + data["Staff_ID"]);
}
else
{
StudentRecordsDAL.Command("INSERT INTO Staff (Staff_Name) VALUES ('" + data["Staff_Name"] + "')");
}
}
private void updateDB(Dictionary <string, string> data, bool update)
{
if (update)
{
StudentRecordsDAL.Command("UPDATE Gender SET Gender_Name='" + data["Gender_Name"] + "' WHERE Gender_ID=" + data["Gender_ID"]);
}
else
{
StudentRecordsDAL.Command("INSERT INTO Gender (Gender_Name) VALUES ('" + data["Gender_Name"] + "')");
}
}
private void updateDB(Dictionary <string, string> data, bool update)
{
if (update)
{
StudentRecordsDAL.Command("UPDATE Course_Attempt SET Student_ID=" + data["Student_ID"] + ", Course_ID=" + data["Course_ID"] + " WHERE Attempt_ID=" + data["Attempt_ID"]);
}
else
{
StudentRecordsDAL.Command("INSERT INTO Course_Attempt (Student_ID, Course_ID) VALUES (" + data["Student_ID"] + ", " + data["Course_ID"] + ")");
}
}
private void updateDB(Dictionary <string, string> data, bool update)
{
if (update)
{
StudentRecordsDAL.Command("UPDATE Unit_Attempt SET Attempt_ID=" + data["Attempt_ID"] + ", Unit_ID=" + data["Unit_ID"] +
", Semester_Attempted=" + data["Semester_Attempted"] + ", Attempt_Mark=" + data["Attempt_Mark"] + " WHERE Unit_Att_ID=" + data["Unit_Att_ID"]);
}
else
{
StudentRecordsDAL.Command("INSERT INTO Unit_Attempt (Attempt_ID, Unit_ID, Semester_Attempted, Attempt_Mark) VALUES (" +
data["Attempt_ID"] + ", " + data["Unit_ID"] + ", " + data["Semester_Attempted"] + ", " + data["Attempt_Mark"] + ")");
}
}
private void updateDB(Dictionary <string, string> data, bool update)
{
if (update)
{
StudentRecordsDAL.Command("UPDATE Course_Type SET Course_Type_Name='" + data["Course_Type_Name"] + "', Course_Type_Credit_Points=" + data["Course_Type_Credit_Points"] +
", Course_Type_Duration_Months=" + data["Course_Type_Duration_Months"] + " WHERE Course_Type_ID=" + data["Course_Type_ID"]);
}
else
{
StudentRecordsDAL.Command("INSERT INTO Course_Type (Course_Type_Name, Course_Type_Credit_Points, Course_Type_Duration_Months) VALUES ('" + data["Course_Type_Name"] + "', " + data["Course_Type_Credit_Points"] +
", " + data["Course_Type_Duration_Months"] + ")");
}
}
private void updateDB(Dictionary <string, string> data, bool update)
{
if (update)
{
StudentRecordsDAL.Command("UPDATE Unit SET Unit_Code='" + data["Unit_Code"] + "', Unit_Title='" + data["Unit_Title"] + "', Unit_Coordinator=" + data["Unit_Coordinator"] +
", Unit_Credit_Points=" + data["Unit_Credit_Points"] + " WHERE Unit_ID=" + data["Unit_ID"]);
}
else
{
StudentRecordsDAL.Command("INSERT INTO Unit (Unit_Code, Unit_Title, Unit_Coordinator, Unit_Credit_Points) VALUES ('" + data["Unit_Code"] + "', '" + data["Unit_Title"] + "', " + data["Unit_Coordinator"] +
", " + data["Unit_Credit_Points"] + ")");
}
}
public void updateDB(Dictionary <string, string> data, bool update)
{
string studentEmail = data["Student_Name"].Substring(0, 1) + data["Student_Name"].Split(' ')[1].Substring(0, Math.Min(4, data["Student_Name"].Split(' ')[1].Length));
string counter = "";
int counterint = 0;
while (StudentRecordsDAL.Query("SELECT * FROM Student_State WHERE Student_Email='" + studentEmail + counter + "@our.ecu.edu.au'").Count != 0)
{
counterint++;
counter = counterint.ToString();
}
if (update)
{
StudentRecordsDAL.Command("UPDATE Student SET Student_Name='" + data["Student_Name"] + "', Student_Email='" + studentEmail + counter + "@our.ecu.edu.au', " +
" Student_Password='******', Gender=" + data["Gender"] + ", Date_Of_Birth=#" + data["Date_Of_Birth"] + "#, Phone_Number='" +
data["Phone_Number"] + "', Address_Line_One='" + data["Address_Line_One"] + "', Address_Line_Two='" +
data["Address_Line_Two"] + "', City='" + data["City"] + "', State=" + data["State"] + ", Postcode='" + data["Postcode"] + "' WHERE Student_ID=" + data["Student_ID"]);
}
else
{
Random rand = new Random();
int studentNumber = rand.Next();
string studentNumberStr = "10000000";
studentNumber = rand.Next();
studentNumberStr = studentNumberStr.Substring(0, 8 - Math.Min(8, studentNumber.ToString().Length));
studentNumberStr = studentNumberStr + studentNumber.ToString().Substring(0, Math.Min(8, studentNumber.ToString().Length));
while (StudentRecordsDAL.Query("SELECT * FROM Student_State WHERE Student_Number='" + studentNumberStr + "'").Count != 0)
{
studentNumber = rand.Next();
studentNumberStr = studentNumberStr.Substring(0, Math.Min(8, studentNumber.ToString().Length));
studentNumberStr = studentNumberStr + studentNumber.ToString().Substring(0, Math.Min(8, studentNumber.ToString().Length));
}
StudentRecordsDAL.Command("INSERT INTO Student (Student_Number, Student_Name, Student_Email, Student_Password, Gender, Date_Of_Birth, Phone_Number, Address_Line_One, " +
"Address_Line_Two, City, State, Postcode) VALUES ('" + studentNumberStr + "', '" + data["Student_Name"] + "', '" + studentEmail + counter + "@our.ecu.edu.au', '" + data["Student_Password"] + "', " +
data["Gender"] + ", #" + data["Date_Of_Birth"] + "#, '" + data["Phone_Number"] + "', '" + data["Address_Line_One"] + "', '" +
data["Address_Line_Two"] + "', '" + data["City"] + "', " + data["State"] + ", '" + data["Postcode"] + "')");
}
}
protected void delete_button(object sender, EventArgs e)
{
ArrayList deleteQueue = new ArrayList();
foreach (string key in Request.Form)
{
Match match = Regex.Match(key, "^check([0-9]+)$");
if (match.Success)
{
deleteQueue.Add(Request.Form["rowid" + match.Groups[1]]);
}
}
ArrayList deleteErr = new ArrayList();
string errTable = "";
foreach (string id in deleteQueue)
{
try {
StudentRecordsDAL.Command("DELETE FROM " + table + " WHERE " + idCol + " = " + id);
} catch (OleDbException ex)
{
errTable = Regex.Match(ex.Message, "'([a-z_A-Z]+)' includes related records\\.$").Groups[1].ToString();
deleteErr.Add(id);
}
}
errTable = errTable.Replace("_", " ");
errTable += "s";
if (errTable != "s")
{
ScriptManager.RegisterStartupScript(this, this.GetType(), "KeyClient", "alert('Unable to delete " + deleteErr.Count + " row(s) due to related entries in " + errTable + ".'); reloadAsGet();", true);
}
else
{
Response.Redirect(Request.RawUrl);
}
}
private void btnDeleteClick(object sender, EventArgs e)
{
string id;
Match match = Regex.Match(((Button)sender).ID, "^delete([0-9]+)$");
if (match.Success)
{
id = match.Groups[1].ToString();
}
else
{
ScriptManager.RegisterStartupScript(this, this.GetType(), "KeyClient", "alert('Unable to delete row due to unknown error')", true);
return;
}
string errTable = "";
try
{
StudentRecordsDAL.Command("DELETE FROM " + table + " WHERE " + idCol + " = " + id);
}
catch (OleDbException ex)
{
errTable = Regex.Match(ex.Message, "'([a-z_A-Z]+)' includes related records\\.$").Groups[1].ToString();
}
errTable = errTable.Replace("_", " ");
errTable += "s";
if (errTable != "s")
{
ScriptManager.RegisterStartupScript(this, this.GetType(), "KeyClient", "alert('Unable to delete row due to related entries in " + errTable + ".'); reloadAsGet();", true);
}
else
{
Response.Redirect(Request.RawUrl);
}
}