Asp.Net SQL update语句 [英] Asp.Net SQL Update Statement
问题描述
我有我的页面上的 Asp.net
应用程序对用户来说,用户请求被删除。这然后填充我的'Admin_TaskList分贝。
管理员然后就在该网站的安全区,并进入用户的名称,并点击一个按钮。一旦确认,用户,然后从我的'用户'DB(已经有了这方面的工作)中删除,但我希望我的'Admin_TaskListDB状态栏,以改变'要做'到'已完成'。
在我伤心我删除位工作,但我在努力更新我的其他表。
code的片段我都试过
conn.Open();
的SqlCommand CMD2 =新的SqlCommand(UPDATE FROM Admin_TaskList设置状态='完整'WHERE说明='删除用户,名称= @Name,康恩);
cmd2.Parameters.AddWithValue(@名,txtRemoveUser.Text);
SqlDataReader的RD2 = cmd2.ExecuteReader();
conn.Close();
展开code
公共无效btnRemoveConfirmYes_Click(对象发件人,EventArgs的发送)
{
字符串连接= ConfigurationManager.ConnectionStrings [PaydayLunchConnectionString1]的ConnectionString。
康涅狄格州的SqlConnection =新的SqlConnection(连接); conn.Open();
CMD1的SqlCommand =新的SqlCommand(DELETE FROM WHERE用户名= @Name,康恩);
cmd1.Parameters.AddWithValue(@名,txtRemoveUser.Text);
SqlDataReader的RD1 = cmd1.ExecuteReader();
conn.Close(); conn.Open();
的SqlCommand CMD2 =新的SqlCommand(UPDATE FROM Admin_TaskList设置状态='完整'WHERE说明='删除用户,名称= @Name,康恩);
cmd2.Parameters.AddWithValue(@名,txtRemoveUser.Text);
SqlDataReader的RD2 = cmd2.ExecuteReader();
conn.Close(); txtRemoveUser.Text =;
的Response.Redirect(/ AdminSide / TaskList.aspx);
}
而不是使用 SqlDataReader的
更新值使用<一的href=\"https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executenonquery(v=vs.110).aspx\"相对=nofollow> SqlCommand.ExecuteNonQuery
:
内部更新= cmd2.ExecuteNonQuery();
请记住,你需要使用的ExecuteNonQuery
上修改数据,如删除指令
,插入
或更新
。
MSDN:
您可以使用ExecuteNonQuery执行目录操作(
例如,查询数据库的结构或创建数据库
对象,如表),或不改变在数据库中的数据
使用DataSet通过执行UPDATE,INSERT或DELETE语句。
块引用>完整的方法:
INT删除,更新;
字符串连接= ConfigurationManager.ConnectionStrings [PaydayLunchConnectionString1]的ConnectionString。使用(VAR康恩=新的SqlConnection(连接))
{
conn.Open();
字符串delSql =DELETE FROM WHERE用户名= @Name;
使用(VAR CMD =新的SqlCommand(delSql,康涅狄格州))
{
cmd.Parameters.Add(@名,SqlDbType.NVarChar).value的= txtRemoveUser.Text;
删除= cmd.ExecuteNonQuery();
} 字符串updSql = @UPDATE Admin_TaskList
设置状态='完成'
WHERE说明='删除用户'
并且Name = @Name;
使用(VAR CMD =新的SqlCommand(updSql,康涅狄格州))
{
cmd.Parameters.Add(@名,SqlDbType.NVarChar).value的= txtRemoveUser.Text;
更新= cmd.ExecuteNonQuery();
}
}I have an
Asp.net
application on my page the user requests for a user to be removed. This then populates my 'Admin_TaskList' db.An administrator then goes in the secure area of the site and enters the users name and clicks a button. Upon the confirmation, the user is then deleted from my 'Users' db (already got this working) but I want my 'Admin_TaskList' db 'Status' column to change from 'To Do' to 'Completed'.
As I sad I have the delete bit working but I am struggling updating my other table.
Snippet of code I have tried
conn.Open(); SqlCommand cmd2 = new SqlCommand("UPDATE FROM Admin_TaskList SET Status = 'Complete' WHERE Description = 'Remove User' AND Name = @Name", conn); cmd2.Parameters.AddWithValue("@Name", txtRemoveUser.Text); SqlDataReader rd2 = cmd2.ExecuteReader(); conn.Close();
Full code
public void btnRemoveConfirmYes_Click(object sender, EventArgs e) { string connection = ConfigurationManager.ConnectionStrings["PaydayLunchConnectionString1"].ConnectionString; SqlConnection conn = new SqlConnection(connection); conn.Open(); SqlCommand cmd1 = new SqlCommand("DELETE FROM Users WHERE Name = @Name", conn); cmd1.Parameters.AddWithValue("@Name", txtRemoveUser.Text); SqlDataReader rd1 = cmd1.ExecuteReader(); conn.Close(); conn.Open(); SqlCommand cmd2 = new SqlCommand("UPDATE FROM Admin_TaskList SET Status = 'Complete' WHERE Description = 'Remove User' AND Name = @Name", conn); cmd2.Parameters.AddWithValue("@Name", txtRemoveUser.Text); SqlDataReader rd2 = cmd2.ExecuteReader(); conn.Close(); txtRemoveUser.Text = ""; Response.Redirect("/AdminSide/TaskList.aspx"); }
解决方案Instead of using a
SqlDataReader
to update a value useSqlCommand.ExecuteNonQuery
:int updated = cmd2.ExecuteNonQuery();
Remember that you need to use
ExecuteNonQuery
on commands that modify your data likeDelete
,Insert
orUpdate
.MSDN:
You can use the ExecuteNonQuery to perform catalog operations (for example, querying the structure of a database or creating database objects such as tables), or to change the data in a database without using a DataSet by executing UPDATE, INSERT, or DELETE statements.
The complete method:
int deleted, updated; string connection = ConfigurationManager.ConnectionStrings["PaydayLunchConnectionString1"].ConnectionString; using (var conn = new SqlConnection(connection)) { conn.Open(); string delSql = "DELETE FROM Users WHERE Name = @Name"; using (var cmd = new SqlCommand(delSql, conn)) { cmd.Parameters.Add("@Name", SqlDbType.NVarChar).Value = txtRemoveUser.Text; deleted = cmd.ExecuteNonQuery(); } string updSql = @"UPDATE Admin_TaskList SET Status = 'Complete' WHERE Description = 'Remove User' AND Name = @Name"; using (var cmd = new SqlCommand(updSql, conn)) { cmd.Parameters.Add("@Name", SqlDbType.NVarChar).Value = txtRemoveUser.Text; updated = cmd.ExecuteNonQuery(); } }
这篇关于Asp.Net SQL update语句的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!