Can't Get Last Transaction ID in C# ASP.NET

Question

I have the following code which runs a stored procedure:

    [WebMethod]
    public static string addNewNote(string id, string txt)
    {
        Guid parentId = new Guid(id);
        DbProviderFactory dbf = DbProviderFactories.GetFactory();
        using (IDbConnection con = dbf.CreateConnection())
        {
            con.Open();
            using (IDbTransaction trn = con.BeginTransaction())
            {

                Guid noteId = Guid.Empty;
                SqlProcs.spNOTES_WebForms
                        (ref noteId,
                        parentId,
                        "Files",
                        "Client Note",
                        txt,
                        true
                        );

                IDbCommand cmd = con.CreateCommand();
                cmd.CommandText = "SELECT SCOPE_IDENTITY()";
                cmd.Transaction = trn;

                // Get the last inserted id.
                string insertID = cmd.ExecuteScalar().ToString();
                Debug.Print(insertID.ToString());
            }
        }
        return "";
    }

It doesn't output anything in the output panel. How can I retrieve the last ID?

Note: My ID is a GUID (uniqueidentifier).

Edit:

This is my stored procedure:

if exists (select * from dbo.sysobjects where id = object_id(N'spNOTES_WebForms') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
    Drop Procedure dbo.spNOTES_WebForms;
GO

Create Procedure dbo.spNOTES_WebForms
    ( @ID                   uniqueidentifier output
    , @PARENT_ID            uniqueidentifier
    , @PARENT_TYPE          nvarchar(255)
    , @MODIFIED_USER_ID     uniqueidentifier
    , @NAME                 nvarchar(255)
    , @DESCRIPTION          ntext
    , @VISIBLE_TO_CLIENT    bit
    )
with encryption
as
  begin
    set nocount on

    if dbo.fnIsEmptyGuid(@ID) = 1 begin -- then
        set @ID = newid();
    end -- if;
    insert into NOTES
        ( ID               
        , PARENT_ID
        , PARENT_TYPE
        , CREATED_BY       
        , DATE_ENTERED     
        , MODIFIED_USER_ID 
        , DATE_MODIFIED    
        , NAME             
        , DESCRIPTION      
        , VISIBLE_TO_CLIENT
        )
    values
        ( @ID               
        , @PARENT_ID
        , @PARENT_TYPE
        , @MODIFIED_USER_ID 
        ,  getdate()        
        , @MODIFIED_USER_ID 
        ,  getdate()        
        , @NAME             
        , @DESCRIPTION      
        , @VISIBLE_TO_CLIENT
        );

    -- 03/04/2006 Paul.  Add record to custom table. 
    if not exists(select * from NOTES_CSTM where ID_C = @ID) begin -- then
        insert into NOTES_CSTM ( ID_C ) values ( @ID );
    end -- if;

  end
GO

Grant Execute on dbo.spNOTES_WebForms to public;
GO

If your "ID" (primary key?) is a GUID, then why are you using `SCOPE_IDENTITY()`? It would help to see your `INSERT` code and relevant table structure (`CREATE TABLE` script), to avoid any confusion. Have you considered returning the new ID from the stored procedure as an output parameter? — Pondlife, Feb 13 '13 at 20:26
I have considered it, but I am not sure how to do that... I added my stored procedure to the code above. — user1477388, Feb 13 '13 at 20:30
[This question](http://stackoverflow.com/questions/339910/retrieve-the-uniqueidentifier-key-value-for-a-record) explains how to get the GUID value; [this one](http://stackoverflow.com/questions/3433694/how-to-run-the-stored-procedure-that-has-output-parameter-from-c) explains how to use get an output parameter from C# — Pondlife, Feb 13 '13 at 20:34
[SCOPE_IDENTITY](http://msdn.microsoft.com/en-us/library/ms190315.aspx) returns the last identity value inserted into an identity column in the same scope -- A scope is a module: a stored procedure, trigger, function, or batch. So better to put that SELECT SCOPE_IDENTITY into your sproc and execute scalar that. — MikeSmithDev, Feb 13 '13 at 20:36
@Pondlife I looked at those but I am still unsure how to apply it in my code. Thanks, anyway. — user1477388, Feb 13 '13 at 20:37
@MikeSmithDev Do you have an example how to do this? Thanks. — user1477388, Feb 13 '13 at 20:38
@user1477388 Make a sql command for your sproc that now has `SELECT SCOPE_IDENTITY()` in it. newCmd.ExecuteScalar just like you are doing, but for the SPROC command. [example](http://stackoverflow.com/questions/1260952/how-to-execute-a-stored-procedure-from-c-sharp-program). — MikeSmithDev, Feb 13 '13 at 20:46
I am already executing my SP, I just can't get my last ID from it. I think I will just do something else... Fight this battle another day :) — user1477388, Feb 13 '13 at 20:49
@user1477388 Yeah. Execute it and return value at same time. Don't put it in two different commands. — MikeSmithDev, Feb 13 '13 at 20:55

score 0 · Answer 1 · answered Feb 13 '13 at 20:14

0

First you should check isDBNull or not? And your query is not returning any id. string id = cmd.ExecuteScalar(); Add SELECT SCOPE_IDENTITY(); end of your query and try again. Also Scope_Identity() returns a decimal. You can use Convert.ToInt32 on the result of your query, or you can cast the return value to decimal and then to int.

answered Feb 13 '13 at 20:14

Kadir

3,094
4
37
57

I am not sure what you mean to have me do. I know the record is going in because I can see it in my list. The query works, but I can't seem to retrieve the ID. My ID is not an integer or decimal, but a `GUID`. Is it not possible to retrieve a `GUID`? – user1477388 Feb 13 '13 at 20:16
1

You can cast scope_identifier() as uniqueidentifier like that: Guid guid = (Guid)cmd.ExecuteScalar(); – Kadir Feb 13 '13 at 20:19
It says, "Specified cast is not valid." on the `Guid guid = (Guid)cmd.ExecuteScalar();` line when I run. – user1477388 Feb 13 '13 at 20:23
1

So cast is as varchar in query. CAST(SCOPE_IDENTITY() AS VARCHAR) – Kadir Feb 13 '13 at 20:26
Sorry, but it still says, "Specified cast is not valid." – user1477388 Feb 13 '13 at 20:29

Can't Get Last Transaction ID in C# ASP.NET

1 Answers1