MissingSchemaAction.AddWithKey fails with DISTINCT

24 Feb 2006 12:43 PM

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);
}

26 Feb 2006 10:20 PM

Mary Chipman [MSFT]

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

Show quote

On 24 Feb 2006 04:43:30 -0800, "Howell Page" <howell.p***@gmail.com>
wrote:

>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);
>}