Mismatch between foreign key and primary key is not detected

  • Thread starter Thread starter Tony Johansson
  • Start date Start date
T

Tony Johansson

Hello!

Below is an event handler called saveButton_Click.
I have also a bound DataGridView that display the Product table from the
Northwind database that is included in VS2005 and can also be downloaded.
In this Product table is two foreign keys one from table Suppliers and one
from table Categories

Now to my question: If I change the foreign key supplierID in the
DataGridView in
a way that this supplierID doesn't exist as primary key in the Suppliers
table that would cause problem.
In the event handler I have these two central rows
DataTable dt = changes.Tables["Products"];
DataRow[] badRows = dt.GetErrors();
I mean that when the foreign key doesn't have a match to the primary this
method GetErrors() that
is called on an object of DataTable would find this error but the array
badRows is empty.
Can explain whay this GetErrors() doesn't find this mismatch between fk and
pk.

private void saveButton_Click(object sender, EventArgs e)
{
try
{
NorthwindDataSet changes =
(NorthwindDataSet)northwindDataSet.GetChanges();
if (changes == null)
{
return;
}

DataTable dt = changes.Tables["Products"];
DataRow[] badRows = dt.GetErrors();

if (badRows.Length == 0)
{
MessageBox.Show("Ok to update database");
}
else
{
string errMsg = null;
foreach (DataRow row in badRows)
{
foreach (DataColumn col in
row.GetColumnsInError())
errMsg += row.GetColumnError(col);
}
MessageBox.Show("Errors in data:" + errMsg,
"Please fix", MessageBoxButtons.OK,
MessageBoxIcon.Error);
}
}
catch (Exception ex)
{
MessageBox.Show("Error: " + ex.Message, "Errors",
MessageBoxButtons.OK, MessageBoxIcon.Error);
northwindDataSet.RejectChanges();
}
}

//Tony
 
Hello!

Below  is an event handler called saveButton_Click.
I have also a bound DataGridView that display the Product table from the
Northwind database that is included in VS2005 and can also be downloaded.
In this Product table is two foreign keys one from table Suppliers and one
from table Categories

Now to my question: If I change the foreign key supplierID in the
DataGridView in
a way that this supplierID doesn't exist as primary key in the Suppliers
table that would cause problem.
In the event handler I have these two central rows
DataTable dt = changes.Tables["Products"];
DataRow[] badRows = dt.GetErrors();
I mean that when the foreign key doesn't have a match to the primary this
method GetErrors() that
is called on an object of DataTable would find this error but the array
badRows is empty.
Can explain whay this GetErrors() doesn't find this mismatch between fk and
pk.

private void saveButton_Click(object sender, EventArgs e)
{
         try
         {
               NorthwindDataSet changes =
(NorthwindDataSet)northwindDataSet.GetChanges();
                if (changes == null)
                {
                     return;
                 }

                 DataTable dt = changes.Tables["Products"];
                 DataRow[] badRows = dt.GetErrors();

                if (badRows.Length == 0)
                {
                       MessageBox.Show("Ok to update database");
                }
                else
                {
                       string errMsg = null;
                       foreach (DataRow row in badRows)
                       {
                              foreach (DataColumn col in
row.GetColumnsInError())
                             errMsg += row.GetColumnError(col);
                       }
                       MessageBox.Show("Errors indata:" + errMsg,
                               "Please fix", MessageBoxButtons.OK,
                               MessageBoxIcon.Error);
                }
         }
         catch (Exception ex)
         {
            MessageBox.Show("Error: " + ex.Message, "Errors",
MessageBoxButtons.OK, MessageBoxIcon.Error);
            northwindDataSet.RejectChanges();
         }

}

//Tony
Click to expand...

Hi,

Two questions,

are you saving your data back to the DB? if not no error exist yet.

are you sure that the FK are present in the DB? creaet a diagram and
drag the tables there, maybe the FK are not there, or are not being
enforced
 
Hello!

There is a foreign key defined and the FK is enable and the integrity is on.
I assume when there is only the single DataTable Product in the DataSet
there
is probably impossible to detect violation for missmatch between FK and PK
as
I mentioned in the previous mail.

//Tony

"Ignacio Machin ( .NET/ C# MVP )" <[email protected]> skrev i
meddelandet
Hello!

Below is an event handler called saveButton_Click.
I have also a bound DataGridView that display the Product table from the
Northwind database that is included in VS2005 and can also be downloaded.
In this Product table is two foreign keys one from table Suppliers and one
from table Categories

Now to my question: If I change the foreign key supplierID in the
DataGridView in
a way that this supplierID doesn't exist as primary key in the Suppliers
table that would cause problem.
In the event handler I have these two central rows
DataTable dt = changes.Tables["Products"];
DataRow[] badRows = dt.GetErrors();
I mean that when the foreign key doesn't have a match to the primary this
method GetErrors() that
is called on an object of DataTable would find this error but the array
badRows is empty.
Can explain whay this GetErrors() doesn't find this mismatch between fk
and
pk.

private void saveButton_Click(object sender, EventArgs e)
{
try
{
NorthwindDataSet changes =
(NorthwindDataSet)northwindDataSet.GetChanges();
if (changes == null)
{
return;
}

DataTable dt = changes.Tables["Products"];
DataRow[] badRows = dt.GetErrors();

if (badRows.Length == 0)
{
MessageBox.Show("Ok to update database");
}
else
{
string errMsg = null;
foreach (DataRow row in badRows)
{
foreach (DataColumn col in
row.GetColumnsInError())
errMsg += row.GetColumnError(col);
}
MessageBox.Show("Errors in data:" + errMsg,
"Please fix", MessageBoxButtons.OK,
MessageBoxIcon.Error);
}
}
catch (Exception ex)
{
MessageBox.Show("Error: " + ex.Message, "Errors",
MessageBoxButtons.OK, MessageBoxIcon.Error);
northwindDataSet.RejectChanges();
}

}

//Tony
Click to expand...

Hi,

Two questions,

are you saving your data back to the DB? if not no error exist yet.

are you sure that the FK are present in the DB? creaet a diagram and
drag the tables there, maybe the FK are not there, or are not being
enforced
 
Tony Johansson said:
Hello!

Below is an event handler called saveButton_Click.
I have also a bound DataGridView that display the Product table from the
Northwind database that is included in VS2005 and can also be downloaded.
In this Product table is two foreign keys one from table Suppliers and one
from table Categories

Now to my question: If I change the foreign key supplierID in the
DataGridView in
a way that this supplierID doesn't exist as primary key in the Suppliers
table that would cause problem.
In the event handler I have these two central rows
DataTable dt = changes.Tables["Products"];
DataRow[] badRows = dt.GetErrors();
I mean that when the foreign key doesn't have a match to the primary this
method GetErrors() that
is called on an object of DataTable would find this error but the array
badRows is empty.
Can explain whay this GetErrors() doesn't find this mismatch between fk
and pk.

private void saveButton_Click(object sender, EventArgs e)
{
try
{
NorthwindDataSet changes =
(NorthwindDataSet)northwindDataSet.GetChanges();
if (changes == null)
{
return;
}

DataTable dt = changes.Tables["Products"];
DataRow[] badRows = dt.GetErrors();

if (badRows.Length == 0)
{
MessageBox.Show("Ok to update database");
}
else
{
string errMsg = null;
foreach (DataRow row in badRows)
{
foreach (DataColumn col in
row.GetColumnsInError())
errMsg += row.GetColumnError(col);
}
MessageBox.Show("Errors in data:" + errMsg,
"Please fix", MessageBoxButtons.OK,
MessageBoxIcon.Error);
}
}
catch (Exception ex)
{
MessageBox.Show("Error: " + ex.Message, "Errors",
MessageBoxButtons.OK, MessageBoxIcon.Error);
northwindDataSet.RejectChanges();
}
}

//Tony
Click to expand...
 
Tony Johansson said:
Hello!

Below is an event handler called saveButton_Click.
I have also a bound DataGridView that display the Product table from the
Northwind database that is included in VS2005 and can also be downloaded.
In this Product table is two foreign keys one from table Suppliers and one
from table Categories

Now to my question: If I change the foreign key supplierID in the
DataGridView in
a way that this supplierID doesn't exist as primary key in the Suppliers
table that would cause problem.
In the event handler I have these two central rows
DataTable dt = changes.Tables["Products"];
DataRow[] badRows = dt.GetErrors();
I mean that when the foreign key doesn't have a match to the primary this
method GetErrors() that
is called on an object of DataTable would find this error but the array
badRows is empty.
Can explain whay this GetErrors() doesn't find this mismatch between fk
and pk.

private void saveButton_Click(object sender, EventArgs e)
{
try
{
NorthwindDataSet changes =
(NorthwindDataSet)northwindDataSet.GetChanges();
if (changes == null)
{
return;
}

DataTable dt = changes.Tables["Products"];
DataRow[] badRows = dt.GetErrors();

if (badRows.Length == 0)
{
MessageBox.Show("Ok to update database");
}
else
{
string errMsg = null;
foreach (DataRow row in badRows)
{
foreach (DataColumn col in
row.GetColumnsInError())
errMsg += row.GetColumnError(col);
}
MessageBox.Show("Errors in data:" + errMsg,
"Please fix", MessageBoxButtons.OK,
MessageBoxIcon.Error);
}
}
catch (Exception ex)
{
MessageBox.Show("Error: " + ex.Message, "Errors",
MessageBoxButtons.OK, MessageBoxIcon.Error);
northwindDataSet.RejectChanges();
}
}

//Tony
Click to expand...
 
Back
Top