Operand type clash: int is incompatible with uniqueidentifier c# Dapper.Net

Question

I've tried many solutions on stackoverflow yet not one gave me a solution, I have the following query, which passes in three parameters

  using (var sqlCon = new SqlConnection(Database.ReturnDatabaseConnection()))
   {
     var p = new DynamicParameters();
     p.Add("@EmailAddress", emailAddress);
     p.Add("@UserId", SqlDbType.BigInt, direction: ParameterDirection.InputOutput);
     p.Add("@UniqueId", SqlDbType.UniqueIdentifier, direction: ParameterDirection.InputOutput);

     var t = sqlCon.Execute("RequestPasswordReset", p, commandType: CommandType.StoredProcedure);

     var b = p.Get<Int64>("@UserId");
     var c = p.Get<Guid>("@UniqueId");
    }

which calls the following stored procedure

ALTER PROCEDURE RequestPasswordReset
@EmailAddress varchar(320),
@UserId bigint output,
@UniqueId UniqueIdentifier output
AS
BEGIN

SET NOCOUNT ON;

SET @UserId = (Select ISNULL(Id, NULL) from [User].[User_Profile] where EmailAddress = @EmailAddress and ProfileStatus <> 5)

IF @UserId is not null 
BEGIN 

       SET @UniqueId = NEWID()    

       INSERT INTO [Reset].[PasswordReset] 
              (UserId, UniqueId, 
               DateRequested, DateCompleted)

       VALUES 
              (@UserId, @UniqueId, SYSDATETIME(), NULL)


END

If the user exists I will return the userId (bigint) and the uniqueId (UniqueIdentifier)

when I get to the following line in my C# project

sqlCon.Execute("RequestPasswordReset", p, commandType: CommandType.StoredProcedure);

I get the error message written as the title of this question, can someone explain to me what I'm doing wrong here?

The table structure is as follows:

CREATE TABLE [Reset].[PasswordReset](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[UserId] [bigint] NOT NULL,
[UniqueId] [uniqueidentifier] NOT NULL,
[DateRequested] [datetime] NOT NULL,
[DateCompleted] [datetime] NULL,
CONSTRAINT [PK_Reset]].[PasswordReset] PRIMARY KEY CLUSTERED 
(
[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

User Profile table as requested

CREATE TABLE [User].[User_Profile](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[UniqueId] [uniqueidentifier] NOT NULL,
[Username] [varchar](25) NOT NULL,
[EmailAddress] [varchar](320) NOT NULL,
[Password] [varchar](200) NOT NULL,
[ProfileStatus] [int] NOT NULL,
CONSTRAINT [PK_User.User_Profile] PRIMARY KEY CLUSTERED 
(
[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

How is the `UniqueId` column defined in your database table? — Zohar Peled, May 27 '15 at 10:06
Is this the hole procedure? can you provide the ddl of the table ? — Zohar Peled, May 27 '15 at 10:09
I'm sorry, but I still can't reproduce the problem. Everything seems to be working when I try it on my environment (It's sql server 2012, but I don't think that that is the problem). The problem is somewhere in the c# itself, not in the stored procedure. — Zohar Peled, May 27 '15 at 10:45
@ZoharPeled Yeah I've done that, it works so its something to do with the c# app — Code Ratchet, May 27 '15 at 10:45
@ZoharPeled I'm using Dapper.Net micro ORM, I have updated that section of the question to include the sqlCon — Code Ratchet, May 27 '15 at 10:49
I know nothing about Dapper, but I've looked around and found [this link.](http://stackoverflow.com/questions/5962117/is-there-a-way-to-call-a-stored-procedure-with-dapper) Try this: `p.Add("@UniqueId", dbType: SqlDbType.UniqueIdentifier, direction: ParameterDirection.InputOutput);` **Note** I've added `dbType:` to your parameter declaration. — Zohar Peled, May 27 '15 at 10:56
@ZoharPeled that's what I based my code off, I originally started off with that. tried it again for arguments sake and yet still get the same problem — Code Ratchet, May 27 '15 at 10:59
@ZoharPeled it's definitely that UniqueIdentifer parameter, I just removed it and it worked and returned me the UserId — Code Ratchet, May 27 '15 at 11:01
You can use a simple workaround and return a resultset from the stored procedure instead of the @ uniqueId and @ UserId as parameters, but I'm guessing there should be some other simple thing you are missing. perhaps it's better to wait for someone that has experience with Dapper. At least now you know the where the problem is :-) — Zohar Peled, May 27 '15 at 11:05
@ZoharPeled true, I've updated the question title to include dapper.net and also added an additional tag — Code Ratchet, May 27 '15 at 11:09
What datatype is `direction`? Did you mean to pass the same value for both UserID and UniqueID? — Chris Dunaway, May 27 '15 at 16:10
not sure I'm following you? Data type direction? Userid is a bigint and uniqueid is a unique identifier ? — Code Ratchet, May 27 '15 at 21:31
@ChrisDunaway the direction isn't a datatype it specifies the direction of the parameter, i.e I pass it in and I expect the value to come back hence why I have ParameterDirection.InputOutput — Code Ratchet, May 28 '15 at 00:30

score 0 · Answer 1 · edited Mar 02 '20 at 07:56

0

I was stuck with the same problem. Try this. It solved my issue.

p.Add("@UserId",null,dbType: DbType.BigInt, direction: ParameterDirection.Output);
p.Add("@UniqueId",null,dbType: DbType.Guid, direction: ParameterDirection.Output,size:40);

I guess the Add method expects value as the second parameter, in case of output as a parameter as well.

edited Mar 02 '20 at 07:56

Piotr Labunski

1,638
4
19
26

answered Mar 02 '20 at 07:00

Supriya123

1
1

Operand type clash: int is incompatible with uniqueidentifier c# Dapper.Net

1 Answers1