Delete & Update query doesn't work in ADO.NET

Question

I am trying to DELETE a record of Access Database using OleDbCommand class of Connected Architecture

using System.Data.OleDb;
using System.Data;

protected void Button2_Click(object sender, EventArgs e)
        {
        String x = "Connection String...";
        OleDbConnection con = new OleDbConnection(x);
        con.Open();


        String query = "Delete FROM TB WHERE NO=@number";
        OleDbCommand cmd = new OleDbCommand(query, con);
        cmd.Parameters.AddWithValue("@number", TextBox2.Text);


        int res = cmd.ExecuteNonQuery();
        if (res > 0)
        {
            Label.Text = "Deleted successfully";
        }
        else
        {
            Label.Text = "Not Deleted";
        }

        con.Close();
    }

Every time I try deleting record Else Condition is executed which is NOT DELETED.

Same problem with UPDATE query,

protected void Button3_Click(object sender, EventArgs e)
    {

        String x = "Connection String..";
        OleDbConnection con = new OleDbConnection(x);
        con.Open();


        String query = "UPDATE TB SET NM = @name WHERE NO = @TextBox_NO";
        OleDbCommand cmd = new OleDbCommand(query, con);

        int res = cmd.ExecuteNonQuery();
        if (res > 0)
        {
            Label.Text = "Updated successfully";
        }
        else
        {
            Label.Text = "Not Updated";
        }
        con.Close();
    }

INSERT query works perfectly fine. Where am I doing wrong?

You should [never use](http://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/) `AddWithValue` — DavidG, Sep 12 '15 at 07:23

score 2 · Answer 1 · edited May 23 '17 at 10:26

i think there is any datatype conversion error, that's why it's not deleting, and for the update case you just missed the parameter to pass @name,@TextBox_No

See here Why to use Add()

You need to change parameter passing method AddedWithValue() to Add()

Delete:

String query = "Delete FROM TB WHERE NO=@number";
        OleDbCommand cmd = new OleDbCommand(query, con);
        cmd.Parameters.Add("@number", OleDbType.Numeric, 30).Value=TextBox2.Text;

        int res = cmd.ExecuteNonQuery();
        if (res > 0)
        {
            Label.Text = "Deleted successfully";
        }
        else
        {
            Label.Text = "Not Deleted";
        }

        con.Close();

and for Update u missed the parameter to pass:

String x = "Connection String..";
        OleDbConnection con = new OleDbConnection(x);
        con.Open();


        String query = "UPDATE TB SET NM = @name WHERE NO = @TextBox_NO";
        OleDbCommand cmd = new OleDbCommand(query, con);
       cmd.Parameters.Add("@name ", OleDbType.VarChar, 200).Value=your_Name_Variable;//

        cmd.Parameters.Add("@TextBox_NO", OleDbType.Numeric, 30).Value=Your_No_Variable;

        int res = cmd.ExecuteNonQuery();
        if (res > 0)
        {
            Label.Text = "Updated successfully";
        }
        else
        {
            Label.Text = "Not Updated";
        }
        con.Close();

Error: cmd.Parameters.Add("@number", OleDbType.Numeric, 30)=TextBox2.Text; Left-hand side of an assignment must be a variable, property or indexer. — Palak, Sep 12 '15 at 08:22
Try, `cmd.Parameters.Add("@number", OleDbType.Numeric, 30).Value=TextBox2.Text;` @Palak — A_Sk, Sep 12 '15 at 08:26
Your answer has made some good points but I am not getting desired output. — Palak, Sep 12 '15 at 08:38
`not getting desired output`,(so, there must be any problem), so, whats the problem?@Palak — A_Sk, Sep 12 '15 at 08:40

score 2 · Accepted Answer · answered Sep 12 '15 at 15:05

Preface: I know nothing of ASP.NET but I do know MS Access. And NO is a reserved word. Hence, if reserved words are used may result in unexpected answers or errors when referenced as fields.

To resolve, consider bracketing the NO column in both delete and update queries.

String query = "DELETE FROM TB WHERE [NO] = @number"

String query = "UPDATE TB SET NM = @name WHERE [NO] = @TextBox_NO"

I can confirm this solution as I just tested a NO vs [NO] column reference in a SQL query in MS Access 2013. The former returned zero records but latter returned correct records.

Rahul · Answer 3 · 2015-09-12T07:27:26.010

1

If it's not deleting any record that means int res = cmd.ExecuteNonQuery(); is returning 0 or no records deleted. Make sure that the condition in your WHERE clause WHERE NO=@number matches any record. To validate run a select along the line with the same condition

SELECT 1 FROM TB WHERE NO=@number

Also, try trimming the textbox data before punching as parameter like

    cmd.Parameters.AddWithValue("@number", TextBox2.Text.Trim());

If NO is of type INT then covert it to integer before passing as parameter like

 cmd.Parameters.AddWithValue("@number", Convert.ToInt32(TextBox2.Text.Trim()));

You can follow the same rules for your UPDATE case as well. Also, I don't see you are passing any parameter for your UPDATE query. Did you just skipped that in posted code?

    String query = "UPDATE TB SET NM = @name WHERE NO = @TextBox_NO";
    OleDbCommand cmd = new OleDbCommand(query, con);

edited Sep 12 '15 at 07:27

answered Sep 12 '15 at 07:23

Rahul

76,197
13
71
125

1

And don't use AddWithValue! – DavidG Sep 12 '15 at 07:26
@Rahul can you explain how trim would make a difference? – Palak Sep 12 '15 at 07:42
1

@Palak, Trim would definitely make a difference in case there is a space in your textbox data and in which case the `WHERE` clause will be false definitely. Hope that make sense. – Rahul Sep 12 '15 at 07:43
@Palak, that's why both delete/update doesn't work. Hope you got my point now. – Rahul Sep 12 '15 at 07:54

Delete & Update query doesn't work in ADO.NET

3 Answers3