We help IT Professionals succeed at work.
Get Started

Not getting return value from SQL Stored Procedure

360 Views
Last Modified: 2012-06-27
Hi Experts,

I'm having trouble getting a return value back from a stored procedure.  I thought I previously had this working, and I don't understand why it's not now.  Attached is the relevant code.

Every time I run the code and step through the program I get an error in the attached database method on the following line of code:

result = command.ExecuteScalar().ToString();

Giving the error saying 'Object reference not set to an instance of an object.' - referring to the line of code trying to perform the ToString() functionality on a null object, because the ExecuteScalar() doesn't hold a value.

I know the stored procedure name and parameters I'm passing into the DB method are correct because the stored procedure is actually performing all the inserts correctly.  I just can't get it to return the value when it's done processing.

Can you see what I'm doing wrong?
/*** Stored Procedure ***/
ALTER PROCEDURE [dbo].[sp_NewProgram]
    -- Parameters:
    @SetID numeric(18,0),
    @Number int,
    @Name varchar(30),
    @Type varchar(10)
AS

DECLARE
    -- Local Variables:
    @ProgramID numeric(18,0)

BEGIN
	
    SET NOCOUNT ON;
	
    -- Insert new Program here:
    INSERT INTO [dbo].[Program]
    VALUES (@SetID, @Number, @Name, -1, -1, GETDATE(), '');

    -- Get the ID of the newly created program:
    -- SET @ProgramID = @@IDENTITY;
    SET @ProgramID =
    (
	SELECT [ID] FROM [dbo].[Program]
	WHERE [SetID] = @SetID
	AND [Number] = @Number
	AND [Name] = @Name
    );
	
    -- Insert 1 new stage with default values for the newly created program:
    INSERT INTO [dbo].[Stage]
    VALUES (@ProgramID, 1, @Type, '', 0, 0, 0, 0, 0, 'NONE', 0, 'AUTO', 0, 0, '', 0, '', '<0>', 0);
	
    RETURN @ProgramID;
	
    SET NOCOUNT OFF;
END


/*** C# Database Class Method ***/
public static String ExecuteScalar(String StoredProcedureName, SqlParameter[] Parameters)
{
    string result = string.Empty;
    try
    {
        connnection = new SqlConnection(connString);
        command = new SqlCommand(StoredProcedureName, connnection);
        command.CommandType = CommandType.StoredProcedure;

        foreach (SqlParameter parameter in Parameters)
            command.Parameters.Add(parameter);

        command.Connection.Open();
        result = command.ExecuteScalar().ToString();
    }
    catch (Exception ex)
    {
        string error = ex.ToString();
    }
    finally
    {
        command.Connection.Close();
        command.Dispose();
        connnection.Dispose();
    }
    return result;
}

Open in new window

Comment
Watch Question
Commented:
This problem has been solved!
Unlock 1 Answer and 6 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE