Click here to Skip to main content
15,867,686 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
In my WinForms App. I have two DataGridViews (master-detail).
The primary key (bill_equ_id) of my master table is generated on the server by a (before-insert trigger), so it's not manually inserted by the user. this key is a foreign key in my detail table.

i don't know how to perform insert operation if user is inserting both a master record and it's details records together, because foreign key for details is not available until master record is already inserted into DB.


can anyone help me with InsertCommand query that can handle insert into both master and detail on a single save-button click ?

here is my code for creating Grids...



public partial class Form1: Form
{
	private String connectionString = null;
	private OracleConnection oracleConnection = null;

	private String equQuery = null;
	OracleCommand equCmd = null;
	private OracleDataAdapter equDataAdapter = null;        
	private OracleCommandBuilder equComBldr = null;       
	private DataTable equDataTable = null;
	private BindingSource equBindingSource = null;

	private String partQuery = null;
	OracleCommand partCmd = null;
	private OracleDataAdapter partDataAdapter = null;
	private OracleCommandBuilder partComBldr = null;
	private DataTable partDataTable = null;
	private BindingSource partBindingSource = null;

	private DataSet dataset = null;
	
	private void Form1_Load(object sender, EventArgs e)
	{
		oracleConnection = new OracleConnection("my connection string bla bla");
		oracleConnection.Open();

		
		// MASTER SELECT CMD 
		equQuery = "select bill_equ_id  ,bill_id ,equ_serial_nbr ,maintenance_date " +
 " from bso_equipment_maint_bill_equ where bill_id = :bill_id";				
		equCmd = new OracleCommand(equQuery, oracleConnection);
		equCmd.Parameters.Add("bill_id", OracleType.Number);

		// DETAIL SELECT CMD
		partQuery = "select bill_equ_id , part_nbr , part_cost , quantity " + 
" from bso_equipment_maint_bill_part where bill_equ_id in  " +
                    " (select bill_equ_id from bso_equipment_maint_bill_equ where bill_id = :bill_id) ";

		partCmd = new OracleCommand(partQuery, oracleConnection);
		partCmd.Parameters.Add("bill_id", OracleType.Number);
		
		// CREATE ADAPTERS
		equDataAdapter = new OracleDataAdapter(equCmd);
		equComBldr = new OracleCommandBuilder(equDataAdapter);


		partDataAdapter = new OracleDataAdapter(partCmd);
		partComBldr = new OracleCommandBuilder(partDataAdapter);

		// HERE I NEED TO PROVIDE INSERTCOMMAND'S ************************
		


equCmd = new OracleCommand(" insert into bso_equipment_maint_bill_equ (bill_id ,equ_serial_nbr ,maintenance_date) " +
                "values (:bill_id , :equ_serial_nbr  , to_date( :maintenance_date) )returning bill_equ_id into :temp_bill_equ_id " ) ;


            equCmd.Parameters.Add("temp_bill_equ_id", OracleType.Number).Direction = ParameterDirection.ReturnValue;
            equCmd.Parameters.Add("bill_id", OracleType.Number);
            equCmd.Parameters.Add("equ_serial_nbr", OracleType.VarChar, 64, "equ_serial_nbr");
            equCmd.Parameters.Add("maintenance_date", OracleType.VarChar, 10, "maintenance_date");

            equDataAdapter.InsertCommand = equCmd;


            partCmd = new OracleCommand("insert into bso_equipment_maint_bill_part (bill_equ_id , part_nbr , part_cost , quantity) " +
                "values (:bill_equ_id , :part_nbr , :part_cost , :quantity ) ");

            partCmd.Parameters.Add("bill_equ_id", OracleType.Number); 
            partCmd.Parameters.Add("part_nbr", OracleType.VarChar, 64, "part_nbr");
            partCmd.Parameters.Add("part_cost", OracleType.Number);
            partCmd.Parameters["part_cost"].SourceColumn = "part_cost";
            partCmd.Parameters.Add("quantity", OracleType.Number, 10, "quantity");

            partDataAdapter.InsertCommand = partCmd;




		
		// CREATE TABLES
		equDataTable = new DataTable("bso_equipment_maint_bill_equ");
		partDataTable = new DataTable("bso_equipment_maint_bill_part");

		
		equDataTable.Columns.Add("bill_equ_id", typeof(int));
		equDataTable.PrimaryKey = new DataColumn[] { equDataTable.Columns["bill_equ_id"] };
		equDataTable.Columns.Add("bill_id", typeof(int));
		equDataTable.Columns.Add("equ_serial_nbr", typeof(string));
		equDataTable.Columns.Add("maintenance_date", typeof(DateTime));

		partDataTable.Columns.Add("bill_equ_id", typeof(int));
		partDataTable.Columns.Add("part_nbr", typeof(string));
		partDataTable.Columns.Add("part_cost", typeof(decimal));
		partDataTable.Columns.Add("quantity", typeof(string));

		// CREATE DATASET AND RELATION
		dataset = new DataSet();
		dataset.Tables.Add(equDataTable);
		dataset.Tables.Add(partDataTable);

		DataRelation relation = new DataRelation("EquPartRel", dataset.Tables["bso_equipment_maint_bill_equ"].Columns["bill_equ_id"], dataset.Tables["bso_equipment_maint_bill_part"].Columns["bill_equ_id"] , true);
		dataset.Relations.Add(relation);

		// CREATE BINDING SOURCES AND BIND TO DATAGRIDS
		equBindingSource = new BindingSource(dataset, "bso_equipment_maint_bill_equ");            
		equGrid.DataSource = equBindingSource;
		//equGrid.Columns["bill_id"].Visible = false;
		//equGrid.Columns["bill_equ_id"].Visible = false;

		partBindingSource = new BindingSource(equBindingSource, "EquPartRel");
		partGrid.DataSource = partBindingSource;
		//partGrid.Columns["bill_equ_id"].Visible = false;


        oracleConnection.Close();

	} // FORM LOAD
	
	
	private void searchBillButton_Click(object sender, EventArgs e)
    {
		equDataAdapter.SelectCommand.Parameters["bill_id"].Value = Convert.ToInt32( billID.Text );    
		partDataAdapter.SelectCommand.Parameters["bill_id"].Value = Convert.ToInt32(billID.Text);               		
		equDataAdapter.Fill(equDataTable);		
		partDataAdapter.Fill(partDataTable);
	}



 private void saveBillButton_Click(object sender, EventArgs e)
        {


equDataAdapter.InsertCommand.Parameters["bill_id"].Value = Int32.Parse( billIDText.Text);               
               equDataAdapter.Update(equDataTable);
               int bill_equ_id = Convert.ToInt32( equDataAdapter.InsertCommand.Parameters["temp_bill_equ_id"].Value.ToString());
               partDataAdapter.InsertCommand.Parameters["bill_equ_id"].Value = bill_equ_id;               
               partDataAdapter.Update(partDataTable);


               partDataTable.Clear();
               equDataTable.Clear();

               equDataAdapter.Fill(dataset.Tables["bso_equipment_maint_bill_equ"]);
               partDataAdapter.Fill(dataset.Tables["bso_equipment_maint_bill_part"]);

}
	
} // class
Posted
Updated 11-Sep-14 2:07am
v5
Comments
George Jonsson 11-Sep-14 5:15am    
The easiest way is probably to make two queries.
First insert data into the Master Table.
Then insert data into the Details Table.
This code can of course exist in the same button click event.
nina4ever 11-Sep-14 5:20am    
i already tried that an hour ago.
i insert into master and return generated primary key, then pass it as a parameter to detail insert command ...

it's doesn't perform correctly if i am inserting multiple master records with their details, because the Update method then returns the primary key of the last inserted master record, so all new detail records are inserted as children of that last master.
George Jonsson 11-Sep-14 5:46am    
Why not showing the Insert and Update code?
Are you using SQL directly or Stored Procedures?
nina4ever 11-Sep-14 6:01am    
there is no insert code YET, because the code i tried did not work correctly, do you want me to post it? ... i need direct SQL not a stored procedure.
user can insert into the grid normally , and updates should be reflected on DB on a save-button Click...
George Jonsson 11-Sep-14 6:07am    
Well, show us what you have tried so far and what errors you get or the outcome you expected but didn't get.

public partial class Form1 : Form
{
    private String connectionString = null;
    private OracleConnection oracleConnection = null;

    private String equQuery = null;
    OracleCommand equCmd = null;
    private OracleDataAdapter equDataAdapter = null;
    private OracleCommandBuilder equComBldr = null;
    private DataTable equDataTable = null;
    private BindingSource equBindingSource = null;

    private String partQuery = null;
    OracleCommand partCmd = null;
    private OracleDataAdapter partDataAdapter = null;
    private OracleCommandBuilder partComBldr = null;
    private DataTable partDataTable = null;
    private BindingSource partBindingSource = null;

    private DataSet dataset = null;

    private void Form1_Load(object sender, EventArgs e)
    {
        connectionString = "Data Source=bla;Persist Security Info=True;User ID=bla;Password=bla;Unicode=True";
        oracleConnection = new OracleConnection(connectionString);
        oracleConnection.Open();

        // SELECT QUERY 
        equQuery = "select bill_id , equ_serial_nbr ,equ_cost ,maintenance_date from bso_equipment_maint_bill_equ where bill_id = :bill_id";
        partQuery = "select bill_id , equ_serial_nbr , part_nbr , part_cost , quantity from bso_equipment_maint_bill_part where bill_id = :bill_id and equ_serial_nbr in  " +
                    " (select equ_serial_nbr from bso_equipment_maint_bill_equ where bill_id = :bill_id) ";

        // SELECT COMMAND WITH PARAMS
        equCmd = new OracleCommand(equQuery, oracleConnection);
        equCmd.Parameters.Add("bill_id", OracleType.Number);

        partCmd = new OracleCommand(partQuery, oracleConnection);
        partCmd.Parameters.Add("bill_id", OracleType.Number);

        // CREATE ADAPTERS
        equDataAdapter = new OracleDataAdapter(equCmd);
        equComBldr = new OracleCommandBuilder(equDataAdapter);

        partDataAdapter = new OracleDataAdapter(partCmd);
        partComBldr = new OracleCommandBuilder(partDataAdapter);


        // INSERT COMMANDS

        equCmd = new OracleCommand(" insert into bso_equipment_maint_bill_equ (bill_id ,equ_serial_nbr ,equ_cost ,maintenance_date) " +
            "values (:bill_id , :equ_serial_nbr , :equ_cost , to_date( :maintenance_date) )");


        equCmd.Parameters.Add("bill_id", OracleType.Number);
        equCmd.Parameters["bill_id"].SourceColumn = "bill_id";
        equCmd.Parameters.Add("equ_serial_nbr", OracleType.VarChar, 64, "equ_serial_nbr");
        equCmd.Parameters.Add("equ_cost", OracleType.Number);
        equCmd.Parameters["equ_cost"].SourceColumn = "equ_cost";
        equCmd.Parameters.Add("maintenance_date", OracleType.VarChar, 10, "maintenance_date");

        equDataAdapter.InsertCommand = equCmd;


        partCmd = new OracleCommand("insert into bso_equipment_maint_bill_part (bill_id , equ_serial_nbr , part_nbr , part_cost , quantity) " +
            "values (:bill_id , :equ_serial_nbr , :part_nbr , :part_cost , :quantity ) ");

        partCmd.Parameters.Add("bill_id", OracleType.Number);
        partCmd.Parameters["bill_id"].SourceColumn = "bill_id";
        partCmd.Parameters.Add("equ_serial_nbr", OracleType.VarChar, 64, "equ_serial_nbr");
        partCmd.Parameters.Add("part_nbr", OracleType.VarChar, 64, "part_nbr");
        partCmd.Parameters.Add("part_cost", OracleType.Number);
        partCmd.Parameters["part_cost"].SourceColumn = "part_cost";
        partCmd.Parameters.Add("quantity", OracleType.Number, 10, "quantity");

        partDataAdapter.InsertCommand = partCmd;

        // CREATE TABLES
        equDataTable = new DataTable("bso_equipment_maint_bill_equ");
        partDataTable = new DataTable("bso_equipment_maint_bill_part");

        equDataTable.Columns.Add("bill_id", typeof(int));
        equDataTable.Columns.Add("equ_serial_nbr", typeof(string));
        equDataTable.PrimaryKey = new DataColumn[] { equDataTable.Columns["bill_id"], equDataTable.Columns["equ_serial_nbr"] };
        equDataTable.Columns.Add("equ_cost", typeof(decimal));
        equDataTable.Columns.Add("maintenance_date", typeof(DateTime));


        partDataTable.Columns.Add("bill_id", typeof(int));
        partDataTable.Columns.Add("equ_serial_nbr", typeof(string));
        partDataTable.Columns.Add("part_nbr", typeof(string));
        partDataTable.PrimaryKey = new DataColumn[] { partDataTable.Columns["bill_id"], partDataTable.Columns["equ_serial_nbr"], partDataTable.Columns["part_nbr"] };
        partDataTable.Columns.Add("part_cost", typeof(decimal));
        partDataTable.Columns.Add("quantity", typeof(int));


        // CREATE DATASET AND RELATION
        dataset = new DataSet();
        dataset.Tables.Add(equDataTable);
        dataset.Tables.Add(partDataTable);

        DataRelation relation = new DataRelation("EquPartRel", new DataColumn[] { equDataTable.Columns["bill_id"], dataset.Tables["bso_equipment_maint_bill_equ"].Columns["equ_serial_nbr"] },
                                                               new DataColumn[] { partDataTable.Columns["bill_id"], dataset.Tables["bso_equipment_maint_bill_part"].Columns["equ_serial_nbr"] }, true);

        dataset.Relations.Add(relation);

        // CREATE BINDING SOURCES AND BIND TO DATAGRIDS
        equBindingSource = new BindingSource(dataset, "bso_equipment_maint_bill_equ");
        equGrid.DataSource = equBindingSource;
        equGrid.Width = 340;
        equGrid.Columns["bill_id"].Visible = false;


        partBindingSource = new BindingSource(equBindingSource, "EquPartRel");
        partGrid.DataSource = partBindingSource;
        partGrid.Width = 315;
        partGrid.Columns["bill_id"].Visible = false;
        partGrid.Columns["equ_serial_nbr"].Visible = false;

        oracleConnection.Close();

    } // FORM LOAD

    private void searchBillButton_Click(object sender, EventArgs e)
    {

        equDataAdapter.SelectCommand.Parameters["bill_id"].Value = Convert.ToInt32(billIDText.Text);
        partDataAdapter.SelectCommand.Parameters["bill_id"].Value = Convert.ToInt32(billIDText.Text);

        equDataAdapter.Fill(equDataTable);
        partDataAdapter.Fill(partDataTable);
    }


    private void saveBillButton_Click(object sender, EventArgs e)
    {
        foreach (DataGridViewRow row in equGrid.Rows)
            row.Cells["bill_id"].Value = bill_id;

        foreach (DataGridViewRow row in partGrid.Rows)
            row.Cells["bill_id"].Value = bill_id;

        equDataAdapter.SelectCommand.Parameters["bill_id"].Value = bill_id;
        partDataAdapter.SelectCommand.Parameters["bill_id"].Value = bill_id;


        // I HAD TO CALL UPDATE ON DELTED ROWS OF DETAIL GRID BEFORE ANYTHING ELSE BECAUSE :
        // IF I DELETE A MASTER ROW, DETAIL ROWS ARE DELETED AUTOMATICALLY DUE TO DATARELATION BETWEEN MASTER & DETAIL
        // SO WHEN I CALL:
        //    DATAADAPTER.UPDATE(MASTER);
        //    DATAADAPTER.UPDATE(DETAIL);
        // I GET COCURRENCY EXCEPTION... THE REASON:
        // DATAADAPTER.UPDATE(MASTER) CAUSES MASTER RECORD TO BE DELETED FROM DATABASE,
        // AND ITS DETAILS RECORDS GET AUTOMATICALLY DELETED FROM DB, TOO (ON DELETE CASCADE).
        // SO, WHEN DATAADAPTER.UPDATE(DETAIL) IS CALLED, IT FINDS THAT DETAIL RECORDS ARE MISSING FROM DATABASE,
        // AND AN EXCEPTION IS THROWN...

        DataTable deleted_details = partDataTable.GetChanges(DataRowState.Deleted);
        if (deleted_details != null) partDataAdapter.Update(deleted_details);

        equDataAdapter.Update(equDataTable);

        DataTable added_details = partDataTable.GetChanges(DataRowState.Added);
        if (added_details != null) partDataAdapter.Update(added_details);

        DataTable modified_details = partDataTable.GetChanges(DataRowState.Modified);
        if (modified_details != null) partDataAdapter.Update(modified_details);

        partDataTable.Clear();
        equDataTable.Clear();

        equDataAdapter.Fill(dataset.Tables["bso_equipment_maint_bill_equ"]);
        partDataAdapter.Fill(dataset.Tables["bso_equipment_maint_bill_part"]);

    }

} // class
 
Share this answer
 
I am not sure why you insert data to the database in the Form1_Load.
In Form1_Load you have just fetched data from the database that you want to show in the datagrids, right?

I think this is a case when it would be nice to have an alternate key in the Mastr table that can be used to get a unique row without using the primary key.

Maybe "bill_id" and "equ_serial_nbr" can be used for this purpose?

C#
private void saveBillButton_Click(object sender, EventArgs e)
{
    equBindingSource.EndEdit(); // Applies changes to the data tables

    int masterID = 0;
    // This only takes care of added rows, not updated or deleted rows
    foreach (DataRow drMaster in dataset.Tables["bso_equipment_maint_bill_equ"].Rows)
    {
        if (drMaster.RowState == DataRowState.Added)
        {
            // Here you insert data into the master table
            // INSERT INTO bso_equipment_maint_bill_equ all columns except 
            masterID = <last inserted="" id="">;
        }
        else
        {
            // SQL query to get the primary key from the database
            // Assuming you have an alternate key
            String.Format(@"
                SELECT bill_equ_id FROM bso_equipment_maint_bill_equ 
                WHERE bill_id = '{0}' 
                AND equ_serial_nbr = '{1}'", drMaster["bill_id"], drMaster["equ_serial_nbr"]); 
            masterID = <the received="" id="">;
        }
        
        foreach (DataRow drDetail in drMaster.GetChilds("EquPartRel"))
        {
            if (drDetail.RowState == DataRowState.Added)
            {
                // Here you insert data into the detail table
                // Use the value of masterID as value to set for
                // column bso_equipment_maint_bill_part.bill_equ_id
            }
        }
    }
    dataset.AcceptChanges();
}
</the></last>


You also should add this code again where you create your table
This will be a temporary key that never is stored in the DB
C#
DataColumn dc = equDataTable.Columns.Add("bill_equ_id", typeof(int));
dc.AutoIncrement = true;


Add this line of code when you have loaded the dataset from the DB.
C#
dataset.AcceptChanges();

It will make sure you only handle the changed rows.
I hope this "pseudo code" will help you on the way.
 
Share this answer
 
Comments
nina4ever 15-Sep-14 2:03am    
i am sorry i have been away for three days. i will review your solution today and feedback. P.S. i don't insert data on form load. i just assign the InsertStatement value. on save button click i call Update to insert or update data on database
nina4ever 15-Sep-14 2:49am    
where you wrote :
// Here you insert data into the master table
// INSERT INTO bso_equipment_maint_bill_equ all columns except...

and :
// Here you insert data into the detail table

did you mean that i need to perform manual inserts?
George Jonsson 15-Sep-14 3:03am    
I just meant you need to add your code for inserting data into the database in those places.
nina4ever 15-Sep-14 5:36am    
sir, thank you so much, you really helped a lot. i haven't used your code, but rather changed my primary key on database, inspired by your note :
"Maybe "bill_id" and "equ_serial_nbr" can be used for this purpose?"
when i created my database tables, i thought of that option, but didn't choose it just to avoid repeating more than one (foreign-key) column in details table, and that's why i used auto-generated key. but since it caused me troubles as you can see, i changed my ERD and just used (bill_id,equ_serial_nbr) as primary key in master (and thus, a foreign key in detail).
my detail primary key is now: bill_id , equ_serial_nbr , part_nbr.

i will post final code ASAP... maybe tomorrow

thanks again
nina4ever 16-Sep-14 8:35am    
i have posted final code.
thank you for everything
first insert your master data but return the id value in the same query

insert into table values('1525' ,'1/1/2014' ) select @@IDENTITY

and set details foreign key with this value
then insert details data
 
Share this answer
 
Comments
nina4ever 17-Sep-14 2:29am    
thank you. i have already changed my primary and foreign keys just to let DataGridView handle all operations without workarounds.
I was having the same issue on Visual Studio 2010, sql 2008 R2 and using WinForms, but I solved it.

I also had my tables with auto generated primary key from the server (auto-increment)

I used a DataSet and messed around with the DataSet Designer, created relationships, etc. But since I configured TableAdapters I didn't have to manually create the sql commands on my form class, which has one BindingNavigator with textboxes (Master table) and one DataGridView (Detail table).

I experienced the same issue he tells, if trying to save a new record from both the Master (bindingNavigatorAddNewItem in my case) and it's details (a new row), it will throw me an sqlException on my tableAdapterManager1.UpdateAll(DataSet1). Because the new master id (-1) must exist on the table before inserting a new detail along with its master id (FK_master_detail).

mhegazy94's answer gave me an idea, return the new id in the same insert query, so I went to the DataSet.Designer.cs and scrolled from all that bunch of code, to the TableAdapter's insertCommand of my master table. and after the INSERT INTO query, I added the following
SQL
SELECT column1,column2.. FROM yourmastertable
WHERE (id_column = SCOPE_IDENTITY())";


in my dataset designer's class it looks like this:

SQL
this._adapter.InsertCommand.CommandText = @"INSERT INTO Pedidos(fechaPedido,cant_pollitosNoVac,cant_pollitosVac,precioUnitario_pollitosNoVac,precioUnitario_pollitosVac,valor_PollosNoVac,valor_PollosVac,valorTotal_despacho,saldoPimpollo) VALUES(@fechaPedido,@cant_pollitosNoVac,@cant_pollitosVac,@precioUnitario_pollitosNoVac,@precioUnitario_pollitosVac,@valor_PollosNoVac,@valor_PollosVac,@valorTotal_despacho,@saldoPimpollo) SELECT idPedido, fechaPedido, cant_pollitosNoVac, cant_pollitosVac, precioUnitario_pollitosNoVac, precioUnitario_pollitosVac, valor_PollosNoVac, valor_PollosVac, valorTotal_despacho, saldoPimpollo FROM Pedidos
WHERE (idPedido = SCOPE_IDENTITY()";


Hope this helps someone else if using Datasets!!!

If wondering how I figure this out, this video helped me: https://msdn.microsoft.com/es-es/vstudio/cc138241.aspx[^], she shows exactly what this is all about, saving both a master and detail record together, so at the end of the video I looked closely at the SQL Server Profiler and checked the first query that was executed (insert into orders)
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900