使用Delete和Insert命令同时更新表? [英] Update the Table using Delete and Insert commands at the same time?
本文介绍了使用Delete和Insert命令同时更新表?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我开发了一个基于Web的培训矩阵，该矩阵显示了公司部门中每个部门的每位员工的培训记录.除了一件事情，一切都运转良好.当管理员更新某些员工的培训记录并单击更新"按钮时，他将立即看到他的最新更新，但是在一段时间后再次更新矩阵时，他将看不到他先前输入的数据.他们会消失，我不知道为什么.我检查了数据库，也没有数据，也没有人碰过数据库.真的很奇怪.
我开发了Updating功能，就像(删除和插入)一样，而不是使用Update.我认为现在的问题是删除功能.我需要以某种方式修改它，使其特定于确定的员工，而不是针对每个人，如下所示，那么该怎么做呢?
后台代码(C#代码):
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();
//create a new HtmlTable object
HtmlTable table = new HtmlTable();
DataView dv = (DataView)SqlDataSource2.Select(DataSourceSelectArguments.Empty);
int columns = dv.Table.Columns.Count;
int rows = dv.Count;
//table's formating-related properties
table.Border = 2;
table.CellPadding = 3;
table.CellSpacing = 3;
table.Width = "900px";
//to get the css style
table.Attributes["class"] = "uGrid";
//create a new HtmlTableRow and HtmlTableCell objects
HtmlTableRow row;
HtmlTableRow header = new HtmlTableRow();
HtmlTableCell cell;
//for adding the headers to the table
foreach (DataColumn column in dv.Table.Columns)
{
HtmlTableCell headerCell = new HtmlTableCell("th");
headerCell.InnerText = column.Caption;
header.Cells.Add(headerCell);
}
table.Rows.Add(header);
//loop for adding rows to the table
foreach (DataRowView datarow in dv)
{
row = new HtmlTableRow();
//row.BgColor = "yellow";
//loop for adding cells
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);
}
//add the cell to the current row
row.Cells.Add(cell);
}
//add the row to the table
table.Rows.Add(row);
}
//add the table to the page
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))
{
//Now the insert
cmd.CommandText = insertCommand;
cmd.Parameters.Clear(); //need this because still has params from del comm
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))
{
//Now the insert
cmd.CommandText = insertCommand;
cmd.Parameters.Clear(); //need this because still has params from del comm
cmd.Parameters.AddWithValue("@employeeId", employeeID);
cmd.Parameters.AddWithValue("@CourseID", courseID);
cmd.ExecuteNonQuery();
}
}
}
}
}
}
}
}
}
Response.Redirect("KPIReport.aspx");
推荐答案
这样，您的代码就没有错.要了解问题出在哪里，您必须彻底调试代码.
1)尝试从HTML中提取员工ID和更新的课程ID时，请检查您是否获得了正确的值.
2)还要检查您的插入命令是否正常工作.
您的代码存在两个问题:
1)您没有在任何地方关闭连接对象.
2)您可以在交易中删除和插入.
尝试这些技巧，并让我知道问题是否已解决.
谢谢，
Mateen
Hi,
There''s no fault in your code as such. To understand where exactly the problem is you have to debug your code thoroughly.
1) Check Whether you are getting the correct value when you are trying to extract the Employee ID and Updated Course ID from HTML.
2) Also check that your insert command is working properly or not.
Two problems that is with your code is:
1) You havn''t closed the connection object anywhere.
2) You can do delete and insert within a Transaction.
Try these tips and do let me know whether problem gets resolved or not.
Thanks,
Mateen
经过多次尝试，并在StackOverflow社区的一位开发人员的大力帮助下，我能够解决它.解决方案是:
After many tries and by a great help from one of the developers in StackOverflow Community, I could be able to solve it. The solution is:
//TO KEEP TRACK OF EMPLOYEE IDS FOR WHICH WE HAVE ALREADY EXECUTED
//DELETE STATEMENT
IList<string> _deleted=new List<string>();
foreach (Control ctrl in PlaceHolder1.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];
if(!_deleted.Contains(employeeID))
{
using (SqlConnection conn = new SqlConnection(connString))
{
conn.Open();
using (SqlCommand cmd = new SqlCommand(deleteCommand, conn))
{
cmd.Pararmeters.AddWithValue("@employeeId", employeeID);
cmd.ExecuteNonQuery();
//DON'T EXECUTE DELETE FOR ALL CHECKED ROWS
_deleted.Add(employeeID);
}
}
}</string></string>
如果您使用.net，则表示ini网格速度数据采用者有1个选项.使用此属性选项，u hv插入n删除表，并在smaetime删除行.
Hi,
If u r using .net means there is 1 option ini grid veiw r data adoptor. Using this properties option u hv to insert n delete the table, rows at the smaetime.
这篇关于使用Delete和Insert命令同时更新表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
