Solved

Filling Dataset with Primary Key, DB2

Posted on 2004-09-27
11
657 Views
Last Modified: 2009-07-29
Hello,

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)
0
Comment
Question by:Arvaris
11 Comments
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 12160349
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?

AW
0
 
LVL 2

Author Comment

by:Arvaris
ID: 12160452
Yes, I simply need to identify which fields are primary key fields.
0
 
LVL 28

Expert Comment

by:iboutchkine
ID: 12161251
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
database.

        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>;"
        cn.Open()

        '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.
        Console.WriteLine(schemaTable.Rows(0).Item(3).ToString)

       '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.
        Console.WriteLine(schemaTable.Rows(0).Item(3).ToString)

        'Explicitly close - don't wait on garbage collection.
        cn.Close()

        'Pause
        Console.ReadLine()
                              
Modify the ConnectionString to connect to your SQL Server computer with an account that has sufficient
permissions to list the primary keys.
0
 
LVL 2

Author Comment

by:Arvaris
ID: 12162282
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.
0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 28

Expert Comment

by:iboutchkine
ID: 12162446
I don't know your needs, but you can alwaay s add primary key to your datatable yourself.
0
 
LVL 28

Expert Comment

by:iboutchkine
ID: 12162475
I found another hint . Maybe it will help you

set the following property on your data adapter instance:

da.MissingSchemaAction = MissingSchemaAction.AddWithkey
0
 
LVL 28

Expert Comment

by:iboutchkine
ID: 12162480
Sorry. Just noticed that you already tried it
0
 
LVL 2

Author Comment

by:Arvaris
ID: 12162538
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...

        Select COLNAME from SYSIBM.SYSKEYCOLUSE where TBCREATER='Schema' AND TBNAME='Table'

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
            dc_pk.Add(Dv.Table.Columns(strPK))
        Next
        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.
0
 

Accepted Solution

by:
RomMod earned 0 total points
ID: 12414477
The question has been PAQ'd and the 125 points have been refunded.
RomMod
Community Support Moderator
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Welcome my friends to the second instalment and follow-up to our Minify and Concatenate Your Scripts and Stylesheets (http://www.experts-exchange.com/Programming/Languages/.NET/ASP.NET/A_4334-Minify-and-Concatenate-Your-Scripts-and-Stylesheets.html)…
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…
This is a video describing the growing solar energy use in Utah. This is a topic that greatly interests me and so I decided to produce a video about it.

919 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now