protected void insertCEData(object sender, EventArgs e)
{
//--------CONSISTENCY-----------------
if (this.memberID.Text.Equals("") || this.datepicker.Text.Equals("") || this.numberOfSeats.Text.Equals("") || this.selectYear.SelectedIndex == 0)
{
this.errorDiv.Visible = true;
this.response.Visible = false;
this.errorMsg.Text = "Please fill all required fields.";
}
//------------------------------------
else
{
string user = Page.User.Identity.Name;
CEDAO ceDAO = new CEDAO();
CE ce = new CE();
ce.memberID = this.memberID.Text;
ce.numberOfSeats = this.numberOfSeats.Text;
ce.year = this.selectYear.Text + "-01-01";
ce.eventDate = this.datepicker.Text;
this.response.Visible = true;
this.errorDiv.Visible = false;
this.DBMsg.Text = ceDAO.insertCE(ce, user);
}
}
public string editCE(CE ce, string user)
{
sqlConn.ConnectionString = conn;
DataTable dt = new DataTable();
dt.Clear();
SqlCommand cmd = new SqlCommand(string.Format(
@"UPDATE CE SET NUMBER_OF_SEATS=@NUMBER_OF_SEATS, YEAR=@YEAR, EVENT_DATE=@EVENT_DATE,
MODIFIED_BY=@MODIFIED_BY, DATE_MODIFIED=@DATE_MODIFIED WHERE CE.ID_CE=@ID_CE"), sqlConn);
cmd.Parameters.AddWithValue("@NUMBER_OF_SEATS", ce.numberOfSeats);
cmd.Parameters.AddWithValue("@YEAR", ce.year);
cmd.Parameters.AddWithValue("@EVENT_DATE", ce.eventDate);
cmd.Parameters.AddWithValue("@MODIFIED_BY", user);
cmd.Parameters.AddWithValue("@DATE_MODIFIED", System.DateTime.Today);
cmd.Parameters.AddWithValue("@ID_CE", ce.id);
try
{
sqlConn.Open();
int result = cmd.ExecuteNonQuery();
if (result > 0)
{
return "Success";
}
else
{
return "Error";
}
}
catch (Exception e)
{
string error = e.Message.ToString();
}
finally
{
sqlConn.Close();
}
return "Error";
}
protected void insertEditedCE(object sender, EventArgs e)
{
if (this.datepicker.Text.Equals("") || this.numberOfSeats.Text.Equals("") ||
this.selectYear2.Text.Equals("") || this.selectYear2.Text.Equals("..."))
{
this.errorMsg.Text = "Please fill all the 3 required fields.";
}
else
{
ceDAO = new CEDAO();
ce = new CE();
ce.id = this.CEResults.SelectedRow.Cells[5].Text;
ce.memberID = this.CEResults.SelectedRow.Cells[4].Text;
ce.year = this.selectYear2.Text;
ce.numberOfSeats = this.numberOfSeats.Text;
ce.eventDate = this.datepicker.Text;
string user = this.Page.User.Identity.Name.ToString();
string result = ceDAO.editCE(ce, user);
if (result.Equals("Success")){
this.DB_Response.Text = "The CE data was successfully updated on the Database.";
this.errorMsg.Text = "";
}else
{
this.errorMsg.Text = "Nothing was commited. Some error has occurred.";
this.DB_Response.Text = "";
}
}
}
public List<CE> getListOfCE(string memberID, string year)
{
List<CE> ceList = new List<CE>();
sqlConn.ConnectionString = conn;
DataTable dt = new DataTable();
dt.Clear();
SqlCommand cmd = new SqlCommand(string.Format(
@"SELECT * FROM CE WHERE ID_MEMBER=@ID_MEMBER AND YEAR=@YEAR"), sqlConn);
cmd.Parameters.AddWithValue("@ID_MEMBER", memberID);
cmd.Parameters.AddWithValue("@YEAR", year);
try
{
sqlConn.Open();
SqlDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
{
dt.Load(reader);
for (int i = 0; i < dt.Rows.Count; i++)
{
CE ce = new CE();
DataRow row = dt.Rows[i];
ce.id = row[0].ToString();
ce.memberID = row[1].ToString();
ce.numberOfSeats = row[2].ToString();
ce.year = row[3].ToString();
ce.eventDate = row[4].ToString();
ceList.Add(ce);
}
return ceList;
}
}
catch (Exception e)
{
string error = e.Message.ToString();
}
finally
{
sqlConn.Close();
}
return null;
}
public string insertCE(CE ce, string user)
{
sqlConn.ConnectionString = conn;
DataTable dt = new DataTable();
dt.Clear();
SqlCommand cmd = new SqlCommand(string.Format(
@"INSERT INTO CE (ID_MEMBER, NUMBER_OF_SEATS, YEAR, EVENT_DATE, MODIFIED_BY, DATE_MODIFIED) VALUES
(@ID_MEMBER, @NUMBER_OF_SEATS, @YEAR, @EVENT_DATE, @MODIFIED_BY, @DATE_MODIFIED)"), sqlConn);
cmd.Parameters.AddWithValue("@ID_MEMBER", ce.memberID);
cmd.Parameters.AddWithValue("@NUMBER_OF_SEATS", ce.numberOfSeats);
cmd.Parameters.AddWithValue("@YEAR", ce.year);
cmd.Parameters.AddWithValue("@EVENT_DATE", ce.eventDate);
cmd.Parameters.AddWithValue("@MODIFIED_BY", user);
cmd.Parameters.AddWithValue("@DATE_MODIFIED", System.DateTime.Today.ToShortDateString());
try
{
sqlConn.Open();
cmd.ExecuteNonQuery();
return "The CE Data has been added sucessfully.";
}
catch (Exception e)
{
return e.Message.ToString();
}
finally
{
sqlConn.Close();
}
}