MissingSchemaAction.AddWithKey fails with DISTINCT

  • Thread starter Thread starter Howell Page
  • Start date Start date
H

Howell Page

I'm using a SqlDataAdapter to fill a DataSet with MissingSchemaAction
set to AddWithKey. Normally it discovers the table's primary key
successfully, but when I added DISTINCT to the query, it failed to set
the PK. Is this a known issue, and is there are workaround?

Here's some example code.

String connectionString = "Integrated Security=SSPI;Data
Source=.;Initial Catalog=Northwind;";
String query = "select distinct * from customers";
//String query = "select * from customers";

using( SqlConnection connection = new SqlConnection(connectionString) )
{
SqlDataAdapter adapter = new SqlDataAdapter(query, connection);
DataSet dataSet = new DataSet();
connection.Open();
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
adapter.Fill(dataSet, "customers");
Console.Out.WriteLine(dataSet.Tables["customers"].PrimaryKey.Length);
}
 
Remove DISTINCT from the query string and you won't have a problem.
Well, at least not that problem :-)

DISTINCT is used when you want to eliminate duplicates, so it's
redundant for a table with a PK--if there's a PK, and you're selecting
all the rows, how could there be duplicates?

--Mary
 
Back
Top