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 =
    (string)HttpContext.Current.Session["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",
    PrimaryVendorID));
    cmd.Parameters.Add(new SqlParameter("@Status", Status));
    cmd.Parameters.Add(new SqlParameter("@DateEntered",
    DateTime.Today));
    cmd.Parameters.Add(new SqlParameter("@Customer", Customer));
    cmd.Parameters.Add(new SqlParameter("@NewID", OrderID));
    try
    {
    conn.Open();
    //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
    )
    AS
    begin tran
    SET NOCOUNT OFF;
    INSERT INTO [Order] ([FranchiseID], [PrimaryVendorID], [Status],
    [DateEntered], [Customer])
    VALUES (@FranchiseID, @PrimaryVendorID, @Status, @DateEntered, @Customer);
    set @NewID = @@identity
    commit tran
     
    GaryDean, Jan 7, 2007
    #1
    1. Advertisements

  2. GaryDean

    Mark Rae Guest

    set @NewID = @@identity
    commit tran
    SELECT @NewID
     
    Mark Rae, Jan 7, 2007
    #2
    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.