How can I update datagridview

Viewed 84

I've 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), how can I Update All rows in second table if Add or Edit or Delete rows? (Form ex: https://i.postimg.cc/jq7Ks3CW/1.jpg)

I tried this code and others:

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();
            }
        }
    }
}
0 Answers
Related