sql ExecuteNonQuery doesn't update the database in C#

Question

i'm making a small C# program that connects to MS SQL 2008 database. i have the following function, while i use a simmilar one to INSERT new data to database, when i want to update it, there are no changes in the database. Am i missing something?

public bool UpdateItem(int invID, int serialNum, string catA, string catB, string itemName, int quantity,
                           string supplierName, float cost, float price, string comments)
    {
        _cmd.CommandText = "UPDATE inventory SET serialNum=@serialNum,catID=@catID,itemName=@itemName,quantity=@quantity,supplierId=@supplierId,cost=@cost,price=@price,comments=@comments WHERE invID=@invID;";
        _cmd.Parameters.Clear();
        _cmd.Parameters.AddWithValue("@serialNum",serialNum);
        _cmd.Parameters.AddWithValue("@catID", getCatID(catA, catB));
        _cmd.Parameters.AddWithValue("@itemName",itemName);
        _cmd.Parameters.AddWithValue("@quantity",quantity);
        _cmd.Parameters.AddWithValue("@supplierId",GetSupplierIdByName(supplierName));
        _cmd.Parameters.AddWithValue("@cost",cost);
        _cmd.Parameters.AddWithValue("@price", price);
        _cmd.Parameters.AddWithValue("@comments", comments);
        _cmd.Parameters.AddWithValue("@invID", invID);
        _con.Open(); 
        _cmd.ExecuteNonQuery();
        _con.Close();
        return true;
    }

this is the database table stucture

table inventory (
invID int IDENTITY(1,1) primary key,
serialNum int not null,
catID int not null,
itemName nvarchar(20) not null,
quantity int not null,
supplierId int not null,
cost real not null,
price real not null,
comments nvarchar (50),
limbo BIT,
foreign key (supplierId)
references suppliers,
foreign key (catID)
references category)

INSERT

 public bool AddNewItem(int serialNum, int catID, string itemName, int quantity,
                           int supplierId, float cost, float price, string comments)
    {
        _cmd.CommandText =
            "INSERT INTO inventory (serialNum,catID ,itemName ,quantity ,supplierId ,cost ,price ,comments ,limbo ) VALUES " +
                                    "(@serialNum,@catID ,@itemName ,@quantity ,@supplierId ,@cost ,@price ,@comments ,@limbo );";
        _cmd.Parameters.Clear();
        _cmd.Parameters.AddWithValue("@serialNum", serialNum);
        _cmd.Parameters.AddWithValue("@catID", catID);
        _cmd.Parameters.AddWithValue("@itemName", itemName);
        _cmd.Parameters.AddWithValue("@quantity", quantity);
        _cmd.Parameters.AddWithValue("@supplierId", supplierId);
        _cmd.Parameters.AddWithValue("@cost", cost);
        _cmd.Parameters.AddWithValue("@price", price);
        _cmd.Parameters.AddWithValue("@comments", comments);
        _cmd.Parameters.AddWithValue("@limbo", 0);
        _con.Open(); 
        _cmd.ExecuteNonQuery(); 
        _con.Close();
        return true;
    }

you are adding invID with all values which is autoincrement... do you get any error ??? — Ameya Deshpande, Jan 22 '15 at 04:02
@POHH no errors, just no update. what do you mean "autoincrement" — Iakovl, Jan 22 '15 at 04:04
@GrantWinney the invID is taken from the table, it excludes the option of it not being there. added INSERT — Iakovl, Jan 22 '15 at 04:17
After you insert the new item are you refreshing your application so that the newly created ID is attached to the item? I'm thinking you're invID on the UPDATE statement is incorrect. Have you tried putting a break point right after the update function gets called and checked the value of invID? — Sean Cox, Jan 22 '15 at 04:21
@TheShaman after i insert into the data base the gridview i use to view the table updates and the new item is there with it's invID — Iakovl, Jan 22 '15 at 04:26
Okay and if you put a breakpoint right after the open bracket of the UpdateItem function and look at invID, is it the same as the one you are trying to update in the database. If it is then I'm stumped. :/ — Sean Cox, Jan 22 '15 at 04:28
There are two possibilities I can think of. First one is your invID does not exists. Second one is, you are passing same values and not the updated values. Please double check values by putting a break point. Your code looks good. — Kiran Varsani, Jan 22 '15 at 04:42
Is the UpdateItem invoked in a transaction flow, where there is a possibility of a rollback? code looks ok. — Y.S, Jan 22 '15 at 04:53
looked at the values get "SELECT * FROM suppliers WHERE name=N'KSP';"" from the commandtext and no the correct one — Iakovl, Jan 28 '15 at 00:18

score -1 · Answer 1 · answered Jan 22 '15 at 06:19

public bool AddNewItem(int serialNum, int catID, string itemName, int quantity,
                           int supplierId, float cost, float price, string comments)
    {
        _cmd.CommandText =
            "INSERT INTO inventory (serialNum,catID ,itemName ,quantity ,supplierId ,cost ,price ,comments ,limbo ) VALUES " +
                                    "(@serialNum,@catID ,@itemName ,@quantity ,@supplierId ,@cost ,@price ,@comments ,@limbo );";
        _cmd.Parameters.Clear();
        _cmd.Parameters.AddWithValue("@serialNum", serialNum);
        _cmd.Parameters.AddWithValue("@catID", catID);
        _cmd.Parameters.AddWithValue("@itemName", itemName);
        _cmd.Parameters.AddWithValue("@quantity", quantity);
        _cmd.Parameters.AddWithValue("@supplierId", supplierId);
        _cmd.Parameters.AddWithValue("@cost", cost);
        _cmd.Parameters.AddWithValue("@price", price);
        _cmd.Parameters.AddWithValue("@comments", comments);
        _cmd.Parameters.AddWithValue("@limbo", 0);
        _con.Open(); 
         _cmd.Connection = _con;  /* Set Connection for the command*/
        _cmd.ExecuteNonQuery(); 
        _con.Close();
        return true;
    }

sql ExecuteNonQuery doesn't update the database in C#

1 Answers1