Microsoft KB Archive/817183

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

Article ID: 817183

Article Last Modified on 5/12/2007

-

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

-





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 &quot;Applies to&quot; section.



Steps to reproduce the behavior
 In Microsoft Visual Studio .NET, create a new Console Application by using Visual Basic .NET or Visual C# .NET.  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 = &quot;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Northwind;Data Source=(local)&quot; cn.ConnectionString = strConn Dim strSQL As String = &quot;SELECT O.OrderID, O.EmployeeID FROM Orders O, Customers C WHERE O.CustomerID = C.CustomerID AND C.Country = 'Mexico'&quot;

'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(&quot;PrimaryKey from SqlClient: &quot; & tbl.PrimaryKey(0).ColumnName) Else Console.WriteLine(&quot;No PRIMARY KEY For the table &quot;) End If

'use a OleDbDataAdapter with SQLOLEDB provider Dim daOleDb As New OleDb.OleDbDataAdapter(strSQL, &quot;Provider=SQLOLEDB;&quot; & strConn) tbl.Reset

'configure tbl to match with schema of the source daOleDb.FillSchema(tbl, SchemaType.Source) Console.WriteLine(&quot;PrimaryKey from OleDb: &quot; & 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 = &quot;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Northwind;Data Source=(local)&quot;; SqlConnection cn= new SqlConnection(strConn); string strSQL = &quot;SELECT O.OrderID, O.EmployeeID FROM Orders O, Customers C WHERE O.CustomerID = C.CustomerID AND C.Country = 'Mexico'&quot;;

//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(&quot;PrimaryKey from SqlClient: &quot; + tbl.PrimaryKey[0].ColumnName ); else Console.WriteLine(&quot;No Primary Key for the table&quot;); //use OleDbDataAdapter OleDbDataAdapter daOleDb = new OleDbDataAdapter(strSQL, &quot;Provider=SQLOLEDB;&quot; + strConn); tbl.Reset; //Configure tbl to match with the schema of source daOleDb.FillSchema(tbl, SchemaType.Source); Console.WriteLine(&quot;PrimaryKey from OleDb: &quot; + tbl.PrimaryKey[0].ColumnName); Console.ReadLine; } }   Replace  with the name of your SQL Server. On the Debug menu, click Start to run the application.

