StrataFrame Forum » StrataFrame Application Framework - V1 » Business Objects and Data Access (How do I?) » Multiple Primary Key Fields

Multiple Primary Key Fields

Bill Cunnien — Wed, 18 Jun 2008 09:28:52 GMT

I am attempting to insert a record into a table with multiple primary key fields. The SF Business Object has the following properties set:

PrimaryKeyIsAutoIncremented = False
PrimaryKeyIsUpdateable = True

When I run my code, the error generated tells me:

Cannot insert explicit value for identity column in table 'MyTable' when IDENTITY_INSERT is set to OFF.

Dude...wudzupwiddat?
Bill

RE: Multiple Primary Key Fields

Edhy Rijo — Wed, 18 Jun 2008 09:28:52 GMT

[quote][b]Bill Cunnien (06/18/2008)[/b][hr]Hey Edhy,

Related? Nope. Right on!!! That is exactly what I needed. :w00t: I have made the necessary adjustments to my BOs and am now testing the procedure. So far, the results are good...very good. Man, am I glad that I did not have to rewrite any of this in TSQL.

Thanks!
Bill[/quote]

Hi Bill,

I am glad that was the answer. I was following this thread, but somehow got a bit lost with your situation, then with your last post, I saw the light :hehe:

RE: Multiple Primary Key Fields

Trent L. Taylor — Wed, 18 Jun 2008 09:25:03 GMT

Good answer, Edhy!

RE: Multiple Primary Key Fields

Bill Cunnien — Wed, 18 Jun 2008 09:13:46 GMT

Hey Edhy,

Thanks!
Bill

RE: Multiple Primary Key Fields

Edhy Rijo — Wed, 18 Jun 2008 08:36:16 GMT

Hi Bill,

Please check out this post: http://forum.strataframe.net/Topic17032-6-1.aspx?Highlight=BOM

I believe this may be related to your needs.

RE: Multiple Primary Key Fields

Bill Cunnien — Wed, 18 Jun 2008 07:56:44 GMT

[quote]I will need to resort to a stored procedure to insert these records.[/quote]

Btw, I really hope not. This is a really complex procedure and I have about 400 lines of code invested with several new business objects handling all of the heavy lifting. To relocate all of this logic (much of it is business logic and fits perfectly into the business object environment) to a stored procedure will be a nightmare. And, to explain this all to my manager will be even worse.

So, my hope is that SF can come through for me. Can you?

:unsure:
Bill

RE: Multiple Primary Key Fields

Bill Cunnien — Wed, 18 Jun 2008 07:39:13 GMT

Here is what just popped out...on the first table that I am updating, there are two primary key fields. As I mentioned, neither of these are identity columns. As I searched through the table, I discovered that there is indeed an identity column. It is indexed, but not part of the primary key for the table. I know that the SF framework is quite flexible and that you guys have thought of just about everything. Was this thought of and how do I handle it in the BO? My thinking is that I will need to resort to a stored procedure to insert these records.

Thanks for all of your help!
Bill

RE: Multiple Primary Key Fields

Bill Cunnien — Wed, 18 Jun 2008 07:15:21 GMT

[quote]The first thing that I would look at would be INSERT_IDENTITY[/quote]

Peter and Trent,

Thanks. I actually love the BOL. When this error came up I read the same entry that you mentioned, Peter. It does provide decent insight into what SQL Server is doing when manipulating the identity columns manually; however, I am not really doing that. I am inserting a record into a table that has multiple primary keys. None of the keys are identity columns. So, I think (I know...dangerous, at times) that this may be a setting on the BO somewhere. I will do a little more checking, now that I had a good night's sleep. I am sure something will pop out at me.

Bill

RE: Multiple Primary Key Fields

Trent L. Taylor — Tue, 17 Jun 2008 20:00:13 GMT

[quote]Dude...wudzupwiddat?[/quote]

LOL....I needed a good laugh :) I like your attitude! I think that Pete gave you excellent advice. Since your PK is not being auto-incremented then you will have to specify the PK value yourself (like you needed me to tell you that part :hehe: ) if it is not being set on the SQL side (i.e. default value, trigger, etc.).

The first thing that I would look at would be INSERT_IDENTITY to see if you have an issue on that side. if you get stuck, then give me some more details and we can go to the next step ;)

RE: Multiple Primary Key Fields

Peter Jones — Tue, 17 Jun 2008 17:07:31 GMT

Hi Bill,

This looks like an SQL error - check the BOL for IDENTITY_INSERT:

"At any time, only one table in a session can have the IDENTITY_INSERT property set to ON. If a table already has this property set to ON, and a SET IDENTITY_INSERT ON statement is issued for another table, SQL Server 2005 returns an error message that states SET IDENTITY_INSERT is already ON and reports the table it is set ON for.

If the value inserted is larger than the current identity value for the table, SQL Server automatically uses the new inserted value as the current identity value.

The setting of SET IDENTITY_INSERT is set at execute or run time and not at parse time."

Cheers, Peter