.Net DbDataAdapter has wrong primary key on filled datatable and i want to correct this

views:

112

answers:

.Net DbDataAdapter has wrong primary key on filled datatable and i want to correct this

Hi all, .NET/MSSQL Server Question:

I use a System.Data.SqlClient.SqlDataAdapter to connect to a database and read (.Fill) a datatable from a view in the database (select * from v_coolview). The problem is that the view consists of multiple tables (of course) and the resulting DataTable has typically a primary key set (Datatable.PrimaryKey) that consists of the wrong column(s).

As the automatic behaviour/algorithms can not just guess the correct PK for the results of a view, i want to specify it.

How can i do this?

I'm not a .NET guy, but I think you need to define the Primary Key of the datatable first (see http://msdn.microsoft.com/en-us/library/z24kefs8(VS.80).aspx) and then fill the table WITHOUT specifying MissingSchemaAction.AddWithKey

Stuart Ainsworth 2009-10-05 18:38:09

Unfortunatly this is not possible, because i need to read the schema fully from the database. the method should work with any view, so it needs to determine the datatable itself. and this is where the trouble come in.

2009-10-08 07:31:33

Well, this is really outside the realm of my knowledge; I'm more of a database guy. We used to be able to trick Access into guessing a priamry key by specifying a Unique Index on an indexed view. You may want to try that and see.

Stuart Ainsworth 2009-10-08 18:45:15

a possible solution, but with some negative side effects: Influences the basic table (disallows inserting data so that view is illegal), and can not applied to views that rely on other views.

2009-10-15 13:35:21

ansaurus

tags:

views:

answers:

.Net DbDataAdapter has wrong primary key on filled datatable and i want to correct this

related questions