Tutorial :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?


Solution:1

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


Solution:2

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  


Solution:3

There's a library, Kailua - The forgotten methods in the ADO.NET API. , that does provide this and additional metadata for the top 5 vendors. This info is vendor specific.


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »