BUG: The FillSchema method may not include schema for the PrimaryKey column for the join query

This article has been archived. It is offered "as is" and will no longer be updated.
When you create a SqlDataAdapter object for a join query that has columns only from one table that is used in the query, the FillSchema method of the SqlDataAdapter object does not include the schema for the PrimaryKey column of the table. This problem does not occur when the join query has columns from both tables.
To work around this problem, add the PrimaryKey column to the table manually. To do this, add the following statement to the code after calling the FillSchema method.

Visual Basic .NET code

' If first column of table is primarykey MyTable.PrimaryKey = New DataColumn() {MyTable.Columns(0)}

Visual C# .NET code

// If first column of table is primarykey MyTable.PrimaryKey =new DataColumn[]{MyTable.Columns[0]};

You can use a OledbDataAdapter object instead of a SqlDataAdaptor to connect to Microsoft SQL Server.
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

Steps to reproduce the behavior

  1. In Microsoft Visual Studio .NET, create a new Console Application by using Visual Basic .NET or Visual C# .NET.
  2. Replace the existing code with the following code:

    Visual Basic .NET code

    Imports SystemImports System.DataImports System.Data.SqlClientImports System.Data.OleDbModule Module1   Sub Main()      'connect to SQlserver      Dim cn As New SqlClient.SqlConnection()      Dim strConn As String = "Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Northwind;Data Source=(local)"      cn.ConnectionString = strConn      Dim strSQL As String = "SELECT O.OrderID,  O.EmployeeID FROM Orders O, Customers C WHERE O.CustomerID = C.CustomerID AND C.Country = 'Mexico'"      'use a SqlDataAdapter       Dim daSql As New SqlClient.SqlDataAdapter(strSQL, cn)      Dim tbl As New DataTable()      'configure tbl to match with schema of the source      daSql.FillSchema(tbl, SchemaType.Source)      'display the Primary Key columnname of the table      If Not (tbl.PrimaryKey.Length = 0) Then         Console.WriteLine("PrimaryKey from SqlClient: " & tbl.PrimaryKey(0).ColumnName)      Else         Console.WriteLine("No PRIMARY KEY For the table ")      End If      'use a OleDbDataAdapter with SQLOLEDB provider      Dim daOleDb As New OleDb.OleDbDataAdapter(strSQL, "Provider=SQLOLEDB;" & strConn)      tbl.Reset()      'configure tbl to match with schema of the source      daOleDb.FillSchema(tbl, SchemaType.Source)      Console.WriteLine("PrimaryKey from OleDb: " & tbl.PrimaryKey(0).ColumnName)      Console.ReadLine()   End SubEnd Module

    Visual C# .NET code

    using System;using System.Data;using System.Data.SqlClient;using System.Data.OleDb;class Class1{		 	  [STAThread]static void Main(string[] args)	{	//connect to your local SQLServer	string strConn = "Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Northwind;Data Source=(local)";	SqlConnection cn= new SqlConnection(strConn);	string strSQL  = "SELECT O.OrderID, O.EmployeeID FROM Orders O, Customers C WHERE O.CustomerID = C.CustomerID AND C.Country = 'Mexico'";	//use SqlDataAdapter	SqlDataAdapter da = new SqlDataAdapter(strSQL, cn);	DataTable tbl= new DataTable();	//configure tbl to match with schema source	da.FillSchema(tbl,SchemaType.Source);	//display the column name of Primary Key	if(tbl.PrimaryKey.Length!=0)				Console.WriteLine("PrimaryKey from SqlClient: " + tbl.PrimaryKey[0].ColumnName );		else			Console.WriteLine("No Primary Key for the table");		//use OleDbDataAdapter	OleDbDataAdapter daOleDb = new OleDbDataAdapter(strSQL, "Provider=SQLOLEDB;" + strConn);	tbl.Reset();		//Configure tbl to match with the schema of source	daOleDb.FillSchema(tbl, SchemaType.Source);	Console.WriteLine("PrimaryKey from OleDb: " + tbl.PrimaryKey[0].ColumnName);	Console.ReadLine();	}}  
  3. Replace (local) with the name of your SQL Server.
  4. On the Debug menu, click Start to run the application.
For more information about the DataAdapter.FillSchema method, see the following Microsoft Developer Network (MSDN) Web site:

Article ID: 817183 - Last Review: 02/27/2014 18:43:29 - Revision: 2.9

Microsoft ADO.NET 2.0, Microsoft ADO.NET 1.1, Microsoft ADO.NET 1.0, Microsoft Visual Basic 2005, Microsoft Visual Basic .NET 2003 Standard Edition, Microsoft Visual Basic .NET 2002 Standard Edition, Microsoft Visual C# 2005 Express Edition, Microsoft Visual C# .NET 2003 Standard Edition, Microsoft Visual C# .NET 2002 Standard Edition

  • kbnosurvey kbarchive kbtshoot kbvs2002sp1sweep kbprovider kbquery kbschema kbtable kbsystemdata kbsqlclient kbdatabase kbdataadapter kbbug KB817183