I developed a web-based training matrix that shows the training record for each employee in each division in my department in the company. Everything works well except one thing; when the Admin updates the training record for some employees and he clicks on the Update button, he will see his latest updates immediately, but after a period of time when he updates the matrix again, he will not see his previous entered data. They will be disappeared and I don't know why. I checked the database and there was no data, too and there is no body touched the database. It is really strange.
I developed the Updating functionality to be like (delete and insert) instead of using Update. I think the problem now is with the deleting functionality. I need to modify it in such a way to be specific for a determined employee not for everyone as displayed below, so how to do that?
Code-Behind (C# code):
protected void Page_Load(object sender, EventArgs e)
{
DataView dv2 = (DataView)SqlDataSource1.Select(DataSourceSelectArguments.Empty);
foreach (DataRowView group in dv2)
{
SqlDataSource2.SelectParameters[0].DefaultValue = group[0].ToString();
HtmlTable table = new HtmlTable();
DataView dv = (DataView)SqlDataSource2.Select(DataSourceSelectArguments.Empty);
int columns = dv.Table.Columns.Count;
int rows = dv.Count;
table.Border = 2;
table.CellPadding = 3;
table.CellSpacing = 3;
table.Width = "900px";
table.Attributes["class"] = "uGrid";
HtmlTableRow row;
HtmlTableRow header = new HtmlTableRow();
HtmlTableCell cell;
foreach (DataColumn column in dv.Table.Columns)
{
HtmlTableCell headerCell = new HtmlTableCell("th");
headerCell.InnerText = column.Caption;
header.Cells.Add(headerCell);
}
table.Rows.Add(header);
foreach (DataRowView datarow in dv)
{
row = new HtmlTableRow();
for (int j = 0; j < columns; j++)
{
cell = new HtmlTableCell();
if (j < 4)
{
cell.InnerText = datarow[j].ToString();
}
else
{
CheckBox checkbox = new CheckBox();
int checkBoxColumns = dv.Table.Columns.Count - 5;
string fieldvalue = datarow[j].ToString();
string yes = fieldvalue.Split(new string[] { ", " }, StringSplitOptions.RemoveEmptyEntries)[1];
string courseid = fieldvalue.Split(new string[] { ", " }, StringSplitOptions.RemoveEmptyEntries)[0];
checkbox.ID = row.Cells[3].InnerText + "," + courseid.Trim();
checkbox.Checked = yes.Equals("Yes");
cell.Controls.Add(checkbox);
}
row.Cells.Add(cell);
}
table.Rows.Add(row);
}
PlaceHolder1.Controls.Add(table);
}
}
protected void updateButton_Click(object sender, EventArgs e)
{
string connString = "Data Source=localhost\\sqlexpress;Initial Catalog=psspTest;Integrated Security=True";
string deleteCommand = "DELETE FROM employee_courses where employeeID=@employeeID";
string insertCommand = "INSERT INTO employee_courses (employeeId, CourseID) values(@employeeId, @CourseID)";
using (SqlConnection conn = new SqlConnection(connString))
{
conn.Open();
using (SqlCommand cmd = new SqlCommand(deleteCommand, conn))
{
cmd.ExecuteNonQuery();
}
}
foreach (Control ctrl in PlaceHolder1.Controls)
{
if (ctrl is HtmlTable)
{
HtmlTable table = (HtmlTable)ctrl;
foreach (HtmlTableRow row in table.Rows)
{
foreach (HtmlTableCell cell in row.Cells)
{
foreach (Control c in cell.Controls)
{
if (c is CheckBox)
{
CheckBox checkbox = (CheckBox)c;
if (checkbox.Checked)
{
string fieldvalue = checkbox.ID;
string employeeID = fieldvalue.Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries)[0];
string courseID = fieldvalue.Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries)[1];
using (SqlConnection conn = new SqlConnection(connString))
{
conn.Open();
using (SqlCommand cmd = new SqlCommand(insertCommand, conn))
{
cmd.CommandText = insertCommand;
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@employeeId", employeeID);
cmd.Parameters.AddWithValue("@CourseID", courseID);
cmd.ExecuteNonQuery();
}
}
}
}
}
}
}
}
}
Response.Redirect("KPIReport.aspx");
}
***The problem is here:***
protected void updateButton_Click(object sender, EventArgs e)
{
string connString = "Data Source=localhost\\sqlexpress;Initial Catalog=psspTest;Integrated Security=True";
string deleteCommand = "DELETE FROM employee_courses where employeeID=@employeeID";
string insertCommand = "INSERT INTO employee_courses (employeeId, CourseID) values(@employeeId, @CourseID)";
using (SqlConnection conn = new SqlConnection(connString))
{
conn.Open();
using (SqlCommand cmd = new SqlCommand(deleteCommand, conn))
{
cmd.ExecuteNonQuery();
}
}
foreach (Control ctrl in PlaceHolder1.Controls)
{
if (ctrl is HtmlTable)
{
HtmlTable table = (HtmlTable)ctrl;
foreach (HtmlTableRow row in table.Rows)
{
foreach (HtmlTableCell cell in row.Cells)
{
foreach (Control c in cell.Controls)
{
if (c is CheckBox)
{
CheckBox checkbox = (CheckBox)c;
if (checkbox.Checked)
{
string fieldvalue = checkbox.ID;
string employeeID = fieldvalue.Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries)[0];
string courseID = fieldvalue.Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries)[1];
using (SqlConnection conn = new SqlConnection(connString))
{
conn.Open();
using (SqlCommand cmd = new SqlCommand(insertCommand, conn))
{
cmd.CommandText = insertCommand;
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@employeeId", employeeID);
cmd.Parameters.AddWithValue("@CourseID", courseID);
cmd.ExecuteNonQuery();
}
}
}
}
}
}
}
}
}
Response.Redirect("KPIReport.aspx");