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
{
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);
Session["UserId"] = ID;
cn.Close();
Session["uname"] = TextBoxUsername.Text;
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)
{
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");
}