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

Article translations Article translations
Article ID: 817183 - View products that this article applies to.
This article has been archived. It is offered "as is" and will no longer be updated.
Expand all | Collapse all

On This Page


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 System
    Imports System.Data
    Imports System.Data.SqlClient
    Imports System.Data.OleDb
    Module 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)
             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)
          'configure tbl to match with schema of the source
          daOleDb.FillSchema(tbl, SchemaType.Source)
          Console.WriteLine("PrimaryKey from OleDb: " & tbl.PrimaryKey(0).ColumnName)
       End Sub
    End Module

    Visual C# .NET code

    using System;
    using System.Data;
    using System.Data.SqlClient;
    using System.Data.OleDb;
    class Class1
    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
    	//display the column name of Primary Key
    			Console.WriteLine("PrimaryKey from SqlClient: " + tbl.PrimaryKey[0].ColumnName );
    			Console.WriteLine("No Primary Key for the table");
    	//use OleDbDataAdapter
    	OleDbDataAdapter daOleDb = new OleDbDataAdapter(strSQL, "Provider=SQLOLEDB;" + strConn);
    	//Configure tbl to match with the schema of source
    	daOleDb.FillSchema(tbl, SchemaType.Source);
    	Console.WriteLine("PrimaryKey from OleDb: " + tbl.PrimaryKey[0].ColumnName);
  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: February 27, 2014 - 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

Give Feedback


Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com