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
--
Regards,
Gary Blakely
