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

Attendance Table (AttendanceID(PK Identity specific), MembershipNo, Date)
Resistance Table (ResistId(PK Identity specific), Weight , Reps, Sets)
Aerobics Tables(AerobicsID(PK Identity specific), MachineID, Intensity, Time)
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
ScottyI 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.|||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;
}|||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.|||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|||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|||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|||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.|||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]