I have a table where I want to FK to the table's Primary key (so as to create parent/child relationships among the rows). The problem I have is getting the PK value on insert. I do an DataRepository.MyObjectProvider.Insert(TransactionManager, o_myobject) but obviously get an error when the FK value doesn't already exist in the table. I searche the list on how to enable this but come up short. I really don't want to remove the FK restraint on this column and the do TWO hits on the DB one to insert and then one to assign the PK value to the child row FK?
TransactionManager
Brad,
I’m a little confused. Are you tryingto have the record reference itself (ie. ThreadStarter = Id)? In your example,where does o_comment come from?
Ben Johnson------------------------------ Member of the .NetTiers team Visit http://www.nettiers.com------------------------------
ThreadStarter = Id (Yes exactly)
Sorry, I was trying to genericize my sample syntax and forgot to edit the explination as well. Disreagrd o_comment (should be o_myobject);
I also edited the orginial post to correct my imposed confusion.
Is you Id an identity field? If so, I don't know how this could work. Id wouldn't get a value until the record is saved to the database. How would you do this using normal T-SQL? You would have to do two statements (one for the insert followed by an update once the id get's a value), wouldn't you? You could probably set up a trigger where you could set ThreadStarter to null and then have the trigger set the value to the Id.
Awesome.. Thanks. Yes this is a PK identity filed. You saved me from stabbing away at it to no avail. Makes total sense and yes in a straight T-SQL situation I would have to allow null on the column and then update subsequent to identity generation with insert. I like the trigger idea.
-Brad