Only first letter of particular data is getting saved in database in ASP.NET MVC

Question

I'm pretty new to ASP.NET MVC so bear with me here.

I'm creating a Registration page using ASP.NET MVC 5 with SQL Server; whenever I fire my create action method, it only stores only the first letter of my data like if I enter "Stack" it will only store 'S'.

I'm posting my controller code here. Please help and thanks in advance.

Controller:

public ActionResult CreateUser()
{
    return View(db.ClsUsers.Create());
}

[HttpPost]
public ActionResult CreateUser(FormCollection form, ClsUserReg userReg)
{
    using (var con = new SqlConnection(ConfigurationManager.ConnectionStrings["CoupanCodeManagmentContext"].ConnectionString))
    {
        var cmd = new SqlCommand("SP_UserReg_Ins ", con);
        cmd.CommandType = CommandType.StoredProcedure;

        // cmd.Parameters.Add(new SqlParameter("@Id", SqlDbType.Int)).Value = userReg.Id;
        cmd.Parameters.Add(new SqlParameter("@Name", SqlDbType.NVarChar)).Value = userReg.Name;
        cmd.Parameters.Add(new SqlParameter("@Mobile", SqlDbType.NVarChar)).Value = userReg.Mobile;
        cmd.Parameters.Add(new SqlParameter("@Email", SqlDbType.NVarChar)).Value = userReg.Email;
        cmd.Parameters.Add(new SqlParameter("@UserName", SqlDbType.NVarChar)).Value = userReg.UserName;
        cmd.Parameters.Add(new SqlParameter("@Password", SqlDbType.NVarChar)).Value = userReg.Password;
        cmd.Parameters.Add(new SqlParameter("@ConfirmedPassword", SqlDbType.NVarChar)).Value = userReg.ConfirmedPassword;

        try
        {
            if (con.State != ConnectionState.Open)
                con.Open();

            cmd.ExecuteNonQuery();
            ModelState.Clear();
        }
        catch (Exception ex)
        {
            return View();
        }
        finally
        {
            if (con.State != ConnectionState.Closed)
                con.Close();
        }

        return RedirectToAction("UsersList");
    }
}

This is how it's getting saved

Use [the constructor for `SqlParameter` that also takes the size of the parameter](https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlparameter.-ctor?view=netframework-4.8#System_Data_SqlClient_SqlParameter__ctor_System_String_System_Data_SqlDbType_System_Int32_) and give the correct for the column size to it. — sticky bit, Dec 14 '19 at 07:12
@PhongNguyen except for id every other filed is nvarchar(max) both in table and in stored procedure — Ankit Tiwari, Dec 14 '19 at 07:22
@AnkitTiwari use -1 as size. https://stackoverflow.com/questions/21087950/how-to-create-nvarcharmax-sqlparameter-in-c — Jesús López, Dec 14 '19 at 07:32
Side note: you should **not** use the `sp_` prefix for your stored procedures. Microsoft has [reserved that prefix for its own use (see *Naming Stored Procedures*)](http://msdn.microsoft.com/en-us/library/ms190669%28v=sql.105%29.aspx), and you do run the risk of a name clash sometime in the future. [It's also bad for your stored procedure performance](http://www.sqlperformance.com/2012/10/t-sql-queries/sp_prefix). It's best to just simply avoid `sp_` and use something else as a prefix - or no prefix at all! — marc_s, Dec 14 '19 at 08:05
You didn't show the stored procedure, but I strongly suspect your **parameters** are defined as just `NVARCHAR` - without any length. In that case, the **default length** of this parameter is **exactly ONE character** - and the solution is simple: ***ALWAYS*** define an explicit **length** for your `NVARCHAR` parameters and variables in SQL Server! (also applies to `VARCHAR`, `CHAR` and `NCHAR`) — marc_s, Dec 14 '19 at 08:07
@marc_s actually it was just that, i forgot to add length in stored procedure after nvarchar and it caused massive headache, thanks for your help — Ankit Tiwari, Dec 14 '19 at 10:45

Vignesh Kumar A · Accepted Answer · 2019-12-14T08:06:04.417

Check the couple of things

Mention the SqlParameter size manually by checking the columns size in SQL Server
You need to specify the size for NVARCHAR parameters in the stored procedure. If an nvarchar parameter does not have a size, it defaults to 1 character and will truncate the data that is passed to it. Refer more here

For example

 ALTER PROCEDURE [dbo].[SP_UserReg_Ins]
    (    
       @Name nvarchar(500) = null,
       @Mobile nvarchar(500) = null,
       @Email nvarchar(500) = null,
       @UserName nvarchar(500) = null,
       @Password nvarchar(500) = null,
       @ConfirmPassword nvarchar(500) = null
    )
    AS
    BEGIN
    --INSERT Statement
    END

that's it , this was the case i was not specifying any limit to nvarchar in stored procedure . Thanku somuch. — Ankit Tiwari, Dec 14 '19 at 08:25

Only first letter of particular data is getting saved in database in ASP.NET MVC

1 Answers1