I'm trying to insert data into a database using a SQL procedure and an MVC controller. I have the data obtained using a HTML form which is then retrieved by the Create method and added to the parameters of the SQL procedure.
public ActionResult Create([Bind(Include = "UserID,FirstName,Surname,Password,Salt,Phone_Number,Email,IsAdmin")] SaltUsersTable saltUsersTable, FormCollection fc)
{
if (ModelState.IsValid)
{
SqlConnection con = new SqlConnection(@"Connection String");
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "dbo.AddSaltedUser";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = con;
cmd.Parameters.Add("@Password", SqlDbType.NVarChar, 50).Value = Request.Form["Password"];
cmd.Parameters.Add("@FirstName", SqlDbType.NVarChar, 50).Value = Request.Form["FirstName"];
cmd.Parameters.Add("@Surname", SqlDbType.NVarChar, 50).Value = Request.Form["Surname"];
cmd.Parameters.Add("@Email", SqlDbType.NVarChar, 100).Value = Request.Form["Email"];
cmd.Parameters.Add("@PhoneNumber", SqlDbType.NVarChar, 12).Value = Request.Form["PhoneNumber"];
cmd.Parameters.Add("@response", SqlDbType.NVarChar, 250).Direction = ParameterDirection.Output;
if (Request.Form["FirstName"] == "Admin")
{
cmd.Parameters.Add("@IsAdmin", SqlDbType.Bit).Value = 1;
}
else
{
cmd.Parameters.Add("@IsAdmin", SqlDbType.Bit).Value = 0;
}
//Execute the command just established
con.Open();
Int32 rowsAffected = cmd.ExecuteNonQuery();
con.Close();
The code shown above is the Create controller (the connection string I've replaced to be generic)
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[AddSaltedUser]
@FirstName NVARCHAR(50),
@Surname NVARCHAR(50),
@Password NVARCHAR(50),
@PhoneNumber NVARCHAR(12),
@Email NVARCHAR(50),
@IsAdmin BIT,
@response NVARCHAR(250) OUTPUT
AS
BEGIN
SET NOCOUNT ON
DECLARE @salt UNIQUEIDENTIFIER=NEWID()
BEGIN TRY
INSERT INTO dbo.SaltUsersTable (FirstName, Surname , Password, PhoneNumber, Email, IsAdmin)
VALUES(@FirstName, @Surname, HASHBYTES('SHA_512',@Password+CAST(@salt AS NVARCHAR(36))), @PhoneNumber, @Email, @IsAdmin)
SET @response='Success'
END TRY
BEGIN CATCH
SET @response='ERROR'
END CATCH
END
This code is the SQL procedure I wrote which should insert the parameters passed from the MVC controller apart from the salted hash which is done within the procedure.
The issue with this code is that it won't actually insert anything into the database. When I run through the code and have a breakpoint at rowsAffected it shows that it is 0 and I have no idea why. Where am I going wrong in this code?
I should say however that the parameters do exist and the form values are collected properly within the controller as I have gone through it with a debugger.
