SqlCommand doesn't UPDATE and DELETE database when used in ASP.NET

Question

I am having trouble with UPDATE and DELETE data in database when working with ASP.NET web form, the code work well with Windows form so I don't know what I did wrong. The code is suppose to update the Gridview with new edited data but when I click edit button, nothing happen to the gridview as well as the datatable.

This is just an exercise that there is no security requirement so I just want to know how to make it work first.

protected void Edit_btn_Click(object sender, EventArgs e)
{
        if (sqlCon.State == ConnectionState.Closed)
        {
            sqlCon.Open();
        }

        SqlCommand command = new SqlCommand();
        command.Connection = sqlCon;

        command.CommandText = ("UPDATE WareHouse SET [Name] = '" + Name_Field.Text + "' WHERE [Number] = '" + selectedName + "'");
        command.ExecuteNonQuery();

        command.CommandText = ("UPDATE WareHouse SET [Number] = '" + Number_Field.Text + "' WHERE [Number] = '" + selectedName + "'");
        command.ExecuteNonQuery();

        command.CommandText = ("UPDATE WareHouse SET [Storage] = '" + Storage_Field.Text + "' WHERE [Number] = '" + selectedName + "'");
        command.ExecuteNonQuery();

        command.CommandText = ("UPDATE WareHouse SET [Shelf] = '" + Shelf_Field.Text + "' WHERE [Number] = '" + selectedName + "'");
        command.ExecuteNonQuery();

        command.CommandText = ("UPDATE WareHouse SET [Brand] = '" + Brand_Field.Text + "' WHERE [Number] = '" + selectedName + "'");
        command.ExecuteNonQuery();

        SqlDataAdapter ad = new SqlDataAdapter("SELECT * FROM WareHouse", sqlCon);
        DataTable ds = new DataTable();
        ad.Fill(ds);                    // Fill t with data from Adapter a

        GridView1.DataSource = ds; // Get data from Source t 
        GridView1.DataBind();
    }

and for delete data

    protected void Remove_btn_Click(object sender, EventArgs e)
    {
        if (sqlCon.State == ConnectionState.Closed)
        {
            sqlCon.Open();
        }

        SqlCommand command = new SqlCommand();
        command.Connection = sqlCon;
        command.CommandText = "DELETE FROM WareHouse WHERE [Name] = '" + Name_Field.Text + "' AND [Number] = '" + selectedNumber + "' AND [Storage] = '" + selectedStorage + "' AND [Shelf] = '" + selectedShelf + "' AND [Brand] = '" + selectedBrand + "'";
        command.ExecuteNonQuery();

        clear();
        showData();
    }

Aside these 2 function, there are other two that do adding and searching from database which also use SqlCommand and they work fine without problem. Is there any problem with my query?

You do know that you can update more than one column at a time? Also, that this is a horrific example of SQL Injection just waiting to happen (please read up on parameters). And finally, that it looks like you're trying to share a connection object and that rarely works out well. None of these are your current problem, but still... — Damien_The_Unbeliever, Apr 14 '17 at 06:27
You should never ever create SQL statements that way: SQL injection. Any errors shown ? Are you sure the code is ever executed ? — Marged, Apr 14 '17 at 06:28
Are you sure about this `"' WHERE [Number] = '" + selectedName + "'"` since number is wrapped into single quotes — Jaydip Jadhav, Apr 14 '17 at 06:36
As described in comments by others, your code needs lot of improvement. But can you explain how it is not working? Are you seeing any error or exception? Did you try to debug the code and see it the statements are actually executing? How are you assigning value to `selectedName` variable? — Chetan, Apr 14 '17 at 06:53

score 0 · Answer 1 · answered Apr 14 '17 at 07:04

Storage, Shelf and Brand wouldn't be updated since you are updating [Number] to have the value of Number_Field.Text and then comparing with selectedName in where clause.

It will help you a great deal to put all this SQL code in SP with parameters and call it from ASP.Net code.

score 0 · Answer 2 · answered Apr 14 '17 at 07:04

Ok.I also faced this issue back when I was learning ASP.NET. But i had a little different env. I had a datagrid to play with and any updates in datagrid content should reflect back in DB table upon clicking update button.

So I had below query to populate the grid.

Try
        Dim UpperCase As String = UCase(HostnameTextBox.Text)

        Dim sql As String = "select * from HOST_DETAILS where upper(HOSTNAME) like '%" + UpperCase + "%'"
        da = New OracleDataAdapter(sql, conn)
        ds.Clear()
        da.Fill(ds, "TEST")
        DataGridView1.DataSource = ds.Tables(0)
    Catch ex As Exception
        MessageBox.Show(ex.Message.ToString())
    End Try

And the below one to update the table on the Update button click.

conn.Open()

    Try
        Dim ocb As New OracleCommandBuilder
        ocb = New OracleCommandBuilder(da)
        da.Update(ds, "TEST")
        MessageBox.Show("Information Updated")
    Catch ex As Exception
        MessageBox.Show(ex.Message.ToString())


    End Try
    conn.Close()

Also make sure DataAdapter da is global and is defined right after public class so it can be accessed from both.

   Dim da As New OracleDataAdapter

Hope this helps.

VB code but it wont be too much trouble to change the syntax. — PSYDUCK, Apr 14 '17 at 07:05

SqlCommand doesn't UPDATE and DELETE database when used in ASP.NET

2 Answers2