Asp.Net SQL Delete Statement

Question

I have an Asp.net application and I am trying to delete a row from my 'Users' db if the user submits the requests via the web but I can't seem to get it to work.

HTML

<div class="panel panel-danger">
    <div class="panel-heading">
        <h3 class="panel-title">Remove User</h3>
    </div>
    <div class="panel-body">
        <asp:Label ID="lbRemoveUser" runat="server" Text="Remove User">
            <b>Enter Full Name</b>
        </asp:Label>                    
        <asp:TextBox runat="server" ID="txtRemoveUser" CssClass="form-control" AutoPostBack="true" OnTextChanged="txtRemoveUser_TextChanged" />
        <asp:Label ID="removeUserNotExist" runat="server" Text="The user entered does not exist." Visible="false" style="color: red"></asp:Label>
    </div>
    <div class="panel-footer">
        <div class="text-center">
            <asp:Button CssClass="btn btn-danger" ID="btnSubmitRemoveUser" runat="server" Text="Remove User" ToolTip="Click to remove the user from the list." OnClick="removeUserSubmitButton_Click" />
        </div>
    </div>
</div>  

<!-- Confirm Removal Modal-->
<div class="modal fade" id="confirmRemoveUserModal">
    <div class="modal-dialog" style="margin-top: 55px">
        <div class="modal-content">
            <div class="modal-header ConfirmHeader">
                <h4 class="modal-title" id="myModalLabel">Confirm Removal</h4>
            </div>
            <div class="modal-body">
                <p>Are you sure you want to remove <b><%=Session["txtRemoveUser"] %></b> from the payday lunch list?</p>
                <p>If you don't, click 'No' and the user will not be removed.</p>
            </div>
            <div class="modal-footer ConfirmFooter">
                <asp:Button id="btnRemoveConfirmYes" runat="server" CssClass="btn btn-success" Text="Yes" OnClick="btnRemoveConfirmYes_Click" ToolTip="Click to remove the user from the payday lunch list." />
                <asp:Button id="btnRemoveConfirmNo" runat="server" CssClass="btn btn-warning" Text="No" OnClick="btnAllCloses_Click" ToolTip="Click to close this screen. The user will not be removed." />
            </div>
        </div>
    </div>
</div>

Code I tried

 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 = " + txtRemoveUser.Text, conn);
        conn.Close();

        txtRemoveUser.Text = "";
        Response.Redirect("/AdminSide/TaskList.aspx");
    }

Like I said all I want is to delete the entry if it exists in my db. I already have a check to make sure that the entry exists in the 'Users' table

Do I need the SqlDataReader rd1 = cmd1.ExecuteReader(); as when I tried it, I got a server error "System.Data.SqlClient.SqlException: Invalid column name 'Test2'."

You just need ExecuteNonQuery and more important, need use parameterized queries. This kind of string concatenations are open for SQL Injection attacks. — Soner Gönül
– Soner Gönül, Commented Dec 18, 2015 at 13:25

Tim Schmelter · Accepted Answer · 2015-12-18 13:41:27Z

4

You are not using ExecuteNonQuery. You also have to wrap the user-name in apostrophes:

SqlCommand cmd1 = new SqlCommand("DELETE FROM Users WHERE Name = '" + txtRemoveUser.Text + "'", conn);

But you should always use sql parameters to prevent sql injection and other issues:

using(var cmd1 = new SqlCommand("DELETE FROM Users WHERE Name = @Name", conn))
{
    cmd1.Parameters.Add("@Name", SqlDbType.VarChar).Value = txtRemoveUser.Text;
    conn.Open();
    cmd1.ExecuteNonQuery();
}

also use the using-statement on types that implement IDisposable like SqlCommand or -more important- SqlConnection to ensure that unmanaged resources are disposed.

edited Dec 18, 2015 at 13:41

answered Dec 18, 2015 at 13:26

Tim Schmelter

462k79 gold badges719 silver badges980 bronze badges

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

1 Comment

murday1983 Over a year ago

Don't suppose you'd also be able to help me with stackoverflow.com/questions/34357484/…

Collectives™ on Stack Overflow

Asp.Net SQL Delete Statement

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related