How to Insert and Update datagrid row in database table

Question

I am developing C# without using any framework.until now i successfully performed following task on Datagrid.

1.Displaying data in Data-grid by binding with database table
2.Getting the selected row data from Data-grid based on primary key

Here is my variable declaration :

SQLiteDataAdapter adap;
SQLiteCommandBuilder cmdbl;
DataSet ds;
String Query;
DataTable dt;

Here is code for displaying data in Data-grid :

try
{
    Query = "Select * from Items";
    adap = new SQLiteDataAdapter(Query, GlobalVars.conn);
    ds = new DataSet();
    adap.Fill(ds, "Items");
    dt = ds.Tables[0];
    dtgitems.DataContext = ds.Tables[0].DefaultView;
    dtgitems.ItemsSource = dt.DefaultView;
    ds.Dispose();
    adap.Dispose();
    dt.Dispose();
}
catch (Exception ex)
{
    MessageBox.Show(ex.ToString());
}

It is working fine and here is my code for updating that data-grid

try
 {
     cmdbl = new SQLiteCommandBuilder(adap);
     adap.Update(ds, "Items");
    // ds.Tables[0].Clear();
 }
 catch (Exception ex)
 {

 }

it is not working .Does anyone knows how i can accomplish this update functionality in data-grid ? .Please help me to correct this code for update opreation .Thanks

`DataContext`? `ItemsSource`? Winforms? – dotNET Nov 28 '13 at 07:38 — dotNET, Nov 28 '13 at 07:38

Bram Van Strydonck · Answer 1 · 2013-11-29T06:50:15.810

0

Although I would work with an ObservableCollection, you can update your datagrid items with the following code:

dtgItems.Items.Refresh();

Do this after you updated the source. I should use it in your code like this: You should accept the changes so your database gets updated.

try
{
    cmdbl = new SQLiteCommandBuilder(adap);
    adap.Update(ds, "Items");
    ds.Tables[0].AcceptChanges();
    dtgItems.Items.Refresh();
}
catch (Exception ex)
{

}

edited Nov 29 '13 at 06:50

answered Nov 28 '13 at 08:47

Bram Van Strydonck

934
5
11

Where to add this command ?: dtgItems.Items.Refresh(); in update code like this : try { cmdbl = new SQLiteCommandBuilder(adap); adap.Update(ds, "Items"); dtgitems.Items.Refresh(); MessageBox.Show("updated"); } catch (Exception ex) { } – user3012262 Nov 28 '13 at 09:03
still not working with dtgItems.Items.Refresh(); command..it is not updating database .... – user3012262 Nov 28 '13 at 09:21
code is executed till this line adap.Update(ds, "Items"); after that line are skipped – user3012262 Nov 28 '13 at 09:24
Your question was to update the datagrid, not the database. Consider changing your question then. – Bram Van Strydonck Nov 28 '13 at 09:24
Check the return value of adap.update; It should say how many rows that are succesfully updated. – Bram Van Strydonck Nov 28 '13 at 09:34
on debugging by placing break point ...It does not show any return value .....it just shows :Delete command -Null, Insert command-Null,Update Command -null,Select command -null ...and other details – user3012262 Nov 28 '13 at 09:44
Yes, but when you set a breakpoint on the line "adap.Update..." the method is not yet executed. For testing purpose do something like this: var returnValue = adap.Update(ds, "Items"); and set breakpoint on dtgItems.Items.refresh. When it breaks, check the value of returnValue – Bram Van Strydonck Nov 28 '13 at 09:56
returnValue=0 in while debugging in watch window – user3012262 Nov 28 '13 at 10:07
if i try this code with updated data grid the Message box does not appear ....other wise message box is shown ....this means that update function is not updating my table ....code is : try { cmdbl = new SQLiteCommandBuilder(adap); var returnValue = adap.Update(ds, "Items"); dtgitems.Items.Refresh(); MessageBox.Show("updated"); } – user3012262 Nov 28 '13 at 11:30
Check this question: http://stackoverflow.com/questions/2711021/sqlitedataadapter-update-method-returning-0 – Bram Van Strydonck Nov 29 '13 at 06:48
still same behavior ...it is not updating in db ..execution still not progress after this command ...without any compiler message ..adap.Update(ds, "Items"); ....please Help – user3012262 Nov 29 '13 at 07:31
It is off topic,, you should ask new question. I have no experience with SqlLiteCommandBuilder – Bram Van Strydonck Nov 29 '13 at 08:58
No.... same code(with few keyword difference) with same db is working in win-from with data grid view ...But in wpf it is not working with data-grid ....so SqlLiteCommandBuilder is working correctly in win-form – user3012262 Nov 29 '13 at 09:04

How to Insert and Update datagrid row in database table

1 Answers1