Adding a row with null columns to a typed dataset in ADO.NET 2.0

[tmeynink]

Hi,

In ADO.NET 2.0 if you have a table with foreign keys in it, when you
add a row using the strongly typed add row method you pass references
to the foreign rows rather than the foreign key value itself.

If any of these foreign key row references are null, I get an
exception.

The only way I have found to work around this is to use the following
for each column with a foreign key that can be null:

TypedDataSet.SomeTableRow myNewRow = ds.SomeTable.NewSomeTableRow ();
TypedDataSet.ForeignTableRow foreignRow = ds.ForeignTable.FindByID
(id);
//
// repeat for each foreign key column that allows nulls...
//
if (null != foreignRow)
{
myNewRow.ForeignKeyID = foreignRow.ID;
}
else
{
myNewRow["ForeignKeyID"] = DBNull.Value;
}

ds.SomeTable.AddSomeTableRow (myNewRow);

I must be missing something here - surely the generated code is smart
enough to detect null values for foreign key references and insert a
DBNull.Value in the appropriate foreign key columns (where null values
are allowed of course).

What am I missing here?

Thanks,
Todd

 

[luxspes]

Hi,

In ADO.NET 2.0 if you have a table with foreign keys in it, when you
add a row using the strongly typed add row method you pass references
to the foreign rows rather than the foreign key value itself.

If any of these foreign key row references are null, I get an
exception.

The only way I have found to work around this is to use the following
for each column with a foreign key that can be null:

TypedDataSet.SomeTableRow myNewRow = ds.SomeTable.NewSomeTableRow ();
TypedDataSet.ForeignTableRow foreignRow = ds.ForeignTable.FindByID
(id);
//
// repeat for each foreign key column that allows nulls...
//
if (null != foreignRow)
{
myNewRow.ForeignKeyID = foreignRow.ID;
}
else
{
myNewRow["ForeignKeyID"] = DBNull.Value;
}

ds.SomeTable.AddSomeTableRow (myNewRow);

I must be missing something here - surely the generated code is smart
enough to detect null values for foreign key references and insert a
DBNull.Value in the appropriate foreign key columns (where null values
are allowed of course).

Are you sure that null ara allowed in the foreing keys you are testing
with (nulls might be allowed at the database level... but that does not
mean they are allowed at the dataset level).
I think you have to check if the "minOccurs" property for the datacolumn
is correctly set. (In this case if the foreign key is not mandatory,
minOccurs should be equal to zero)

 

[tmeynink]

I checked the "minOccurs" attribute and it was in fact set to zero for
the appropriate columns. The schema's generated by VS 2005 are very
different to those from VS 2003.

Any other ideas?

Thanks for your help.
Todd

 

[Luxspes [MCP]]

I checked the "minOccurs" attribute and it was in fact set to zero for
the appropriate columns. The schema's generated by VS 2005 are very
different to those from VS 2003.

Any other ideas?

Thanks for your help.
Todd

Can you copy the exact excepcion message and stack trace?