Thanks Peter, I had finally discovered that on my own, just took me a while






Keith Chisarik

Yes, DB2 can auto-generate primary keys. Here's a CREATE TABLE statement with an auto-generated key field:







I think you can reset the starting number with a table alter, but I've not had any requirement to do that yet, so I'm not sure what the SQL is. Here's the DB2 v9 SQL reference, though. It should (hopefully) have the information in there.



http://publibfp.boulder.ibm.com/epubs/pdf/dsnsqk10.pdf



Peter

Just to close this out, I am up and running on DataDirect's DB2 Provider for ADO.NET with the SF framework, just the changes Ben mentioned above and a syntax change to the INSERT SQL statement was required.



Thank you.

Keith Chisarik

Yes, the Db2DataSourceItem can be used with any ADO.NET provider that implements the System.Data.Common classes and provides a DbProviderFactory class.  However, when you swap to a new ADO.NET provider, you will need to swap the places where you pass Db2Commands to the FillDataTable() methods of the business object to use the new provider as well.

My client is not happy with the performance of the IBM DB2 Provider for .NET.



As I understand iy SF DB@ support is designed to work with the IBM Provider IBM.DATA.DB2.dll and wont work with other third party providers, is this correct?



They want to use this.



http://www.hitsw.com/products_services/sql400/dlsql400.html

http://www.datadirect.com/products/net/net_for_db2/index.ssp

Keith Chisarik

Can you send me the create script for the table, the .vb file and the .designer.vb file for the business object/table that is causing the problems?  Also send me the stack trace for the error that you're receiving after you got everything back to ground-zero.  I think I'm going to have to run it on this end to see what it's doing.

Ben, I did set it back to TRUE, I am 100% sure of it as I thought that was the problem from the start once I got my database auto-incrementing and tested it a few times over before posting.



Thanks.

Keith Chisarik

Looks like it's trying to insert a value into the PK field because you might still have the PrimaryKeyIsAutoIncremented = False set.  Set it back to its default of True and you should be able to save it fine.  The Db2DataSourceItem is designed to work with auto-incremented columns the same way as SQL Server, it just uses a different server function to retrieve the next assigned value (where SQL Server uses SCOPE_IDENTITY()).  So, it should be working.  Re-set that field back to True to get back to ground-zero for this post, where you got the InvalidCastException and we'll go from there. 

No problem, thanks.



Enjoy poking around with DB2




Keith Chisarik

DB2 is not something I work in every day so before I can just throw an answer out there like I can for SQL Server or some of the other databases.  I will have to get it setup and play with it on my side.    I have not dug into the DB2 server to play with this...yet.  But we will look into it.  Sorry for the delay