?
Solved

Filling Dataset with Primary Key, DB2

Posted on 2004-09-27
11
Medium Priority
?
662 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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
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

Congratulations! You’re Certified – Now What?

Starting a new career can be overwhelming. Becoming certified in your field of expertise is a great start, but where do you go from here?  Here are some tips to help you on your career journey.

Question has a verified solution.

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

The object model of .Net can be overwhelming at times – so overwhelming that quite trivial tasks often take hours of research. In this case, the task at hand was to populate the datagrid from SQL Server database in Visual Studio 2008 Windows applica…
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…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
Suggested Courses

770 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