How to use Stored Procedure SCOPE_IDENTITY() output in Session C#

See more:

C#

ASP.NET

SQL-Server

, +

I'm trying to use the SCOPE_IDENTITY from an Insert stored procedure and pass it for use in an Update statement as well. I've been trying to get this to work for almost 2 days now, any suggestions would be greatly appreciated. Here is my code:


Insert Stored Procedure Code:

ALTER PROCEDURE [dbo].InsertUser
(
	@UserID int OUTPUT,
	@FirstName nvarchar(50),
	@LastName nvarchar(50),
	@Username nvarchar(50),
	@Password nvarchar(50),
	@SecurityQuestionOption nvarchar(50),
	@SecurityAnswer nvarchar(50),
	@Address nvarchar(50),
	@City nvarchar(50),
	@State nvarchar(50),
	@ZipCode nvarchar(50),
	@PhoneNumber nvarchar(50),
	@EMail nvarchar(50),
	@LastLogin datetime,
	@IsActive bit,
	@IsAdmin bit
)
AS
	SET NOCOUNT ON;
INSERT INTO [Users] ([FirstName], [LastName], [Username], [Password], [SecurityQuestionOption], [SecurityAnswer], [Address], [City], [State], [ZipCode], [PhoneNumber], [EMail], [LastLogin], [IsActive], [IsAdmin]) VALUES (@FirstName, @LastName, @Username, @Password, @SecurityQuestionOption, @SecurityAnswer, @Address, @City, @State, @ZipCode, @PhoneNumber, @EMail, @LastLogin, @IsActive, @IsAdmin);
	
SELECT @UserID = SCOPE_IDENTITY();

Here's the Register.aspx.cs code for inserting:

protected void ButtonRegisterUser_Click(object sender, EventArgs e)
    {
        try
        {
            //Inserts user into database if student is selected, password is encrypted
            SqlCommand cmd = new SqlCommand("InsertUser", cn);
            cmd.Parameters.Add("@UserID", SqlDbType.Int).Direction = ParameterDirection.Output;
            cmd.Parameters.Add("@FirstName", SqlDbType.NVarChar).Value = TextBoxFName.Text;
            cmd.Parameters.Add("@LastName", SqlDbType.NVarChar).Value = TextBoxLName.Text;
            cmd.Parameters.Add("@Username", SqlDbType.NVarChar).Value = TextBoxUsername.Text;
            cmd.Parameters.Add("@Password", SqlDbType.NVarChar).Value = FormsAuthentication.HashPasswordForStoringInConfigFile(TextBoxPassword.Text, "SHA1");
            cmd.Parameters.Add("@SecurityQuestionOption", SqlDbType.NVarChar).Value = DropDownListSecurityQuestion.SelectedValue;
            cmd.Parameters.Add("@SecurityAnswer", SqlDbType.NVarChar).Value = TextBoxSecurityAnswer.Text;
            cmd.Parameters.Add("@Address", SqlDbType.NVarChar).Value = TextBoxAddress.Text;
            cmd.Parameters.Add("@City", SqlDbType.NVarChar).Value = TextBoxCity.Text;
            cmd.Parameters.Add("@State", SqlDbType.NVarChar).Value = DropDownListState.SelectedValue;
            cmd.Parameters.Add("@ZipCode", SqlDbType.NVarChar).Value = TextBoxZipCode.Text;
            cmd.Parameters.Add("@PhoneNumber", SqlDbType.NVarChar).Value = TextBoxPhoneNumber.Text;
            cmd.Parameters.Add("@EMail", SqlDbType.NVarChar).Value = TextBoxEmailAddress.Text;
            cmd.Parameters.Add("@LastLogin", SqlDbType.DateTime).Value = DateTime.Now.ToString();
            cmd.Parameters.Add("@IsActive", SqlDbType.Bit).Value = "True";
            cmd.Parameters.Add("@IsAdmin",SqlDbType.Bit).Value = "False";
            cmd.CommandType = CommandType.StoredProcedure;
            cn.Open();
            cmd.ExecuteNonQuery();
            int ID = Convert.ToInt32(cmd.Parameters["@UserId"].Value);
            //UserID that will be passed into session to be checked throughout program
            Session["UserId"] = ID;
            cn.Close();

            //username that will be passed onto confirm registration
            Session["uname"] = TextBoxUsername.Text;

            //Information session to be passed to the confirm registration
            Session["fname"] = TextBoxFName.Text;
            Session["lname"] = TextBoxLName.Text;
            Session["address"] = TextBoxAddress.Text;
            Session["city"] = TextBoxCity.Text;
            Session["state"] = DropDownListState.SelectedValue;
            Session["zip"] = TextBoxZipCode.Text;
            Session["phone"] = TextBoxPhoneNumber.Text;
            Session["email"] = TextBoxEmailAddress.Text;
            Response.Redirect("~/ConfirmUserRegistration.aspx");
        }
        catch (Exception ex)
        {
            LabelMessage.Text = ex.Message.ToString();
        }
    }

Here's the Update Stored Procedure code:

ALTER PROCEDURE [dbo].UpdateUser
(
	@FirstName nvarchar(50),
	@LastName nvarchar(50),
	@Address nvarchar(50),
	@City nvarchar(50),
	@State nvarchar(50),
	@ZipCode nvarchar(50),
	@PhoneNumber nvarchar(50),
	@EMail nvarchar(50),
	@Original_UsersID int,
	@UsersID int
)
AS
	SET NOCOUNT OFF;
UPDATE [Users] SET [FirstName] = @FirstName, [LastName] = @LastName, [Address] = @Address, [City] = @City, [State] = @State, [ZipCode] = @ZipCode, [PhoneNumber] = @PhoneNumber, [EMail] = @EMail WHERE (([UsersID] = @Original_UsersID));
	
SELECT UsersID, FirstName, LastName, Username, Password, SecurityQuestionOption, SecurityAnswer, Address, City, State, ZipCode, PhoneNumber, EMail, LastLogin, IsActive, IsAdmin FROM Users WHERE (UsersID = @UsersID)

And finally here's the code for the insert, UpdateRegister.aspx.xs:

protected void ButtonUpdate_Click(object sender, EventArgs e)
    {
            //Updates contact information
            cmd = new SqlCommand("UpdateUser", cn);
            cmd.Parameters.Add("@UsersID", SqlDbType.Int).Value = Session["UserId"];
            cmd.Parameters.Add("@Original_UsersID", SqlDbType.Int).Value = Session["UserId"];
            cmd.Parameters.Add("@FirstName", SqlDbType.NVarChar).Value = TextBoxFName.Text;
            cmd.Parameters.Add("@LastName", SqlDbType.NVarChar).Value = TextBoxLName.Text;
            cmd.Parameters.Add("@Address", SqlDbType.NVarChar).Value = TextBoxAddress.Text;
            cmd.Parameters.Add("@City", SqlDbType.NVarChar).Value = TextBoxCity.Text;
            cmd.Parameters.Add("@State", SqlDbType.NVarChar).Value = DropDownListState.SelectedValue;
            cmd.Parameters.Add("@ZipCode", SqlDbType.NVarChar).Value = TextBoxZipCode.Text;
            cmd.Parameters.Add("@PhoneNumber", SqlDbType.NVarChar).Value = TextBoxPhoneNumber.Text;
            cmd.Parameters.Add("@EMail", SqlDbType.VarChar).Value = TextBoxEmailAddress.Text;
            cmd.CommandType = CommandType.StoredProcedure;
            cn.Open();
            cmd.ExecuteNonQuery();
            cn.Close();
            Response.Redirect("~/Login.aspx");
    }