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 use SqlCommand.ExecuteNonQuery:

int updated = cmd2.ExecuteNonQuery();

Remember that you need to use ExecuteNonQuery on commands that modify your data like Delete, Insert or Update.

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屋！