How to pass Table PK to FK Related Table

bobmc · #1 (**permalink**) 06-27-10, 17:52

Hello all..I'm pretty new to all this so after searching through the forums and some Googling I couldn't find an aswer to this that I could really follow.

I have 3 tables, Season, SubSeason, and Teams. Each has an ID column set as the PK. The Subseason has a FK to the SeasonID and the Teams has a FK to both the Season and SubSeason IDs.

My goal is to use a single Web form that will allow the user to input all three Names into their respective tables.

However my issue is that the FK column is not getting updated with the relational Table's ID, hence the form errors out, FKs are set at don't not allow Null value.

I understand how to do the input statement into a single table, but I don't know how write the code to get the FKs to update with the relational table's ID during the submit process of my form.

Any thoughts or suggestes would be great..

Thanks,
Bob

gvee · #2 (**permalink**) 06-27-10, 18:00

What database are you using?1

bobmc · #3 (**permalink**) 06-27-10, 18:07

Sorry about that..I'm using SQL 2008 Express

Thanks,
Bob

gvee · #4 (**permalink**) 06-28-10, 06:02

Code:

BEGIN TRAN

DECLARE @team_id int
      , @season_id int

BEGIN TRY
  INSERT INTO Teams (field_a, field_b, etc)
    OUTPUT inserted.id INTO @team_id
    VALUES ('value_a', 'value_b', 'etc')

  INSERT INTO Season (field_a, field_b, etc)
    OUTPUT inserted.id INTO @season_id
    VALUES ('value_a', 'value_b', 'etc')

  INSERT INTO SubSeason (team_id, season_id)
    VALUES (@team_id, @season_id)

  COMMIT TRAN
END TRY
  BEGIN CATCH
    SELECT Error_Message() As error_message
    ROLLBACK TRAN
  END CATCH

bobmc · #5 (**permalink**) 06-28-10, 14:30

gvee...Thanks for your reply. However I'm not completly following. How does this take the ID of, say, Seasson Table and into into the seasonFKID column in the Teams table when the SessionID column will not exist until after the user clicks the submit button..

Maybe I'm completely off base though..but if wouldn't mind explaining to me a bit on what going on here in your code.

Also I've laid out the Tables for a bit of a better view

Season
SeasonID - PK
SeassonName

SubSeason
SubSeasonID - PK
SubSeasonName
SubSeasonFKID --> SeasonID

Teams
TeamsID - PK
TeamName
SeasonFKID --> SeasonID
SubSeasonFKID --> SubseasonID

Thanks,
Bob

Internet Services

Web Development

Digital Marketing

Consumer Tech