Insert current row from gridview into database-CodePudding

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?

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)