SQL UPDATE删除记录
本文关键字:记录 删除 UPDATE SQL | 更新日期: 2023-09-27 17:53:12
出于某种原因UPDATE正在删除我的SQL Server数据库中的记录,而不是更新它。我试过只传递一个变量(并改变它不同的单独字段),它删除100%。我还确保有数据被传递,所以我没有处理DBNull问题。我的SO和谷歌搜索不断提出删除然后添加行更新记录的过程。这里可能发生了什么?
protected void grdvMyEntries_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
string Task = (grdvMyEntries.Rows[e.RowIndex].FindControl("ddlTasks") as DropDownList).SelectedItem.Value;
string Code = (grdvMyEntries.Rows[e.RowIndex].FindControl("ddlPayrollCodes") as DropDownList).SelectedItem.Value;
DateTime strDate = Convert.ToDateTime((grdvMyEntries.Rows[e.RowIndex].FindControl("txtDateEdit") as TextBox).Text.Trim());
string TimeIn = (grdvMyEntries.Rows[e.RowIndex].FindControl("txtTimeInEdit") as TextBox).Text.Trim();
string TimeOut = (grdvMyEntries.Rows[e.RowIndex].FindControl("txtTimeOutEdit") as TextBox).Text.Trim();
string ItemNo = (grdvMyEntries.Rows[e.RowIndex].FindControl("txtItemNo") as TextBox).Text.Trim();
string WO = (grdvMyEntries.Rows[e.RowIndex].FindControl("txtWO") as TextBox).Text.Trim();
string WIP = (grdvMyEntries.Rows[e.RowIndex].FindControl("txtWIP") as TextBox).Text.Trim();
string Note = (grdvMyEntries.Rows[e.RowIndex].FindControl("txtNote") as TextBox).Text.Trim();
string TimeID = grdvMyEntries.DataKeys[e.RowIndex].Value.ToString();
string strConnString = ConfigurationManager.ConnectionStrings["MAFapp"].ConnectionString;
string strTmp = "Date:" + strDate + "|TimeIn:" + TimeIn + "|TimeOut:" + TimeOut + "|WIP:" + WIP + "|WO:" + WO + "|ItemNo:" + ItemNo + "|Task:" + Task + "|Code:" + Code + "|Note:" + Note + "|WHERE TimeID:" + TimeID;
Debug.Print(strTmp);
using (SqlConnection con = new SqlConnection(strConnString))
{
string query = "UPDATE Time SET [Date]=@Date, [TimeIn]=@TimeIn, [TimeOut]=@TimeOut, [WIP]=@WIP, [WO]=@WO, [ItemNo]=@ItemNo, [Task]=@Task, [Code]=@Code, [Note]=@Note WHERE [TimeID]=@TimeID";
Debug.WriteLine(query);
using (SqlCommand cmd = new SqlCommand(query))
{
cmd.Connection = con;
cmd.Parameters.AddWithValue("@TimeID", TimeID);
cmd.Parameters.AddWithValue("@Date", strDate);
cmd.Parameters.AddWithValue("@TimeIn", TimeIn);
cmd.Parameters.AddWithValue("@TimeOut", TimeOut);
if (String.IsNullOrWhiteSpace(WIP.Trim()))
{
cmd.Parameters.AddWithValue("@WIP", WIP).Value = Convert.DBNull; ;
}
else
{
cmd.Parameters.AddWithValue("@WIP", WIP);
}
if (String.IsNullOrWhiteSpace(WO.Trim()))
{
cmd.Parameters.AddWithValue("@WO", WO).Value = Convert.DBNull; ;
}
else
{
cmd.Parameters.AddWithValue("@WO", WO);
}
if (String.IsNullOrWhiteSpace(ItemNo.Trim()))
{
cmd.Parameters.AddWithValue("@ItemNo", ItemNo).Value = Convert.DBNull; ;
}
else
{
cmd.Parameters.AddWithValue("@ItemNo", ItemNo);
}
if (String.IsNullOrWhiteSpace(Task.Trim()))
{
cmd.Parameters.AddWithValue("@Task", Task).Value = Convert.DBNull; ;
}
else
{
cmd.Parameters.AddWithValue("@Task", Task);
}
cmd.Parameters.AddWithValue("@Code", Code);
if (String.IsNullOrWhiteSpace(Note.Trim()))
{
cmd.Parameters.AddWithValue("@Note", Note).Value = Convert.DBNull; ;
}
else
{
cmd.Parameters.AddWithValue("@Note", Note);
}
con.Open();
cmd.ExecuteNonQuery();
con.Close();
tblSummary.DataBind();
grdvMyEntries.DataBind();
}
}
}
Update不会从表中删除行。唯一的可能就是你桌上的扳机。请运行此查询以查找触发器列表及其文本。
USE ChangeThisToDatabaseName
GO
SELECT so.NAME
,TEXT
FROM sysobjects so
,syscomments sc
WHERE type = 'TR'
AND so.id = sc.id
AND TEXT LIKE '%Time%'
注意:如果您的表上没有触发器,那么请在运行更新之前确保是否存在基于您的过滤器的记录。
不久前,当我尝试将数据插入到表中时,数据正在被记录,但在插入记录两秒钟后被自动删除。没有扳机之类的东西。然后我观察了这个表,发现了一个索引问题。我已经重建了索引,问题已经解决了。如果您的表中没有大量数据,我建议您尝试重建索引或重新创建索引