5

I am filling a datatable object through a dataadapter pulling data from a MS-SQL database. For some reason the wrong column is set as the primary key on the datatable. I have tried FillSchema and MissingSchemaAction.AddWithKey to no avail.

The correct primary key is defined properly in the SQL server DB schema & is backed by a non clustered unique index PK_x.

The column selected as the primary key by the dataadapter is a unique column backed by a clustered unique index.

These are the only two columns in the table.

How does DataAdapter retrieve the table schema from SQL? I have interrogated the sys tables (sys.tables, sys.index_columns, sys.indexes) and they indicate the proper primary key schema. What gives?

LarsTech
  • 80,625
  • 14
  • 153
  • 225
Drew R
  • 2,988
  • 3
  • 19
  • 27
  • 3
    You're going to have to post your code for us to help. – Mike Perrenoud Sep 18 '12 at 16:22
  • Have you used DataAdpater.FillSchema(dataset, SchemaType.Source, "Table") ? – Joshua Van Hoesen Sep 18 '12 at 17:13
  • I have tried calling FillSchema in that way. @Mike I am calling instanceOfDataAdapter.Fill(instanceOfDataTable); where the data adapter has been given a SQL string to retrieve the rows. The result, regardless of the use of the methods mentioned above is the same; the second of the two columns is selected as a primary key for the data table. The first column is explicitly defined as the primary key of the SQL table. Though both are constrained to be unique (effectively a composite key), only the first column is defined as the primary key. – Drew R Sep 18 '12 at 18:48
  • 1
    And what does the SQL statement look like? It would really be beneficial if you could just drop in a code snippet. The reason for that is because it gives us context to understand what's being sent to the database and what .NET is being forced to interpret. – Mike Perrenoud Sep 18 '12 at 19:04
  • @Mike The select command is just `select * from tableName` I'm then calling `sourceAdapter.Fill(sourceTable);` – Drew R Sep 19 '12 at 08:24
  • 2
    And the PK is a *single column* in the database? What's the database schema look like? Maybe you could post that since it's only a couple of columns. – Mike Perrenoud Jan 10 '13 at 18:41
  • 2
    Have you tried running a profiler trace on the db during the call? What if you did CommandType = CommandType.TableDirect; CommandText="TableName"; then Fill? – mrtig Aug 19 '13 at 18:14

1 Answers1

1

"If a unique clustered index is defined on a column or columns in a SQL Server table and the primary key constraint is defined on a separate set of columns, then the names of the columns in the clustered index will be returned. To return the name or names of the primary key columns, use a query hint with the SELECT statement that specifies the name of the primary key index. For more information about specifying query hints, see Query Hint (Transact-SQL)."

http://msdn.microsoft.com/EN-US/library/152bda9x(v=VS.110,d=hv.2).aspx

C.B.
  • 666
  • 3
  • 18
IT63
  • 121
  • 3