Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2010
    Posts
    21

    Unanswered: How to pass Table PK to FK Related Table

    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

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    What database are you using?1
    George
    Home | Blog

  3. #3
    Join Date
    Jun 2010
    Posts
    21
    Sorry about that..I'm using SQL 2008 Express

    Thanks,
    Bob

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    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
    George
    Home | Blog

  5. #5
    Join Date
    Jun 2010
    Posts
    21
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •