Asp.Net SQL Update Statement

Question

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");
        }

You don't need to close and reopen the connection between commands. — Dai
– Dai, Commented Dec 18, 2015 at 14:08
Better write a Stored Proc and do both operation i.e. update & delete in that. — Rahul Singh
– Rahul Singh, Commented Dec 18, 2015 at 14:08
@RahulSingh You don't need a sproc, you can send a command batch in a single SqlCommand execution. — Dai
– Dai, Commented Dec 18, 2015 at 14:09
Why you using ExecuteReader instead of ExecuteNonQuery for your DELETE and UPDATE statements? That does not make sense. — Soner Gönül
– Soner Gönül, Commented Dec 18, 2015 at 14:10
Also, use ExecuteNonQuery instead of ExecuteReader as you don't have any SELECT statements. — Dai
– Dai, Commented Dec 18, 2015 at 14:10

Tim Schmelter · Accepted Answer · 2015-12-18 14:39:30Z

5

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();
    }
}

edited Dec 18, 2015 at 14:39

answered Dec 18, 2015 at 14:10

Tim Schmelter

462k79 gold badges719 silver badges980 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

murday1983 Over a year ago

I'm struggling in understanding how to implement this. I have only been doing development for 3wks so please bear with me. Could you provide what you are meaning please

Tim Schmelter Over a year ago

@murday1983: have a look

murday1983 Over a year ago

Thanks but I get the following server error "ExecuteNonQuery: Connection property has not been initialized." for the updated = cmd1.ExecuteNonQuery(); line

Tim Schmelter Over a year ago

@murday1983: updated my answer, forgotten to add the connection to the constructor of SqlCommand(the second with the update). I've also changed UPDATE FROM Admin_TaskList to UPDATE Admin_TaskList

murday1983 Over a year ago

Thanks. I did notice this already and added it but when I did I got the error "Incorrect syntax near the keyword 'FROM'."

|

Collectives™ on Stack Overflow

Asp.Net SQL Update Statement

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related