By Gary


2012-06-05 22:18:40 8 Comments

I am having a problem returning an output parameter from a Sql Server stored procedure into a C# variable. I have read the other posts concerning this, not only here but on other sites, and I cannot get it to work. Here is what I currently have. Currently I am just trying to print the value that comes back. The following code returns a null value. What I an trying to return is the primary key. I have tried using @@IDENTITY and SCOPE_INDENTITY() (i.e. SET @NewId = SCOPE_IDENTITY()).

Stored Procedure:

CREATE PROCEDURE usp_InsertContract
    @ContractNumber varchar(7),

    @NewId int OUTPUT
AS
BEGIN

    INSERT into [dbo].[Contracts] (ContractNumber)
        VALUES (@ContractNumber)

    Select @NewId = Id From [dbo].[Contracts] where ContractNumber = @ContractNumber
END

Opening the database:

pvConnectionString = "Server = Desktop-PC\\SQLEXPRESS; Database = PVDatabase; User ID = sa;
    PASSWORD = *******; Trusted_Connection = True;";

try
{
    pvConnection = new SqlConnection(pvConnectionString);
    pvConnection.Open();
}
catch (Exception e)
{
    databaseError = true;
}

Executing the command:

pvCommand = new SqlCommand("usp_InsertContract", pvConnection);

pvCommand.Transaction = pvTransaction;
pvCommand.CommandType = CommandType.StoredProcedure;    

pvCommand.Parameters.Clear();
pvCommand.Parameters.Add(new SqlParameter("@ContractNumber", contractNumber));

SqlParameter pvNewId = new SqlParameter();
pvNewId.ParameterName = "@NewId";
pvNewId.DbType = DbType.Int32;
pvNewId.Direction = ParameterDirection.Output;
pvCommand.Parameters.Add(pvNewId);

try
{
    sqlRows = pvCommand.ExecuteNonQuery();

    if (sqlRows > 0)
        Debug.Print("New Id Inserted =  ", 
            pvCommand.Parameters["@NewId"].Value.ToString()); 
    }
    catch (Exception e)
    {
        Debug.Print("Insert Exception Type: {0}", e.GetType());
        Debug.Print("  Message: {0}", e.Message);
    }
}

4 comments

@Nitin 2016-01-22 07:36:26

In your C# code, you are using transaction for the command. Just commit the transaction and after that access your parameter value, you will get the value. Worked for me. :)

@TarasB 2012-06-06 05:52:17

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.

@Kev 2012-06-07 00:09:02

This just about works as an answer, but probably should be a comment, however I can't convert it because of the multiple @ character usage (even mods are denied that right) and I can't be bothered editing it to make it work. :)

@Jeremy Thompson 2012-06-05 22:21:12

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.

enter image description here

@Rango 2012-06-05 22:25:17

But this answers not the question why the output parameter for the new id returns null. With ExecuteScalar you can retrieve a return value not an output parameter.

@Gary 2012-06-05 22:30:00

I also receive the following error when I make this change...Object reference not set to an instance of an object.

@Russ Clarke 2012-06-05 23:04:50

Thats because the Parameter is called SCOPE_IDENTITY, in SQL; C# is still looking for a parameter called @ContractNumber.

@Gary 2012-06-05 23:11:21

ContractNumber is the value being added to the table. The primary key is called Id.

@Gary 2012-06-05 23:21:42

ExecuteScalar returns an object, not an int. I receive an exception when I execute the code.

@Jeremy Thompson 2012-06-05 23:30:34

Hi Gary, I thought you'd be able to work that bit out, but I edit my answer none-the-less.

@Gary 2012-06-05 23:40:36

Sorry, I'm just trying figure this out. I've made so many updates...

@Gary 2012-06-05 23:59:14

I still get back a DBNull value....Object cannot be cast from DBNull to other types. I'm off to my other job,. I'll take this up again tomorrow. Thanks for the help!!!

@Gary 2012-06-06 22:15:56

The column does says it is an identity column

@Jeremy Thompson 2012-06-06 23:16:41

something else must be wrong because this works perfectly for me.

@Jeremy Thompson 2012-07-12 12:54:23

Sorry to delete my answer, the down vote really got to me. I know its bad form on my part and it can get you banned from answering questions. To the down voter its not cool and I would like to know why?

@marc_s 2012-06-06 05:25:23

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.

@John Suit 2014-09-16 18:30:28

Thank you! I appreciate your time in answering this for those of us Googling it years later. :-)

@Gunner 2015-01-09 12:22:10

@marc_s if the size of Varchar output is unknown then how to tackle that problem.

@user3569147 2017-05-13 23:29:45

cmd.Parameters.Add("@NewId", SqlDbType.Int).Direction = ParameterDirection.Output; needs to be altered to cmd.Parameters.AddWithValue("@NewId", SqlDbType.Int).Direction = ParameterDirection.Output; This is because it has depreciated

@marc_s 2017-05-14 07:32:29

@user3569147: most definitely NOT!! You should check out Can we stop using AddWithValue() already? and stop using .AddWithValue() - it can lead to unexpected and surprising results...

Related Questions

Sponsored Content

18 Answered Questions

[SOLVED] Function vs. Stored Procedure in SQL Server

296 Answered Questions

[SOLVED] Hidden Features of C#?

  • 2008-08-12 16:32:24
  • Serhat Ozgel
  • 650607 View
  • 1476 Score
  • 296 Answer
  • Tags:   c# hidden-features

24 Answered Questions

[SOLVED] Cast int to enum in C#

  • 2008-08-27 03:58:21
  • lomaxx
  • 1139572 View
  • 2795 Score
  • 24 Answer
  • Tags:   c# enums casting

42 Answered Questions

[SOLVED] How to create Excel (.XLS and .XLSX) file in C# without installing Ms Office?

  • 2008-09-29 22:30:28
  • mistrmark
  • 980819 View
  • 1696 Score
  • 42 Answer
  • Tags:   c# .net excel file-io

57 Answered Questions

[SOLVED] What is the difference between String and string in C#?

22 Answered Questions

[SOLVED] Search text in stored procedure in SQL Server

9 Answered Questions

[SOLVED] What are the correct version numbers for C#?

25 Answered Questions

63 Answered Questions

[SOLVED] How do I calculate someone's age in C#?

  • 2008-07-31 23:40:59
  • Jeff Atwood
  • 524038 View
  • 1743 Score
  • 63 Answer
  • Tags:   c# .net datetime

1 Answered Questions

[SOLVED] Getting value from stored procedure in another stored procedure

Sponsored Content