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

SYMPTOMS

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.

WORKAROUND

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]};
-or-

You can use a OledbDataAdapter object instead of a SqlDataAdaptor to connect to Microsoft SQL Server.

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

MORE INFORMATION

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)
          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 Sub
    
    End 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.

REFERENCES

For more information about the DataAdapter.FillSchema method, see the following Microsoft Developer Network (MSDN) Web site:
http://msdn2.microsoft.com/en-us/library/system.data.common.dataadapter.fillschema(vs.71).aspx

Properties

Article ID: 817183 - Last Review: February 27, 2014 - Revision: 2.9
APPLIES TO
  • 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
Keywords: 
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