Dapper: Implicit conversion from data type datetime to int is not allowed. Use the CONVERT function to run this query

Question

I have this query for inserting a user into the database :

INSERT INTO [User] 
OUTPUT inserted.Id 
VALUES (@IsDelete, @FirstName, @LastName, @UserName, @Email, @Birthdate, @IsActive, @Password, @SecurityStamp, @Gender, @ActivetionCode, @ActivationCodeExpireDateTime)

and this is my model :

User user = new User();
user.Email = request.Email;
user.IsDelete = false;
user.IsActive = false;
user.Birthdate = DateTime.UtcNow;
user.FirstName = "user" + DateTime.Now.Millisecond;
user.LastName = "failmy" + DateTime.Now.Millisecond;
user.Password = request.Password;
user.UserName = request.UserName;
user.Gender = GenderEnum.Male;
user.SecurityStamp = Guid.NewGuid();
user.ActivetionCode = code;
user.ActivationCodeExpireDateTime = DateTime.UtcNow;

and I use this here :

var role = roleConnection.Query<int>(Command, Model);

but I get this error:

Implicit conversion from data type datetime to int is not allowed. Use the CONVERT function to run this query.

What's the problem? How can I solve it?

Answer 1

As commented: possibly there is a mismatch between your value list and the column list, which ends up in a value being written to a column it does not belong.

You are using insert syntax that does not list the target columns:

insert into [User] output inserted.Id values(val1, val2, ...)

This is error prone, and hard to debug when things go wrong. Instead, you should enumerate the columuns in your insert query:

insert into [User](col1, col2, ...) output inserted.Id values(val1, val2, ...)) 

Using this syntax makes it much easier to check if columns and values match.