SqlDataAdapter.Update(dataset) method inserts new rows but does not updates existing rows

Question

I am creating winform application that have DataGrigView to present a table. I have a DAL class that is responsible to work with DB.

There are one method that loads data of the table:

 public static void GetItemsByOrder(int orderId, ref DataSet dataSet)
    {
        string queryString = @"Select Id,OrderId as [מס' הזמנה],ItemCode as[מק""ט], ItemName as [שם פריט], ReceiptDate as [ת. הספקה],
                WarrantyExpDate as [באחריות עד],SuppliersItemCode as [מק""ט ספק], Supplier as [ספק], Count as[כמות], Active 
                FROM OrdersManager_Items where OrderId = @param";

        SqlConnection connection = new SqlConnection(connectionString);
        SqlCommand command = new SqlCommand(queryString, connection);
        command.Parameters.AddWithValue("@param", orderId);

        SqlDataAdapter adapter = new SqlDataAdapter(command);


        try
        {
            lock (myLock)
            {
                adapter.Fill(dataSet,"Items");
            }
        }
        catch (Exception ex)
        {
            LogWriter.WriteLogEntry(LogWriter.LogType.ERROR, string.Format("Failed to get Items by OrderId code from DB."+
                "This is due to exception: {0},\n StackTrace: {1}. ", ex.Message, ex.StackTrace));

            dataSet = null;
        }
    }

And second method that is responsible to update the DB with the changes that were made in the table:

public static bool UpdateItemsByOrder(int orderId, DataSet data)
    {

        string queryString = @"Select Id,OrderId as [מס' הזמנה],ItemCode as[מק""ט], ItemName as [שם פריט], ReceiptDate as [ת. הספקה],
                WarrantyExpDate as [באחריות עד],SuppliersItemCode as [מק""ט ספק], Supplier as [ספק], Count as[כמות], Active 
                FROM OrdersManager_Items where OrderId = @param";


        SqlConnection connection = new SqlConnection(connectionString);
        SqlCommand command = new SqlCommand(queryString, connection);
        command.Parameters.AddWithValue("@param", orderId);

        SqlDataAdapter adapter = new SqlDataAdapter(command);


        try
        {
            lock (myLock)
            {

                SqlCommandBuilder builder = new SqlCommandBuilder(adapter);
                int rowsUpdated = adapter.Update(data,"Items");

                return true;
            }
        }
        catch (Exception ex)
        {
            LogWriter.WriteLogEntry(LogWriter.LogType.ERROR, string.Format("Failed to update Items table in DB. This is due to exception: {0},\n StackTrace: {1}. ", ex.Message, ex.StackTrace));

            return false;
        }
    }

The problem: If in the Items table new rows were aded or deleted - UpdateItemsByOrder add/delete the rows in the DB as expected. But updates in existing rows of the Items table does not updated in DB.

There are no error or exceptions. I have tryed to add builder.GetUpdateCommand() command = no result.

I will be happy to get any help or advice. Thanks

P>S> I am using this MSDN LINK to learn how to work with SQLAdapter

Have you tried using an Update query along with command.ExecuteNonQuery()? — George Chondrompilas, Jun 01 '14 at 15:07
What you meen to use Update command? How can I write a query when I don't now wich value were updated??? — Lev Z, Jun 01 '14 at 15:10
Are you using the `AutoGenerateEditButton` of the `GridView` for the Updates? — George Chondrompilas, Jun 01 '14 at 16:37
Put the breakpoint on `adapter.Update()`, and check the `RowState` of the `DataTable`, it must be set to `Modified` in order to update the rows. — sallushan, Jun 01 '14 at 17:16
Sallushan, you are right, the rowState is "Unchanged". What is the reason?It is because the DataSet thet I load and DataSet that I try to update is not a same object? — Lev Z, Jun 02 '14 at 07:15
George Chondrompilas, No, i don't use AutoGenerateEditButton — Lev Z, Jun 02 '14 at 07:26
I start to understand something: The rows that I update in DataTable through the DGV (I can't update the DataTable directly, because it is in use as DataSource of DGV) has RowState = Unmodified. How can I solve this behaviour? Or i have to run through loop and set RowState "Modified" to all rows in the Table? — Lev Z, Jun 02 '14 at 07:37
Thanks to all. The solution is found. The problem and the solution described in this link: http://stackoverflow.com/questions/10570505/datagridview-datatable-rowstate-doesnt-change — Lev Z, Jun 02 '14 at 08:45

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

Ok, with the advice of sallushan I got the solution: The reason why DataAdapter doesn't updated the DB is that updated rows in DataTable has RowState value "Unchanged" instead "Modified".

There is 2 basic ways to resolve this problem:

Update the data direcrly in DataTable and not in DGV
Call DataTable.Rows[indexOfUpdatedRowInDGV].EndEdit() method, after making updates through DGV, as described Here.

Thanks to all for a help :-)

score 0 · Answer 2 · answered Jun 01 '14 at 15:01

0

You do realize that you run a SELECT command instead of update right? My guess is adapter.Update just does select and then reports that no lines where updated since none were.

answered Jun 01 '14 at 15:01

in need of help

1,606
14
27

If you are right, why does new rows added successfully? – Lev Z Jun 01 '14 at 15:04
I am using an example from this link http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommandbuilder.aspx – Lev Z Jun 01 '14 at 15:05
I am sure that if you run "SELECT ...." you cannot update any rows in an SQL table, for that you will need an `UPDATE table-name SET colName=value WHERE conditional` command. – in need of help Jun 01 '14 at 15:54
You can do a "SELECT INTO" which will add rows into a table. – in need of help Jun 01 '14 at 15:54

SqlDataAdapter.Update(dataset) method inserts new rows but does not updates existing rows

2 Answers2