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:
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();
}
}
}
}