SqlDataAdapter.FillSchema

[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.fkColo rID

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

[Smokey Grindel]

Re: SqlDataAdapter. FillSchema

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


"Aaron" <odysseus183@ho tmail.comwrote in message
news:1155854003 .796724.118490@ 74g2000cwt.goog legroups.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.fkColo rID
>
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
>

[Cor Ligthert [MVP]]

Re: SqlDataAdapter. FillSchema

Aaron,

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

Cor

"Aaron" <odysseus183@ho tmail.comschree f in bericht
news:1155854003 .796724.118490@ 74g2000cwt.goog legroups.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.fkColo rID
>
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
>

[Aaron]

Re: SqlDataAdapter. FillSchema

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