SqlDataAdapter.FillSchema

17 Aug 2006 10:33 PM

Aaron

Hi,

The SqlDataAdapter.FillSchema method will automatically setup things
like the primary keys, auto increment, etc based on the settings on the
SQL Server. However, if you create a datatable using the SQL statement
that joins together data from multiple data tables, then FillSchema
does not work (as much? at all??). For example, if I use the following
query:

SELECT     Material.*, Color.Color AS Color
FROM         Material INNER JOIN
                      Color ON Color.ColorID = Material.fkColorID

FillSchema does not obtain MaterialID, the primary key of the Material
table, as the primary key, even though its uniqueness is not affected
at all by the JOIN with Color.

It is, of course, possible to manually configure primary keys.
However, this is not as desirable as the automated FillSchema approach.
Is this just the way things are, or is there some trick that I am
missing?

Thanks,
Aaron

18 Aug 2006 1:58 AM

Smokey Grindel

that is because after a join there is no longer a "primary key" on the
table... you'd have the same problem on the server side too in SQL server...
when you alter the DML through the join it ignores keys...

Show quoteHide quote

"Aaron" <odysseus***@hotmail.com> wrote in message
news:1155854003.796724.118490@74g2000cwt.googlegroups.com...
> Hi,
>
> The SqlDataAdapter.FillSchema method will automatically setup things
> like the primary keys, auto increment, etc based on the settings on the
> SQL Server. However, if you create a datatable using the SQL statement
> that joins together data from multiple data tables, then FillSchema
> does not work (as much? at all??). For example, if I use the following
> query:
>
> SELECT     Material.*, Color.Color AS Color
> FROM         Material INNER JOIN
>                      Color ON Color.ColorID = Material.fkColorID
>
> FillSchema does not obtain MaterialID, the primary key of the Material
> table, as the primary key, even though its uniqueness is not affected
> at all by the JOIN with Color.
>
> It is, of course, possible to manually configure primary keys.
> However, this is not as desirable as the automated FillSchema approach.
> Is this just the way things are, or is there some trick that I am
> missing?
>
> Thanks,
> Aaron
>

18 Aug 2006 5:47 AM

Cor Ligthert [MVP]

Aaron,

You be aware that AFAIK a joined table not meant to be updated using that.

Cor

Show quoteHide quote

"Aaron" <odysseus***@hotmail.com> schreef in bericht
news:1155854003.796724.118490@74g2000cwt.googlegroups.com...
> Hi,
>
> The SqlDataAdapter.FillSchema method will automatically setup things
> like the primary keys, auto increment, etc based on the settings on the
> SQL Server. However, if you create a datatable using the SQL statement
> that joins together data from multiple data tables, then FillSchema
> does not work (as much? at all??). For example, if I use the following
> query:
>
> SELECT     Material.*, Color.Color AS Color
> FROM         Material INNER JOIN
>                      Color ON Color.ColorID = Material.fkColorID
>
> FillSchema does not obtain MaterialID, the primary key of the Material
> table, as the primary key, even though its uniqueness is not affected
> at all by the JOIN with Color.
>
> It is, of course, possible to manually configure primary keys.
> However, this is not as desirable as the automated FillSchema approach.
> Is this just the way things are, or is there some trick that I am
> missing?
>
> Thanks,
> Aaron
>

19 Aug 2006 8:20 AM

Aaron

Interesting (although mostly useless) loophole:

If a tables primary keys are also the foreign key columns (used to form
the joins) then the primary keys will populate correctly. I discovered
this on accident. Weird, huh?

I am also still on VS 2003.

Aaron