data reader is incompatible with the specified. A member of the type, does not have a corresponding column in the data reader with the same name

Question

So I am using a Stored Procedure to Add a new user to db in a test MVC application using C#.

I keep getting an error, and I believed it was caused by the fact that I was returning the ID of the newly added user to the db, commenting out the code did nothing :(, or rather it gave me the same error but complaining about a different variable.

The funny thing is, it successfully adds a user just fine, except it throws an exception.

I would still like to return the ID to the calling program, and I would still like to this with a parameterized query.

Code Below:

#region AddUser
public static bool AddUser(Models.User user)
{
    bool result = false;
    Models.UserRegistrationPasswordsEntities1 db = new Models.UserRegistrationPasswordsEntities1();
    var queryResult = db.Users.SqlQuery(@"EXECUTE uspAddUser @1, @2, @3, @4, @5, @6",
                                        new SqlParameter("1", user.userFirstName),
                                        new SqlParameter("2", user.userLastName),
                                        new SqlParameter("3", user.userName),
                                        new SqlParameter("4", user.userEmail),
                                        new SqlParameter("5", user.userPassword),
                                        new SqlParameter("6", user.userDateOfBirth)).Single();


    // the ternary opertor is pretty awesome
    result = queryResult == null ? false : true;

    return result;
}
#endregion

Model.user below:

public partial class User
{
    public int userID { get; set; }

    [Display(Name = "First Name")]
    [DataType(DataType.Text)]
    [Required(AllowEmptyStrings = false, ErrorMessage ="First name required")]
    public string userFirstName { get; set; }

    [Display(Name = "Last Name")]
    [DataType(DataType.Text)]
    [Required(AllowEmptyStrings = false, ErrorMessage = "Last name required")]
    public string userLastName { get; set; }

    [Display(Name = "Username")]
    [DataType(DataType.Text)]
    [Required(AllowEmptyStrings = false, ErrorMessage = "Username required")]
    public string userName { get; set; }

    [Display(Name = "Email")]
    [DataType(DataType.EmailAddress)]
    [Required(AllowEmptyStrings = false, ErrorMessage = "email is required")]
    public string userEmail { get; set; }

    [Display(Name = "Date Of Birth")]
    [DataType(DataType.DateTime)]
    [Required(AllowEmptyStrings = false, ErrorMessage = "Date of Birth is required")]
    [DisplayFormat(ApplyFormatInEditMode = true, DataFormatString = "{0:MM/dd/yyyy}")]
    public DateTime userDateOfBirth { get; set;}


    [Display(Name = "Password")]
    [DataType(DataType.Password)]
    [Required(AllowEmptyStrings = false, ErrorMessage = "Password is required")]
    [MinLength(6, ErrorMessage = "Minimum of 6 characters required")]
    public string userPassword { get; set; }

    [Display(Name = "Confirm Password")]
    [DataType(DataType.Password)]
    [Required(AllowEmptyStrings = false, ErrorMessage = "Confirm password is required")]
    [Compare("userPassword", ErrorMessage = "Confirm password and password do not match")]
    public string userConfirmPassword { get; set; }

    public bool userStatus { get; set; }
    public bool userEmailVerificationStatus { get; set; }

    public System.Guid userActivationCode { get; set; }
}

Error Below:
This is the error I receive with the stored procedure code commented out as seen below. If I uncomment it screams about userFirstName

The data reader is incompatible with the specified 'UserRegistrationPasswordsModel.User'. A member of the type, 'userID', does not have a corresponding column in the data reader with the same name.

Create Table Below:

CREATE TABLE [dbo].[Users] (
    [userID]                      INT              IDENTITY (1, 1) NOT NULL,
    [userFirstName]               VARCHAR (50)     NOT NULL,
    [userLastName]                VARCHAR (50)     NOT NULL,
    [userName]                    VARCHAR (50)     NOT NULL,
    [userEmail]                   VARCHAR (50)     NOT NULL,
    [userPassword]                VARCHAR (255)    NOT NULL,
    [userStatus]                  BIT              DEFAULT ((0)) NOT NULL,
    [userEmailVerificationStatus] BIT              DEFAULT ((0)) NOT NULL,
    [userActivationCode]          UNIQUEIDENTIFIER DEFAULT (newid()) NOT NULL,
    [userDateOfBirth]             DATETIME         NOT NULL,
PRIMARY KEY CLUSTERED ([userID] ASC)
);

Stored Procedure below:
Note that the SELECT at the bottom is commented out cause I thought that maybe returning the ID to the calling program was the problem. In my final solution I would like to still return the Id to the calling program in this way if at all possible.

CREATE PROCEDURE uspAddUser
 @userFirstName                 VARCHAR(50)
,@userLastName                  VARCHAR(50)
,@userName                      VARCHAR(50)
,@userEmail                     VARCHAR(50)
,@userPassword                  VARCHAR(100)
,@userDateOfBirth               DATETIME

AS
SET NOCOUNT ON      -- Report Only Errors
SET XACT_ABORT ON   -- Rollback transaction on error

BEGIN TRANSACTION

DECLARE @userID  INTEGER

-- CREATE new record
INSERT INTO Users(userFirstName, userLastName, userName, userEmail, userPassword, userStatus, userEmailVerificationStatus, userDateOfBirth)
VALUES(@userFirstName, @userLastName, @userName, @userEmail, @userPassword, 0 ,0, @userDateOfBirth)  -- 1 = Active

---- return ID to calling program
--SELECT UserID FROM Users WHERE userFirstName = @userFirstName AND
--                             userLastName = @userLastName   AND
--                             userName = @userName           AND
--                             userEmail = @userEmail

COMMIT TRANSACTION

Resources I accessed searching for solution below:
Exception : Execute Insert Stored Procedure using Entity framework ExecuteStoreQuery function

https://blogs.msdn.microsoft.com/diego/2012/01/09/stored-procedures-with-output-parameters-using-sqlquery-in-the-dbcontext-api/

Get return value from stored procedure

Using Entity Framework, should I use RETURN or SELECT in my stored procedures?

Any and all help is appreciated, Thanks.

Answer 1

As a general rule, you need to return full part of the entity when using SELECT statement with SqlQuery method (instead of single property). Change the SELECT statement inside stored procedure to this:

SELECT TOP 1 * FROM Users 
WHERE userFirstName = @userFirstName AND userLastName = @userLastName 
AND userName = @userName AND userEmail = @userEmail

Then, execute it into queryResult:

var queryResult = db.Users.SqlQuery(@"EXECUTE uspAddUser @1, @2, @3, @4, @5, @6",
                                        new SqlParameter("1", user.userFirstName),
                                        new SqlParameter("2", user.userLastName),
                                        new SqlParameter("3", user.userName),
                                        new SqlParameter("4", user.userEmail),
                                        new SqlParameter("5", user.userPassword),
                                        new SqlParameter("6", user.userDateOfBirth)).Single();

If you wish to obtain userID from AddUser method, you can call it from the query result above (and change return type to int instead of bool):

int userId = queryResult.userID;

return userId;

Side note:

Since userID declared as primary key identity column, put KeyAttribute and DatabaseGeneratedAttribute to set corresponding property as primary key in EF model class:

public partial class User
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int userID { get; set; }

    // other properties

}

Similar issue:

does not have a corresponding column in the data reader with the same name