Geting Identity back from SS SP

[GaryDean]

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

 

[William \(Bill\) Vaughn]

The Identity value is being returned as an OUTPUT parameter--not as a row.
This is the correct approach. You need to set the Direction property of the
@NewID to Direction.Output and fetch the returned value by examining the
Value property post execution. See Chapter 10, page 686.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

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]

Bill
That worked great ! that's the way to do it. What book are you refering
to?

--
Regards,
Gary Blakely

The Identity value is being returned as an OUTPUT parameter--not as a row.
This is the correct approach. You need to set the Direction property of
the @NewID to Direction.Output and fetch the returned value by examining
the Value property post execution. See Chapter 10, page 686.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no
rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

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

 

[William \(Bill\) Vaughn]

The newly written "Hitchhiker's Guide to Visual Studio and SQL Server (7th
Edition)".


--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

Bill
That worked great ! that's the way to do it. What book are you refering
to?

--
Regards,
Gary Blakely

The Identity value is being returned as an OUTPUT parameter--not as a
row. This is the correct approach. You need to set the Direction property
of the @NewID to Direction.Output and fetch the returned value by
examining the Value property post execution. See Chapter 10, page 686.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no
rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

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

 

[Steven Cheng[MSFT]]

Thanks for William's input.

For further clarification, when you call stored procedure in ADO.NET and
the SP return multiple values(such as rowset by select query and other
output values like return value and output parameters), those values are
returned as multi resultset in your ADO.NET datareader. You can loop
through all the resultset in datareader to get them. Here is a former
thread discussing on this:

#fetch data from output parameter of a store procedure
http://groups.google.com/group/microsoft.public.dotnet.framework.aspnet/brow
se_thread/thread/440ea9cdda0911b0/891c257f8d015608

Hope this also helps.


Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead


This posting is provided "AS IS" with no warranties, and confers no rights.