calling a stored procedure and retrieving an output parameter

Feb 21 2007 5:42 AM
HI, I am having some problems here, i am creating an asp.net website using c# for the functionality...I have a stored procedure called insert attendance which is meant to insert data to the attendace table, this is then linked to both a resistance and an aerobics table through the attendance ID... So i created the following stored procedure in sql server
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

This fills in the attendance ID in both the Aerobics and Resistance table according to the identity specific number(autonumber) entered in the attendance table.... that works fine from here but i am unable to use my application to work with this stored proc.

Here is the code i am trying and i have highlighted where i feel the problem is happening....

string userID;
const int macId = 3;
int newID;
newID = @NewIdentity;

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");
}
}

Please please please say someone can help with this as i am really stuck here. If i need to clarify anything please just let me know..
Thanks
Scotterson