Filling Dataset with Primary Key, DB2


This is my first time posting, but I have been programming for a long time.  

I am connecting to a IBM DB2 database and retrieving rows to a Dataset.  I need to get primary key information.  I have tried using FillSchema and also using MissingSchemaAction.AddWithKey and neither of these worked.  It still comes back with no primary key information.  I am sure that the primary keys exist.  

Is there a problem retrieving schema information from DB2 databases?  

Thank you
(BTW - I am using Ole adapters)
Who is Participating?
RomModConnect With a Mentor Commented:
The question has been PAQ'd and the 125 points have been refunded.
Community Support Moderator
what do you mean by "I need to get primary key information" - do you need to be able to identify which field(s) is(are) the Primary Key?

ArvarisAuthor Commented:
Yes, I simply need to identify which fields are primary key fields.
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

try this example

Create Sample That Lists Primary Keys in a Table
The following sample lists the primary keys of the Employees table in the SQL Server Northwind database
and of the Employee table in the SQL Server Pubs database.

OleDbSchemaGuid.Primary_Keys returns those primary keys in a catalog that are accessible to a given log on.
 In this sample, the OleDbConnection is to SQL Server but not to a specific SQL Server database:

 cn.ConnectionString = "Provider=SQLOLEDB;Data Source=server;User ID=<username>;Password=<strong password>;"
Instead, this code specifies the Northwind or Pubs databases as the TABLE_CATALOG in the restriction array.
 This code specifies the table owner, "dbo", for the TABLE_SCHEMA restriction. In addition, this code
specifies the table names for the TABLE_NAME restriction.

To obtain the primary key of the Employees table in the Northwind database, you use an Object array of {"Northwind", "dbo", "Employees"}: schemaTable = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Primary_Keys, _
              New Object() {"Northwind", "dbo", "Employees"})
To obtain the primary key of the Employee table in the Pubs database, you use an Object array
of {"Pubs", "dbo", "Employee"}:
schemaTable = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Primary_Keys, _
              New Object() {"Pubs", "dbo", "Employee"})
To create the sample, follow these steps:
Create a new Visual Basic Console Application project. Module1.vb is added to the project by default.
Open the Code window for Module1. Paste the following code into the top of the Code window, above
the Module declaration:

   Imports System.Data
   Imports System.Data.OleDb
In the Code window, paste the following code into the Sub Main procedure.

Note You must change User ID <username> and password =<strong password> to the correct values before
you run this code. Make sure that User ID has the appropriate permissions to perform this operation on the

        Dim cn As New OleDbConnection()
        Dim schemaTable As DataTable

        'Connect to SQL Server.
        'Be sure to use an account that has permissions to list primary keys
        'in both the Northwind and Pubs databases.
        cn.ConnectionString = "Provider=SQLOLEDB;Data Source=server;User ID=<username>;Password=<strong password>;"

        'Retrieve schema information about primary keys.
        'Restrict to just the Employees TABLE in the Northwind CATALOG.
        schemaTable = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Primary_Keys, _
                      New Object() {"Northwind", "dbo", "Employees"})

        'List the primary key for the first row in the schema table.
        'The first three Items (zero-based) in the row are catalog, schema, and table.
        'The fourth item is the primary key.

       'Retrieve primary key for the Employee TABLE in the Pubs CATALOG.
       schemaTable = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Primary_Keys, _
                      New Object() {"Pubs", "dbo", "Employee"})

        'List the primary key for the first row in the schema table.

        'Explicitly close - don't wait on garbage collection.

Modify the ConnectionString to connect to your SQL Server computer with an account that has sufficient
permissions to list the primary keys.
ArvarisAuthor Commented:
After looking into trying this example, I realized that this only works with SqlServer.  I am using a DB2 database and so this does not work.  I will however try and make sure that my username that i am connecting to the database with has the neccessary writes to list primary keys.
I don't know your needs, but you can alwaay s add primary key to your datatable yourself.
I found another hint . Maybe it will help you

set the following property on your data adapter instance:

da.MissingSchemaAction = MissingSchemaAction.AddWithkey
Sorry. Just noticed that you already tried it
ArvarisAuthor Commented:
Well, thank you all for your help, but it seems there was only one way to accomplish this...and here's how:

I had to select from the system table that holds the primary keys and manually add these columns as my primary keys...


This selects all primary key columns for a table in a given schema.  I then execute a reader that loops through and adds the columns from the dataset with these column names to the primary key.

        Dim dc_pk As New ArrayList
        Dim arrStrPK() As String = PK_DataBind()
        For Each strPK As String In arrStrPK
        Dv.Table.PrimaryKey = dc_pk.ToArray(GetType(DataColumn))

I think there may be an issue with getting primary keys from DB2 databases, so this is the only work around I could think of.  Again, thank you guys for trying to help.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.