Return newly created stored procedure C#

Question

I've tried two different thoughts in resolving this issue without success. I've look at the following links here, here and here but I end up with

Object reference not set to an instance of an object.

or

Procedure or function InsertHero has too many arguments specified.

When I execute the stored procedure in SSMS, my IDs also appear off in that they are incrementing from 1110 instead of the original number set at 100 (this might be a different issue).

It seems like I'm 99% of the way there and I'm missing a small piece. What am I missing?

C#:

using (SqlConnection connection = new SqlConnection(ConnectionString))
{
    connection.Open();

    using (SqlCommand command = new SqlCommand("InsertHero", connection))
    {
        command.CommandType = CommandType.StoredProcedure;

        command.Parameters.AddWithValue("@Name", hero.Name);
        command.Parameters.AddWithValue("@Universe", hero.Universe);
        // command.Parameters.Add("@Created_Id", SqlDbType.Int).Direction = ParameterDirection.Output;

        int id = (int)command.ExecuteScalar();
    }
}

Stored procedure:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[InsertHero] 
    @Name nvarchar(50), 
    @Universe nvarchar(15), 
    @Created_Id int OUTPUT
AS
    INSERT INTO Hero
    VALUES (@Name, @Universe);

    SET @Created_Id = SCOPE_IDENTITY()
GO

Stored procedure (different way):

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[InsertHero] 
    @Name nvarchar(50), 
    @Universe nvarchar(15) OUTPUT
AS
    INSERT INTO Hero
    OUTPUT inserted.Id
    VALUES (@Name, @Universe);
GO

EDIT:

Leaving this here in case someone stumbles upon it. Turned out to be a problem with the connection string so be sure to double and maybe triple check

You should check out [Can we stop using AddWithValue() already?](http://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/) and stop using `.AddWithValue()` - it can lead to unexpected and surprising results... — marc_s, Apr 25 '20 at 18:15

Steve · Accepted Answer · 2020-04-25T18:57:23.580

3

There is no need for the @CreatedID parameter in the first example. Just change your stored procedure to

ALTER procedure [dbo].[InsertHero] @Name nvarchar(50), @Universe nvarchar(15)
as
   Insert into Hero  values (@Name, @Universe);
   SELECT SCOPE_IDENTITY()

When you call ExecuteScalar the result is expected as the return from a SELECT statement not from an output parameter. If you want to use an output paramenter then you need to add it to the SqlCommand parameters collection and read it back its value from the same collection after executing the command.

Also do not use AddWithValue, this method while handy has numerous drawbacks documented in Can we Stop using AddWithValue Already and also in How data access code affects Database performances

Instead use Add specifying type and size of the two parameters

using (SqlConnection connection = new SqlConnection(ConnectionString))
{
    connection.Open();
    using (SqlCommand command = new SqlCommand("InsertHero", connection))
    {
        command.CommandType = CommandType.StoredProcedure;
        command.Parameters.Add("@Name", SqlDbType.NVarChar, 50).Value = hero.Name;
        command.Parameters.Add("@Universe", SqlDbType.NVarChar, 15).Value = hero.Universe;
        int id = (int)command.ExecuteScalar();
    }
}

The second method should work as well if you change back the @Universe parameter to the same definition of the first example (No output clause).

ALTER procedure [dbo].[InsertHero] @Name nvarchar(50), @Universe nvarchar(15)

edited Apr 25 '20 at 18:57

answered Apr 25 '20 at 18:02

Steve

213,761
22
232
286

thanks for your response. Strange, I'm still seeing "Object reference not set to an instance of an object." Could there be something else affecting this? Also, thanks for the article on AddWithValues. Learn something new everyday – SPQR Apr 25 '20 at 18:26
On which line do you get that error?. The C# code above seems to be correct, unless the variable _hero_ itself is null – Steve Apr 25 '20 at 18:52
Added another article that explains another important detail about the ill nature of AddWithValue – Steve Apr 25 '20 at 18:58
Error --> Assigning the result of ExecuteScalar to id – SPQR Apr 25 '20 at 18:59
Did you check if the data has been inserted ? – Steve Apr 25 '20 at 19:02
Also, I can confirm the hero is not null and can see the values when stepping through the code – SPQR Apr 25 '20 at 19:02
Interesting, no the data is not inserted when call the stored procedure from C#. When I execute it from SSMS, the data is inserted – SPQR Apr 25 '20 at 19:03
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/212497/discussion-between-steve-and-spqr). – Steve Apr 25 '20 at 19:03

Return newly created stored procedure C#

1 Answers1