Mismatch between foreign key and primary key is not detected

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
 
I

Ignacio Machin ( .NET/ C# MVP )

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
 
T

Tony Johansson

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
 
T

Tony

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...
 
T

Tony

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Primary and Foreign Keys Question 1
Saving Master-Detail bound form - primary key is identity column 2
What is the difference between a primary foreign key and a non-pr. 1
Table Wizard Does Not Set Relationship if Foreign Key and Primary Key Name Do Not Match Exactly in C 3

Top