So..... I got my DB2 project working to display and edit existing records.



When I try to add a record I always get a conversion error on the primekey field.



I know DB2 doesn't have auto increment fields (does it?) so I have to manually manage the primekeys but I don't even get a chance. I get this error as soon as I hit the add button, or programmatically call BO.add().



I have two projects referencing two different libraries/tables with primekeys of different data types exhibiting the same behavior.



Any ideas? Please see the attached error image, I cant seem to add images to posts anymore.

Keith Chisarik

shameless post bump, I'm stuck at this point

Keith Chisarik

Keith,

Did you set the PrimaryKeyIsAutoIncrementing to False on the BO?  This is probably what is causing your problem.

Yes changing that property to false is what got me past my problem updating existing records, which I can do fine. I just cant add records.

Keith Chisarik

Well, this is happening in the Get of the property so this indicates that something is trying to pull a value.  So two things, look at and/or post the call stack so that we can see where it is coming from.  Also, have you initialized the field in the SetDefaultValues event?  You mentioned that this is happening when you call the Add(), so at this point, the server has never been queried.  So more than likely you are not allowing the fields to be initialized and have not set a value in the SetDefaultValues before something is trying to query the property.

That was it, I didn't set the default value for the primekey field. Your the man.



So then would the process be to always set the primekey field to a default, and then make sure to update it with the "real" primekey before calling save?



I was going to use my old VFP way of tracking primekeys that we used before VFP supported auto increment fields, have a table that has a record for each table in the database that contains a field for the next valid primekey.



I cant believe DB2 doesn't have auto increment fields in 2007.....



Thanks again for your help. So simple once you know......




Keith Chisarik

Well, this is kindof a trick question.  You must have the initialization of the properties turned off for the BO, otherwise this would have at least had a "0" in it when created.  So in this case, you will have to assign a value or manage the NULL value.  In this particular case, I think that I would go ahead and use a NextID table, like you did in VFP, and assign the "real" PK value in the SetDefaultValues of the BO.

gotcha

Keith Chisarik

Just for anyone that might read this later, DB2 does fully support auto-increment fields, our RPG programmer just doesn't use them so he didn't know they existed.

Keith Chisarik

So.........



I have my DB2 table set to now use auto incrementing keys of type BIGINT.



I set the PrimaryKeyIsAutoIncremented property to TRUE on the BO.



I get the following error now:

ERROR [428C9] [IBM][AS] SQL0798N A value cannot be specified for column "PURCH_PK" which is defined as GENERATED ALWAYS. SQLSTATE=428C9



The field is defined as:

PURCH_PK BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 0, INCREMENT BY 1)



I tried adding the PURCH_PK field to the FieldsToExcludeFromInsert collection to no avail:

Cannot create INSERT command because the updating DataTable does not contain columns for all PrimaryKeyFields.



Any ideas why this isn't working? The primekey field does auto-increment if I add records manually using a query window.

Keith Chisarik