datatable not knowing its primary key

Question

I'm trying to query a datatable to establish the primary key [identity column], by querying each columns autoincrement property. However its always false (for the column which is the Idenity/PK).

Querying the tables primary key collection reveals that the datatable doesn't think it has a PK.;

  Dim dc As DataColumn() = dt.PrimaryKey
  Debug.WriteLine(dc.Count)  'Result is 0

The datatable is being populated.......

Using cn As SqlConnection = MyApp.GetConnection
  Using cmd As New SqlCommand(strSQL, cn)
    Using da As New SqlDataAdapter(cmd)
      Dim ds As New DataSet
      Try
        da.Fill(ds)

        Return ds

      Catch ex As Exception
        MyAppClass.LogWarning(ex, EventLogEntryType.Error)
        Throw
      End Try
    End Using 
  End Using 
End Using

The table in question's primary key is : ([myTableId] [int] IDENTITY(1,1) NOT NULL). and its the pk : CONSTRAINT [PK_myTablesPK] PRIMARY KEY CLUSTERED ( [myTableId] ASC )

Here's someone having the same problem (perhaps its clearer than what i've written) : http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataproviders/thread/c6abdeef-0cb0-42f5-a5f1-10dc4d81df4a/

I'm assuming its something simple i'm missing, does anyone care to enlighten me?

Answer 1

Do you need to determine the primary key from the autoincrement property? or could this help you?

Private Sub GetPrimaryKeys ( myTable As DataTable )
   ' create the array for the columns.
   Dim colKeys ( ) As DataColumn = myTable.PrimaryKey
   ' get the number of elements in the array.
   Response.Write ( "Column Count: " & colKeys.Length.ToString ( ) )
   Dim i As Integer
   For i = 0 To colKeys.GetUpperBound ( 0 )
      Response.Write ( colKeys ( i ).ColumnName & _
         colKeys ( i ).DataType.ToString ( ) )
   Next i
End Sub

Answer 2

Using fillschema fixes my issue;

da.FillSchema(ds, SchemaType.Mapped, table.tableName)
da.Fill(ds, table.tableName)

"

The DataAdapter object is optimized for read-only scenarios by default. The Fill method only retrieves the amount of schema that is necessary to populate a DataSet object. To obtain the additional schema that are necessary to update or validate DataSet objects, use one of the following methods for DataSet objects that are populated by the DataAdapater:

* Use the FillSchema method of the DataAdapter.
* Use the AddWithKey enumeration for the MissingSchemaAction property

of the DataAdapter.

This article describes how to choose between these two methods when you want to populate updateable DataSet objects with the DataAdapter.

"

REF : http://support.microsoft.com/kb/310128