Solved

Filling Dataset with Primary Key, DB2

Posted on 2004-09-27
11
661 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
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
 
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

MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

Question has a verified solution.

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

This document covers how to connect to SQL Server and browse its contents.  It is meant for those new to Visual Studio and/or working with Microsoft SQL Server.  It is not a guide to building SQL Server database connections in your code.  This is mo…
Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

726 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