Scope_Identity not working with GUID on c#

Question

I've been trying to insert some data on a database and at the same time get the identifier.

The identifier type is a Guid.

I already tried some resolutions.

I was searching but I couldn't found any that worked with a Guid in C#.

The last one I tried was like this:

        dbAccess db = new dbAccess();
        SqlConnection con = db.openConnection();
        Guid retVal = Guid.Empty;
        try
        {
            SqlCommand cmd = new SqlCommand(@"insert into Comment(IdDiscussion,UserId,Description,DateTime)
                                            values(@IdDiscussion,@UserId,@description,@dateTime);
                                            set @ret=SCOPE_IDENTITY();", con);
            cmd.Parameters.AddWithValue("@IdDiscussion", discussionId);
            cmd.Parameters.AddWithValue("@UserId", userId);
            cmd.Parameters.AddWithValue("@description", comment);
            cmd.Parameters.AddWithValue("@dateTime", dateTime);
            SqlParameter retParameter = new SqlParameter("@ret", SqlDbType.UniqueIdentifier);
            retParameter.Direction = ParameterDirection.Output;
            cmd.Parameters.Add(retParameter);
            cmd.ExecuteNonQuery();
            retVal = (Guid)retParameter.Value;
        }
        catch (Exception e)
        {
            //doesn't really matter
        }
        db.closeConnection();
        return retVal;

In this case I get the following error on executeNonQuery(): "Operand type clash: numeric is incompatible with uniqueidentifier".

Any suggestion that can help?

If, all that you want is to pass unique identifier to the database, did you try : http://stackoverflow.com/questions/11724614/passing-uniqueidentifier-parameter-to-stored-procedure ? — Siva Gopal, Jun 19 '13 at 09:37
Appending: ";SELECT SCOPE_IDENTITY();" to end of the in line query and calling ExecuteScalar(...) might do the trick for you. Also, please take a look at : http://stackoverflow.com/questions/3913115/scope-identity-in-c-sharp-range — Siva Gopal, Jun 19 '13 at 09:40
Is your Identity Column in `Comment` Table of type `uniqueidentifier`? — gzaxx, Jun 19 '13 at 09:41
I already tryed the executescalar with the select scope at the end but it didn't work either.Yes, my column identity type os uniqueidentifier. — Ricardo Neves, Jun 19 '13 at 09:43
The problem is, `SCOPE_IDENTITY()` returns the result of the last incremented Indentity column in your scope, this is always an number, as all identity columns are numeric. Your `uniquieidentifier` column is not an indentity column so has nothing to do with the `SCOPE_IDENTITY()` function. — Jodrell, Jun 19 '13 at 09:58

score 3 · Answer 1 · answered Jun 19 '13 at 09:56

scope_identity() only returns the last (scoped) identity value - this value datatype has to be numeric.

So unfortunetly only fix that comes to my mind would be something like this:

declare @op table
(
    ColGuid uniqueidentifier
)

insert into Comment(IdDiscussion,UserId,Description,DateTime)
output inserted.CommentId -- GUID column
into @op
values (@IdDiscussion,@UserId,@description,@dateTime)

select top 1 o.ColGuid
from @op o

and then in your code:

var guid = (Guid)cmd.ExecuteScalar();

One more mention: this solution should work in SQL Server 2005 and above

or a more simple version: `var sql = "insert into yourTable(col2,col3) output inserted.guidCol values('test','best')"; var guid = (Guid) cmd.ExecuteScalar();` — dortique, Apr 29 '16 at 08:26

score 1 · Answer 2 · answered Jul 13 '15 at 02:36

I tried to use SCOPE_IDENTITY() to retrieve Guid values, but not worked with me. You can try declare a SQL variable and generate a new guid with the command NEWID() and in the end do a SELECT command in this variable executing ExecuteScalar method on the command object.

DECLARE @Id UNIQUEIDENTIFIER; SET @Id = NEWID();
INSERT INTO [tablename] ([Id], [Field1], [Field2]) VALUES (@Id, @Field1, @Field2);

You must add your parameters on the command normally.

 cmd.Parameters.AddWithValue("@Field1", "Field 1 Value");
 cmd.Parameters.AddWithValue("@Field2", "Field 2 Value"));

So, execute and retrieve the value.

var obj = cmd.ExecuteScalar();
Guid guid = (Guid)obj;

Scope_Identity not working with GUID on c#

2 Answers2