Insert current row from gridview into database

Question

I'm supposed to create a master-detail form and I must add the details straight on the datagridview which is binded to the database. So I have a form with two tables: intrari (master) and intrari_detaliu (detail). When I use the binding navigator to select a row in table intrari which is the parent table, I also get the corresponding details in table intrari_detaliu. I use text boxes/combox to add value in table intrari. So how do I insert values straight into data grid view? image for the form visual structure

What I tried:

First try:

   DataClasses1DataContext db = new DataClasses1DataContext();
    private void btnadd_Click(object sender, EventArgs e)
    {
         int ID;  int id_intrari = 0; int id_produs = 0; decimal cantitate = 0; decimal valoare = 0;

         for (int i = 0; i


but this first copies every row above and then it adds the new row. E.g.: I already have inserted row A and B, and when I want to insert row C, it first inserts row A and B again and inserts then C.
Second try:
 public partial class Intrari2 : Form

{
    SqlConnection con;
    SqlDataAdapter adap;
    DataSet ds;
    SqlCommandBuilder cmbl;
         private void Intrari2_Load(object sender, EventArgs e)
    {
        con = new SqlConnection();
        con.ConnectionString = @"Data Source=DESKTOP-KVMM566;Initial 
        Catalog=MyDatabase;Integrated Security=True";
        con.Open();
        adap = new SqlDataAdapter("select ID,id_intrari,id_produs,cantitate,valoare from tbl_intrari_detaliu", con);
        ds = new System.Data.DataSet();
        adap.Fill(ds, "tbl_intrari_detaliu");
        tbl_intrari_detaliuDataGridView.DataSource = ds.Tables[0]; 
 this.tbl_intrari_detaliuTableAdapter.Fill(this.myDatabaseDataSet.tbl_intrari_detaliu); 
          this.tbl_IntrariTableAdapter.Fill(this.myDatabaseDataSet.tbl_Intrari);

      //Add button------------
     private void btnadd_Click(object sender, EventArgs e)
    {
        cmbl = new SqlCommandBuilder(adap);
        adap.Update(ds, "tbl_intrari_detaliu");

//the Fill method doesn't work anymore because of the code above
 this.tbl_intrari_detaliuTableAdapter.Fill(this.myDatabaseDataSet.tbl_intrari_detaliu);

   
   }

}
This one updates rows correctly, but when I select a row in table intari, I don't get the corresponding details (according to the id) in table intrari_detaliu. I get the details for all rows in the parent table. I know it's because of the code I added to select the data from table using SqlAdapter, but the code doesn't work without that statement.
So, I need to insert/update/delete a selected row from "intrari_detaliu" and still be able to get the corresponding details when I select a row in the parent table "intrari".
Can you help me, please?

user_1410664 · Answer

It's so much easier than that. Throw all that code away (genuinely, all of it). Here is a demo using two of my test tables. 1 Car (parent) has many Clowns (child)

Here's how your data (should) get into your parent grid. You have a query in your TA that selects stuff based on something you want to search by, eg Name:

You give the queries GOOD NAMES, not just Fill - one day you'll have many queries, don't do Fill, Fill1, Fill2. Name them well now:

In your child tableadapter you provide a query that does a lookup by Parent ID:

Or you do a query that looks up children of the same thing you just made a parent query for, like CarName here:

Or you can do both/more/whatever. You can have many queries per TA:

To get grids onto your form you drag them out of Data Sources (View menu, Other Windows). This will do all the data binding setup for you; you can examine how visual studio has done it if you ever want to replicate it manually; the parent grid binds through a binding source, to the table in the dataset. The child grid binds through a binding source to a data relation on the parent binding source

To get related data behavior you drag the CHILD NODE Clowns, not the top level Clowns out of data sources

In your code, to fill the grids with data you just actually fill the tables; the grids will update themselves. You must fill the parent first, then the child. You fill the child either using the query that selects a lot of children based on the parent criteria (like my ...ByCarName),

this.carsTableAdapter.FillByCarName(this.dataSet1.Cars, "Billy"); //fill parent first
this.clownsTableAdapter.FillByCarName(this.dataSet1.Clowns, "Billy"); //then fill the children by the same

or you can enumerate the parent after you fill, and fill the children by the parent ID (like my ...ByCarId does):

this.carsTableAdapter.FillByCarName(this.dataSet1.Cars, "Billy"); //fill parent first
clownsTableAdapter.ClearBeforeFill = false; //turn this off otherwise every call to FillBy.. erases the previous rows
foreach (var r in this.dataSet1.Cars) //for each parent
    clownsTableAdapter.FillByCarId(this.dataSet1.Clowns, r.CarId); //fill the children

Saving

Dragging the grids wrote this:

        this.Validate();
        this.carsBindingSource.EndEdit();
        this.tableAdapterManager.UpdateAll(this.dataSet1);

Add a line:

        this.Validate();
        this.carsBindingSource.EndEdit();
        this.clownsBindingSource.EndEdit();
        this.tableAdapterManager.UpdateAll(this.dataSet1);

That is all you need to do; click the button that runs that code; the tableadapter manager saves any changes you made to the data set, like adding a new row by writing in the bottom of the grid...

..or if you did it in code:

Regardless how it got there, it's a new row with the RowState of Added

When the tableadaptermanager.UpdateAll is called, or if you call an individual tableadapter.Update:

carsTableAdapter.Update(this.dataset1.Cars); //update doesn't just UPDATE, it runs INSERT and DELETE too

then the changes you made locally are saved. If someone else changed the data while you were editing it, and you have Optimistic Concurrency turned on (see above "Refresh the.." in the screenshot above) then you get an exception at the point of save. You can choose what to do to and write code that overwrites the new data, merges it, or discards the changes; ask the user what they would like to do

Note, if the DB is calculating your parent and child ID values, right clic on the relation line between your datatables, and ensure that Update is set to cascade:

This way when you add a new car and two new related clowns, and the car has -1 ID (generated locally by the dataset, pre-save), and the clowns use it too:

Then when the DB calculates the real ID, and "Refresh the dataset" option...

... causes the new ID to be retreived:

Then the changing of CarId -1 in the parent to e.g. 15 (what the DB calculated) causes the child Clowns' CarIds to be updated automatically too so the relation is preserved

TableAdapter.Update will save every kind of change; edits, deletions and inserts (new rows)

Insert current row from gridview into database

1 Answers

Saving