Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2007
    Posts
    4

    Unanswered: Inserting to multiple tables in SQL Server 2005 that use identity specification

    Hi, I am having a bit of hassle with trying to enter details to multiple tables in SQL Server 2005.
    I have four tables, an
    1. Attendance Table (AttendanceID(PK Identity specific), MembershipNo, Date)
    2. Resistance Table (ResistId(PK Identity specific), Weight , Reps, Sets)
    3. Aerobics Tables(AerobicsID(PK Identity specific), MachineID, Intensity, Time)
    4. and a linking table for all of them.... ExerciseMaster(AttendanceID,ResistanceID,AerobicsI D)

    My problem is that I can insert data to each specific table by itself using seperate insert statements.....eg....

    //insert an attendance record to the attendance table
    string userID;

    userID = Session["User"].ToString();

    SqlDataSource pgpDataSource = new SqlDataSource();
    pgpDataSource.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionStringLogin"].ToString();

    pgpDataSource.InsertCommandType = SqlDataSourceCommandType.Text;
    pgpDataSource.InsertCommand = "INSERT INTO [Attendance] ([MembershipNo], [Date]) VALUES (@MembershipNo, @Date)";
    pgpDataSource.InsertParameters.Add("MembershipNo", userID);
    pgpDataSource.InsertParameters.Add("Date", txtVisitDate.Text);

    int RowsAffected = 0;

    try
    {
    RowsAffected = pgpDataSource.Insert();
    }

    catch (Exception ex)
    {
    Server.Transfer("~/Problem.aspx");
    }

    finally
    {
    pgpDataSource = null;
    }


    //insert an aerobics record into the aerocibs table

    SqlDataSource pgpDataSource = new SqlDataSource();
    pgpDataSource.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionStringLogin"].ToString();

    pgpDataSource.InsertCommandType = SqlDataSourceCommandType.Text;
    pgpDataSource.InsertCommand = "INSERT INTO [Aerobics] ([MachineID], [Intensity], [ExerciseTime]) VALUES (@MachineID, @Intensity, @ExerciseTime)";


    pgpDataSource.InsertParameters.Add("MachineID", rower.ToString());
    pgpDataSource.InsertParameters.Add("Intensity", txtRowerLevel.Text);
    pgpDataSource.InsertParameters.Add("ExerciseTime", txtRowerTime.Text);

    int RowsAffected = 0;

    try
    {
    RowsAffected = pgpDataSource.Insert();
    }

    catch (Exception ex)
    {
    Server.Transfer("~/Problem.aspx");
    }

    finally
    {
    pgpDataSource = null;
    }
    //same code as above for the resistance table

    However, i am facing the problem where this does not populate the link table(ExerciseMaster) with any information as i am unable to write the relevant IDs into the table that have been auto generated by SQL Server for each of the subTables.
    I have read several forums where they recommend using something called @@IDENTITY but i have no idea how or where to use this in order to fill my exercise table...
    Any help would be so much appreciated.... Also, hopefully what i have said all makes sense and someone will be able to help me...oh and one more thing...this is an ASP.NET page coding in C#
    Cheers
    Scotty
    Last edited by scotterson; 02-20-07 at 12:56.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I think the problem is in your design. I'm thinking you should dump the ExerciseMaster table.

    If you have a one-to-one relationship between attendance, Resistance, and Aerobics, then a single table will do. If you have a one-to-many relationship (for a given attendance, there may be zero or more resistance sessions and zero or more aerobics sessions) then this schema should work for you:

    1. Attendance Table (AttendanceID(PK Identity specific), MembershipNo, Date)
    2. Resistance Table (ResistId(PK Identity specific), AttendanceID(FK), Weight , Reps, Sets)
    3. Aerobics Table(AerobicsID(PK Identity specific), AttendanceID(FK), MachineID, Intensity, Time)

    You can insert the attendance record and then either recover the ID created using @@Identity or switch to GUID identifiers and let your interface create the ID. Then use that ID when you insert the Resistance and Aerobics records.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Feb 2007
    Posts
    4

    Inserting to multiple tables in SQL Server 2005 that use identity specification

    Thanks Blindman, I see where you are coming from about the tables and I agree with what you have suggested. However, i am still a bit lost as to what the actual code would be to use the @@IDENTITY...
    eg.
    Suppose i have changed my database design to reflect your suggestions and want to enter code to the Aerobics table, how do i use the attendanceID that is used in the attendance table and enter it into the attendanceID in the Aerobics table..I have no idea how or where to use the @@IDENTITY in my code...

    //insert an attendance record to the attendance table
    string userID;

    userID = Session["User"].ToString();

    SqlDataSource pgpDataSource = new SqlDataSource();
    pgpDataSource.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionStringLogin"].ToString();

    pgpDataSource.InsertCommandType = SqlDataSourceCommandType.Text;
    pgpDataSource.InsertCommand = "INSERT INTO [Attendance] ([MembershipNo], [Date]) VALUES (@MembershipNo, @Date)";
    pgpDataSource.InsertParameters.Add("MembershipNo", userID);
    pgpDataSource.InsertParameters.Add("Date", txtVisitDate.Text);

    int RowsAffected = 0;

    try
    {
    RowsAffected = pgpDataSource.Insert();
    }

    catch (Exception ex)
    {
    Server.Transfer("~/Problem.aspx");
    }

    finally
    {
    pgpDataSource = null;
    }


    //insert an aerobics record into the aerocibs table

    SqlDataSource pgpDataSource = new SqlDataSource();
    pgpDataSource.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionStringLogin"].ToString();

    pgpDataSource.InsertCommandType = SqlDataSourceCommandType.Text;
    pgpDataSource.InsertCommand = "INSERT INTO [Aerobics] ([AttendanceID], [/COLOR][MachineID], [Intensity], [ExerciseTime]) VALUES (@MachineID, @Intensity, @ExerciseTime)";


    pgpDataSource.InsertParameters.Add("MachineID", rower.ToString());
    pgpDataSource.InsertParameters.Add("Intensity", txtRowerLevel.Text);
    pgpDataSource.InsertParameters.Add("ExerciseTime", txtRowerTime.Text);

    int RowsAffected = 0;

    try
    {
    RowsAffected = pgpDataSource.Insert();
    }

    catch (Exception ex)
    {
    Server.Transfer("~/Problem.aspx");
    }

    finally
    {
    pgpDataSource = null;
    }

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Well, the other thing you need to do is to stop issuing dynamic SQL code from your application. It is more complicated, less efficient, and insecure.

    Write a store procedure called "InsertAttendance" that takes MembershipNo and Date as parameters. The stored procedure will insert the new record (after checking to make sure it does not already exist), and then call @@Identity (better yet, use @@SCOPEIDENTITY...) to get the ID that was just created. The sproc can pass the new ID back as an output parameter.

    Or, as I said, you can switch to using GUIDs rather than identity integers as your primary key. Then you can actually create the new GUID within your application code, and you never have to query the database to find out what ID was assigned.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Feb 2007
    Posts
    4
    is there any chance you could provide some sample code as to how i would call the @@IDENTITY, where i would call it from and how i would use it to actually write the information to another table ? im a bit lost as im very new to all this...
    Thanks

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Code:
    set nocount on
    --Create a dummy table
    create table SampleTable (IdentityColumn int identity, DummyData varchar(50))
    go
    
    --Create a dummy sproc
    create procedure InsertSample (@DummyValue varchar(50), @NewIdentity int output)
    as
    begin
    insert into SampleTable (DummyData) values(@DummyValue)
    set @NewIdentity = Scope_Identity()
    end
    go
    
    --Run some insert statements and check the ID values retrieved
    declare	@NewID int
    exec InsertSample 'Fubar1', @NewIdentity = @NewID output
    select	@NewID
    exec InsertSample 'Fubar2', @NewIdentity = @NewID output
    select	@NewID
    exec InsertSample 'Fubar3', @NewIdentity = @NewID output
    select	@NewID
    
    --Cleanup
    drop procedure InsertSample
    drop table SampleTable
    go
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Feb 2007
    Posts
    4
    Thanks for that, Have that working but only when i execute the stored procedure from the database. I am still unable to actually use it unfortunatley within the application....


    string userID;
    const int macId = 3;


    userID = Session["User"].ToString();

    SqlDataSource pgpDataSource = new SqlDataSource();
    pgpDataSource.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionStringLogin"].ToString();
    pgpDataSource.SelectCommandType = SqlDataSourceCommandType.Text;

    pgpDataSource.InsertCommandType = SqlDataSourceCommandType.StoredProcedure;
    pgpDataSource.InsertCommand = "InsertAttendance";

    pgpDataSource.InsertParameters.Add("MembershipNo", userID);
    pgpDataSource.InsertParameters.Add("Date", txtVisitDate.Text);
    //I think this may be the problem, both here and again below where
    // i have the line of code commented out again...
    //pgpDataSource.InsertParameters.Add("AttendanceID", newID.ToString());

    pgpDataSource.InsertParameters.Add("MachineID", macId.ToString());
    pgpDataSource.InsertParameters.Add("Intensity", txtRowerLevel.Text );
    pgpDataSource.InsertParameters.Add("ExerciseTime", txtRowerTime.Text);
    //pgpDataSource.InsertParameters.Add("AttendanceID", newID.ToString());
    pgpDataSource.InsertParameters.Add("Reps", txtReps.Text);
    pgpDataSource.InsertParameters.Add("Sets", txtSets.Text);

    int RowsAffected = 0;

    try
    {
    RowsAffected = pgpDataSource.Insert();
    }

    catch (Exception ex)
    {
    Server.Transfer("~/Problem.aspx");
    }

    finally
    {
    pgpDataSource = null;
    }

    if (RowsAffected != 1)
    {
    Response.Redirect("~/Problem.aspx");
    }
    else
    {
    Response.Redirect("~/Log.aspx");
    }
    }

    This is the storedProcedure i created to fill all three tables which works but not when i try to use it in the application...

    ALTER PROCEDURE InsertAttendance

    @NewIdentity int output,
    @MembershipNo smallint,
    @Date datetime,
    @Reps smallint,
    @Sets smallint,
    @Weight smallint,
    @MachineID smallint,
    @Intensity smallint,
    @ExerciseTime int

    AS

    BEGIN

    INSERT Attendance (MembershipNo, Date)
    Values (@MembershipNo, @Date)

    INSERT Aerobics (AttendanceID, MachineID, Intensity, ExerciseTime)
    Values (@NewIdentity, @MachineID, @Intensity, @ExerciseTime)

    INSERT Resistance ( AttendanceID, Reps, Sets, Weight)
    VALUES (@NewIdentity, @Reps, @Sets, @Weight)
    Set @NewIdentity = Scope_Identity()

    END

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    How you call this stored procedure from your application code depends upon your programming platform. I don't code interfaces, so I can't help you with that. But I'd have a hard time believing that any respectable programming language would not have a method of calling a stored procedure and retrieving an output parameter.
    I suggest you ask about this on a forum specific to your development platform.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Feb 2005
    Posts
    78
    Are you sure this works properly? I would have thought that you would have to set the value of @NewIdentity before you used it in the Insert statements for the Aerobics and Resistance tables.

    [/QUOTE]

    INSERT Attendance (MembershipNo, Date)
    Values (@MembershipNo, @Date)

    INSERT Aerobics (AttendanceID, MachineID, Intensity, ExerciseTime)
    Values (@NewIdentity, @MachineID, @Intensity, @ExerciseTime)

    INSERT Resistance ( AttendanceID, Reps, Sets, Weight)
    VALUES (@NewIdentity, @Reps, @Sets, @Weight)
    Set @NewIdentity = Scope_Identity()

    END[/QUOTE]

Posting Permissions

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