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

21 Nov 2005 6:41 PM

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

22 Nov 2005 5:21 AM

luxspes

tmeyn***@gmail.com wrote:
Show quote

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

Show quote

>
> What am I missing here?
>
> Thanks,
> Todd
>

22 Nov 2005 7:52 PM

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

23 Nov 2005 2:26 AM

Luxspes [MCP]

tmeyn***@gmail.com wrote:

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