Using stored procedure output parameters in C#
I slightly modified your stored procedure (to use SCOPE_IDENTITY
) and it looks like this:
CREATE PROCEDURE usp_InsertContract
@ContractNumber varchar(7),
@NewId int OUTPUT
AS
BEGIN
INSERT INTO [dbo].[Contracts] (ContractNumber)
VALUES (@ContractNumber)
SELECT @NewId = SCOPE_IDENTITY()
END
I tried this and it works just fine (with that modified stored procedure):
// define connection and command, in using blocks to ensure disposal
using(SqlConnection conn = new SqlConnection(pvConnectionString ))
using(SqlCommand cmd = new SqlCommand("dbo.usp_InsertContract", conn))
{
cmd.CommandType = CommandType.StoredProcedure;
// set up the parameters
cmd.Parameters.Add("@ContractNumber", SqlDbType.VarChar, 7);
cmd.Parameters.Add("@NewId", SqlDbType.Int).Direction = ParameterDirection.Output;
// set parameter values
cmd.Parameters["@ContractNumber"].Value = contractNumber;
// open connection and execute stored procedure
conn.Open();
cmd.ExecuteNonQuery();
// read output value from @NewId
int contractID = Convert.ToInt32(cmd.Parameters["@NewId"].Value);
conn.Close();
}
Does this work in your environment, too? I can't say why your original code won't work - but when I do this here, VS2010 and SQL Server 2008 R2, it just works flawlessly....
If you don't get back a value - then I suspect your table Contracts
might not really have a column with the IDENTITY
property on it.
Before changing stored procedure please check what is the output of your current one. In SQL Server Management run following:
DECLARE @NewId int
EXEC @return_value = [dbo].[usp_InsertContract]
N'Gary',
@NewId OUTPUT
SELECT @NewId
See what it returns. This may give you some hints of why your out param is not filled.
Stored Procedure.........
CREATE PROCEDURE usp_InsertContract
@ContractNumber varchar(7)
AS
BEGIN
INSERT into [dbo].[Contracts] (ContractNumber)
VALUES (@ContractNumber)
SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY]
END
C#
pvCommand.CommandType = CommandType.StoredProcedure;
pvCommand.Parameters.Clear();
pvCommand.Parameters.Add(new SqlParameter("@ContractNumber", contractNumber));
object uniqueId;
int id;
try
{
uniqueId = pvCommand.ExecuteScalar();
id = Convert.ToInt32(uniqueId);
}
catch (Exception e)
{
Debug.Print(" Message: {0}", e.Message);
}
}
EDIT: "I still get back a DBNull value....Object cannot be cast from DBNull to other types. I'll take this up again tomorrow. I'm off to my other job,"
I believe the Id column in your SQL Table isn't a identity column.