Azrael94 Azrael94 - 1 month ago 6
ASP.NET (C#) Question

C# MVC - SP not insert values

I'm trying to make an insert from my controller to my database. I already debug and it never enter on the exception. But i can not see the values on the db.

This is my sp:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[RegisterUser]
-- Add the parameters for the stored procedure here
@Username nvarchar(10),
@Password nvarchar(10),
@Mail nvarchar(10),
@Birthday date

AS
BEGIN
SET NOCOUNT ON

INSERT INTO Users
(
Username,
Pass,
Mail,
Birthday
)

VALUES
(
@Username,
@Password,
@Mail,
@Birthday
)

END


And on my model i used this method:

public bool registerUser(UserModel user)
{
bool isOk = false;

using (SqlConnection connection = DbConnection.OpenConnection2())
{
try
{

using (SqlCommand command = new SqlCommand("RegisterUser", connection))
{
command.CommandType = CommandType.StoredProcedure;
SqlCommandBuilder.DeriveParameters(command);

command.Parameters.Add(new SqlParameter("@UserName", SqlDbType.VarChar)).Value = user.userName.Trim();
command.Parameters.Add(new SqlParameter("@Password", SqlDbType.VarChar)).Value = user.password.Trim();
command.Parameters.Add(new SqlParameter("@Mail", SqlDbType.VarChar)).Value = user.mail.Trim();


command.ExecuteNonQuery();

}
}
catch (Exception e)
{

}
finally
{
if (connection.State.Equals(ConnectionState.Open))
{
connection.Close();
isOk = true;
}
}
}
return isOk;
}


Any ideas of why this is not working?

Thanks

Answer

Things are easy, if you use exception handling in a proper manner. You are swallowing the exception thrown by Ado.net. Follow these steps for solving your problem:

  • First of all you should add a throw statement in your catch block to get the exception being thrown. In production, You should catch that at later calling function or layer.
  • You need to provide all the parameters to your stored procedure. Currently, You are not passing one parameter named @Birthday and its value. Add this parameter with its value.

Here is the modified code that should work.

command.CommandType = CommandType.StoredProcedure;                  
command.Parameters.Add(new SqlParameter("@UserName", SqlDbType.VarChar)).Value = user.userName.Trim();
command.Parameters.Add(new SqlParameter("@Password", SqlDbType.VarChar)).Value = user.password.Trim();
command.Parameters.Add(new SqlParameter("@Mail", SqlDbType.VarChar)).Value = user.mail.Trim();
//Assuming that the user object has a field named Birthday
command.Parameters.Add(new SqlParameter("@Birthday", SqlDbType.Date)).Value = user.Birthday; 
command.ExecuteNonQuery();

If you follow my first advice, you will nearly get any other problem that is occurring in your code.

I hope it will help you somehow. Thanks!

Comments