Data Adapter Wiz Prob

[Merrill Aldrich]

Dumb question: I have made a zillion data adapters in VS, but this morning the data adapter wizard suddenly complains that it "Could not determine which columns uniquely identify the rows" for a table that has a primary key, and which primary key is in the select statement. This is frustrating. The wizard does work as expected for other tables in the same DB, which have the same general design. The problem table def is:

CREATE TABLE [dbo].[vendorsImport] (
[vendCode] [char] (8) NOT NULL ,
[vendName] [varchar] (40) NULL ,
[taxid] [varchar] (12) NULL ,
[addr1] [varchar] (50) NULL ,
[addr2] [varchar] (50) NULL ,
[city] [varchar] (40) NULL ,
[state] [char] (2) NULL ,
[zipcode] [varchar] (12) NULL ,
[phone] [varchar] (20) NULL ,
[fax] [varchar] (20) NULL ,
[varchar] (80) NULL ,
[website] [varchar] (160) NULL ,
[contact] [varchar] (20) NULL ,
[forAllProps] [bit] NOT NULL ,
[certForAllProps] [varchar] (50) NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[vendorsImport] ADD
CONSTRAINT [PK_vendorsImport] [B]PRIMARY KEY[/B] CLUSTERED
(
[vendCode]
) ON [PRIMARY]
GO

and I am using all the columns in the data adapter.

Fiddling with it, I find the problem to be that the primary key was added after the table creation (which is perfectly legal). (The reason is that the table was created by using Select ... into to copy an exising table and then the key added afterward.)

This create script makes the same table, but now VS is happy:

CREATE TABLE [dbo].[vendorsImport] (
[vendCode] [char] (8) NOT NULL [B]primary key[/B] ,
[vendName] [varchar] (40) NULL ,
[taxid] [varchar] (12) NULL ,
[addr1] [varchar] (50) NULL ,
[addr2] [varchar] (50) NULL ,
[city] [varchar] (40) NULL ,
[state] [char] (2) NULL ,
[zipcode] [varchar] (12) NULL ,
[phone] [varchar] (20) NULL ,
[fax] [varchar] (20) NULL ,
[email] [varchar] (80) NULL ,
[website] [varchar] (160) NULL ,
[contact] [varchar] (20) NULL ,
[forAllProps] [bit] NOT NULL ,
[certForAllProps] [varchar] (50) NULL
) ON [PRIMARY]
GO

Has anyone encountered this? Is it a bug in VS or is there some subtle thing I am not understanding?

 

[DavidT_macktool]

What is your purpose of defining the primary key as CLUSTERED. If you can get away without that statement I think it will solve your problem. I had to do away with the CLUSTERED definition on my tables to get certain things to work.

 

[Merrill Aldrich]

Thanks for your reply David -- I'm afraid Clustering is not the issue, as all my other tables are clustered. In SQL Server there are preformance and storage advantages to defining all, or nearly all, tables with a clustered index. (To be honest, my expertise is SQL Server, less in VB .NET)