locked
Implicit conversion from data type nvarchar to varbinary is not allowed. Use the CONVERT function to run this query RRS feed

  • Question

  • User-1355965324 posted

    When I am trying to update , the error message is coming.  I think the error is coming while updating the password into table ,  Password is varbinary data type

    Following is my code 

    SqlServer Table

    CREATE TABLE [dbo].[UserTable](
    [UserID] [int] IDENTITY(1,1) NOT NULL,
    [FirstName] [nvarchar](max) NOT NULL,
    [LastName] [nvarchar](max) NOT NULL,
    [UserName] [nvarchar](50) DEFAULT (''),
    [Password] [varbinary](150) NULL,
    [RoleID] [int] NOT NULL DEFAULT ((0)),
    [email] [nvarchar](50) NOT NULL DEFAULT (''),
    [IsActive] [bit] NOT NULL DEFAULT ((0)))

    My Model cs

    public int UserID { get; set; }

    [DefaultValue("")]

    public string FirstName { get; set; }
    [DefaultValue("")]
    public string LastName { get; set; }
    [DefaultValue("")]
    [Required]
    public string UserName { get; set; }

    [Required]
    [DataType(DataType.Password)]
    public string Password { get; set; }


    public int RoleID { get; set; } = 1;

    [Required]
    [RegularExpression(@"^[a-zA-Z0-9.!#$%&’*+/=?^_`{|}~-]+@[a-zA-Z0-9-]+(?:\.[a-zA-Z0-9-]+)*$", ErrorMessage = "Invalid email address")]
    public string CompanyEmail { get; set; }
    [EmailAddress(ErrorMessage = "Invalid email address")]
    public bool IsActive { get; set; } = true;

    C# code

    try
    {
    SqlParameter parUserId = new SqlParameter("@UserID", _user.UserID);
    parUserId.Direction = ParameterDirection.InputOutput;
    SqlParameter _username = new SqlParameter("@UserName", _user.UserName);
    SqlParameter _password = new SqlParameter("@Password", _user.Password);
    SqlParameter _email = new SqlParameter("@Email", _user.CompanyEmail);
    SqlParameter _firstname = new SqlParameter("@FirstName", _user.FirstName);
    SqlParameter _lastname = new SqlParameter("@LastName", _user.LastName);
    SqlParameter _roleid = new SqlParameter("@RoleID", _user.RoleID);
    SqlParameter _isActive = new SqlParameter("@IsActive", _user.IsActive);

    SqlParameter[] parameters = {
    parUserId
    ,_username
    , _password
    ,_email
    ,_firstname
    ,_lastname
    ,_roleid
    ,_isActive
    };
    SqlHelper.ExecuteNonQuery(_trnUser, CommandType.StoredProcedure,
    StoredProcedureConstants.INSERT_UPDATE_USER,
    AppConstants.DEFAULT_COMMAND_TIME_OUT, parameters);
    _userid = Convert.ToInt32(parUserId.Value);
    }
    catch (Exception ex)
    {
    string error = ex.InnerException.Message;
    //Console.Write(ex.InnerException);
    throw ex;
    }

    Please can u help

    Tuesday, November 13, 2018 12:04 PM

All replies

  • User225117742 posted

    VARBINARY is for binary data like storing files/images. You can store a string as binary but your class needs the password parameter to be public byte[] Password { get; set; }

    If the password is a string as indicated in your class, I would recommend changing the database field from VARBINARY to VARCHAR or NVARCHAR.

    Tuesday, November 13, 2018 2:59 PM
  • User-474980206 posted
    Also normally you world store a binary hash of the password, not the text.
    Tuesday, November 13, 2018 3:06 PM
  • User-1355965324 posted

    If I assign   the datatype as byte[] in model

    [Required]
    [DataType(DataType.Password)]
    public byte[] Password { get; set; }

    , how can I define in the  view, Please can you let me know

    <div class="controls col-sm-9">
    <input id="txtPassword" type="password" asp-for="Password" class="form-control k-textbox" data-role="text" data-parsley-errors-container="#errId6">
    <span asp-validation-for="Password" class="text-danger"></span>
    </div>

    Pol

    Tuesday, November 13, 2018 4:54 PM
  • User475983607 posted

    Use standard MVC patterns and practices which can be found by going through the abundant Getting Started tutorials from the learn link above. 

    Build a ViewModel to hold the user data submitted from the HTML Form to the controller action.  Populate an Entity type using the ViewModel.  This includes processing the password input string into the byte[] type. 

    Tuesday, November 13, 2018 5:17 PM
  • User753101303 posted

    Hi,

    You have to distinguish between how a user will use a password (ie text) and how a password is stored in a db (hashed). So the view model would use a string but the db entity uses a byte array to store the hashed password (at least I believe this is your intent).

    Tuesday, November 13, 2018 5:51 PM