Click here to Skip to main content
15,884,237 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have two tables with foreign key, first is tblFile : FileID, FileName... And Second is tblChild (in Datagridview) : ChildID, ChildName, ChildBirthDate, fkFileID.
When click button Save (in Form),; h How can i I Update All rows in second table if Add or Edit or Delete rows?
I tried this code and others:

What I have tried:

C#
string ChID;
using (SqlCommand cmd = new SqlCommand("SELECT ChildID FROM tblChild WHERE ChildID = @ChildID", con))
{
    cmd.Parameters.AddWithValue("@ChildID", dgv.Rows[0].Cells[0].Value ?? DBNull.Value);
    SqlDataAdapter sda = new SqlDataAdapter(cmd);
    DataTable dt = new DataTable();
    sda.Fill(dt);

    if (dt.Rows.Count > 0)
    {
        foreach (DataGridViewRow rowCh in dgv.Rows)
        {
            ChID = dt.Rows[0]["ChildID"].ToString();
            if (ChID == rowCh.Cells[0].Value.ToString())
            {
                using (SqlCommand cmdCh = new SqlCommand("UPDATE tblChild SET ChildName = @ChildName, ChildBirthDate = @ChildBirthDate, fkFileID = (SELECT FileID FROM tblFile WHERE FileID = @FileID) WHERE ChildID = @ChildID", ClsDB.con))
                {
                    cmdCh.Parameters.Clear();
                    cmdCh.Parameters.AddWithValue("@ChildID", rowCh.Cells[0].Value ?? DBNull.Value);
                    cmdCh.Parameters.AddWithValue("@ChildName", rowCh.Cells[1].Value ?? DBNull.Value);
                    cmdCh.Parameters.AddWithValue("@ChildBirthDate", rowCh.Cells[2].Value ?? DBNull.Value);
                    cmdCh.Parameters.AddWithValue("@FileID", txtID.Text.Trim());
                    cmdCh.ExecuteNonQuery();
                }
            }
        }
    }
    else
    {
        foreach (DataGridViewRow rowCh in dgv.Rows)
        {
            using (SqlCommand cmdCh = new SqlCommand("INSERT INTO tblChild (ChildName, ChildBirthDate, fkFileID) VALUES (@ChildName, @ChildBirthDate, (SELECT FileID FROM tblFile WHERE FileID = @FileID))", ClsDB.con))
            {
                cmdCh.Parameters.AddWithValue("@ChildName", rowCh.Cells[1].Value ?? DBNull.Value);
                cmdCh.Parameters.AddWithValue("@ChildBirthDate", rowCh.Cells[2].Value ?? DBNull.Value);
                cmdCh.Parameters.AddWithValue("@FileID", txtID.Text.Trim());
                cmdCh.ExecuteNonQuery();
            }
        }
    }
}
Posted
Updated 18-Oct-21 7:30am
v3
Comments
User_Michel 19-Oct-21 3:14am    
After thanks to everyone who helps and provides solutions..
I can't believe this.. all these genius members and I didn't find a matching solution to this question?
Bye..

1 solution

Take a look at this CodeProject article: Master Detail Datagridview in C#[^]

Another article I can recommend is: A Detailed Data Binding Tutorial[^]
You can download the examples as a zip file, it contains two "Master-Detail" samples.
Data binding is the recommended approach, it might seem complicated at first, but it is well worth learning, it will make your code much easier to maintain.
 
Share this answer
 
v2
Comments
User_Michel 18-Oct-21 13:06pm    
Thank you for your replying. i think this need to tabControl, but my project is different Design a little and simple than this article. look to ex.:
https://i.postimg.cc/jq7Ks3CW/1.jpg
BillWoodruff 18-Oct-21 17:47pm    
+5

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