Getting Identity from SP

Discussion in 'ASP .Net' started by GaryDean, Jan 7, 2007.

  1. GaryDean

    GaryDean Guest

    I'm trying to get Identity back from a stored procedure in SQL Server. When
    I execute the SP in Management Studio @NewID is returned in the first
    row/first columm. but when I run the code below, executescaler returns
    null. I have pasted both the code and the SP below.
    anyone know what is going wrong here?

    public static string InsertOrderSP(ref int OrderID, string FranchiseID,
    int PrimaryVendorID, string Status, string Customer)
    SqlConnection conn = new SqlConnection();
    conn.ConnectionString =
    int rowsAffected;
    SqlCommand cmd = conn.CreateCommand();
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.CommandText = "NewInsertCommand";
    cmd.Parameters.Add(new SqlParameter("@FranchiseID", FranchiseID));
    cmd.Parameters.Add(new SqlParameter("@PrimaryVendorID",
    cmd.Parameters.Add(new SqlParameter("@Status", Status));
    cmd.Parameters.Add(new SqlParameter("@DateEntered",
    cmd.Parameters.Add(new SqlParameter("@Customer", Customer));
    cmd.Parameters.Add(new SqlParameter("@NewID", OrderID));
    //rowsAffected = cmd.ExecuteNonQuery();
    object myobject = new object();
    myobject = cmd.ExecuteScalar(); <-------------this is
    returning null
    Stored Procedure:
    ALTER PROCEDURE [dbo].[NewInsertCommand]
    @FranchiseID nvarchar(16),
    @PrimaryVendorID int,
    @Status nvarchar(15),
    @DateEntered datetime,
    @Customer nvarchar(50),
    @NewID int OUTPUT
    begin tran
    INSERT INTO [Order] ([FranchiseID], [PrimaryVendorID], [Status],
    [DateEntered], [Customer])
    VALUES (@FranchiseID, @PrimaryVendorID, @Status, @DateEntered, @Customer);
    set @NewID = @@identity
    commit tran
    GaryDean, Jan 7, 2007
    1. Advertisements

  2. GaryDean

    Mark Rae Guest

    set @NewID = @@identity
    commit tran
    Mark Rae, Jan 7, 2007
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.