Question: FillSchema on joined table in ADO.NET

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Question: FillSchema on joined table in ADO.NET

Author

Topic

groston
Yak Posting Veteran

61 Posts

Posted - 2004-12-01 : 17:55:18

In my database, I have the following two tables

CREATE TABLE [dbo].[Client] (
[ClientID] [int] IDENTITY (1, 1) NOT NULL ,
[FName] [nvarchar] (20) ,
[LName] [nvarchar] (30) ,
[UserName] [nvarchar] (50)
)

CREATE TABLE [dbo].[ClientOrg] (
[ClientOrgID] [int] IDENTITY (1, 1) NOT NULL ,
[ClientID] [int] NULL ,
[OrgID] [int] NULL ,
[RoleID] [int] NULL
)

When I create the web page, I do the following:

Dim daClientOrg As New SqlDataAdapter("ClientOrg_Select", myDB)
daClientOrg.SelectCommand.CommandType = CommandType.StoredProcedure
daClientOrg.FillSchema(dsClient, SchemaType.Mapped, "ClientOrg")
daClientOrg.Fill(dsClient, "ClientOrg")

The stored procedure is quite complex

:

SELECT ClientOrg.*, LName + ', ' + FName as DisplayName
FROM ClientOrg, Client
WHERE ClientOrg.ClientID = Client.ClientID

Here is the problem: The FillSchema command does get the column names and types, but it does not set ClientOrgID as the PrimaryKey. The only way I know to get around this is to specifically add code to the app - set the AutoIncrement (etc) for the ClientOrgID column and make it the PrimaryKey.

This bothers me - it seems like wasted code because the system should know these column properties. Is there an elegant whay to resolve this rpoblem or am I stuck writing four lines of code every time my SP returns values from more than one table?

Thank you!

p.s. If the SP returned, for example, both ClientID and ClientOrgID, I would understand why it could not pick one as being the PK. However, in this case, it only returns one of the two.

p.p.s. Yes, one could get rid of ClientOrgID and make both ClientID and OrgID PK columns. I have reasons for not using this approach. Also, I do not believe that this would resolve the issue at hand.

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2004-12-01 : 18:49:13

Umm... how about making the ClientOrgID a primary key in the first place?
Having a column in a SQL Server table with the "Identity" property does NOT make it a primary key. Add the key phrase "PRIMARY KEY" in the SQL DDL and try again.

DavidM

"Always pre-heat the oven"

groston
Yak Posting Veteran

61 Posts

Posted - 2004-12-01 : 21:25:05

quote:
Originally posted by byrmol

Umm... how about making the ClientOrgID a primary key in the first place?

Quite interesting - this must be a bug in SQL Server! I generated the table information using "All Tasks - > Generate SQL Script" and this was the result. Looking in Enterprise Manager, the column is clearly marked as a PK. Also, looking at the various sys* tables, it is clear that this column is indeed a PK.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-12-01 : 21:37:32

:) Then you clearly didn't run the DDL you posted. I think it's a carbon-based bug.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

groston
Yak Posting Veteran

61 Posts

Posted - 2004-12-01 : 21:51:40

I sincerely hate to argue with those who certainly know more than I, but I just tried this again (same DB, different computer) and the result is the same. Honest!

[p.s. This is getting off topic - I really need an answer to my original question. However, I will digress...

On a XP machine running MSDE (SP3a) with SQL client tools installed:
-Drill down to DB
-Expand tables
-Right click on table ClientOrg and select "design"
-PK symbol shows up by column ClientOrgID
-Close this window
-Right click again, chose "All Tasks - > Generate SQL Script"
-Click the preview button
-Wait longer than seems to make sense
-View results - no 'PRIMARY KEY' label shows in script

kinda makes you go hmmm.]

Any chance we can now address my real question

meancoder
Starting Member

1 Post

Posted - 2004-12-14 : 01:22:51

Generate SQL Scripts has a bug
it leaves off Primary Key indicator.

When you rerun the statement on another Database you will not get the primary key indicator.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources