Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Filling Dataset with Primary Key, DB2

Posted on 2004-09-27
11
Medium Priority
?
663 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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

IP addresses can be stored in a database in any of several ways.  These ways may vary based on the volume of the data.  I was dealing with quite a large amount of data for user authentication purpose, and needed a way to minimize the storage.   …
More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

636 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