21

When using a Stored procedure on SQL 2008 and C# 4.0, I am unable to retrieve OUTPUT information and return info from a Select statement. I keep getting "Object reference not set to an instance of an object.". When i do ExecuteScalar() i get the rows, but not the data. Found a few examples out there and they look like what i'm doing, so i think i'm missing something simple in front of me. Thanks.

Stored procedure

USE [PhoneDb]
GO
/****** Object:  StoredProcedure [dbo].[TestPagingProcedure]    Script Date: 06/16/2011 08:39:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[TestPagingProcedure] 
    -- Add the parameters for the stored procedure here
    @startRowIndex int,
    @maximumRows int,
    @totalRows int OUTPUT


AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

DECLARE @first_id UNIQUEIDENTIFIER
DECLARE @startRow int

SET @startRowIndex =  (@startRowIndex - 1)  * @maximumRows

IF @startRowIndex = 0 
SET @startRowIndex = 1

SET ROWCOUNT @startRowIndex

SELECT @first_id = ExtensionGUID FROM ExtItem ORDER BY ExtensionGUID

PRINT @first_id

SET ROWCOUNT @maximumRows

SELECT 
ExtensionGUID, AesExt, AesHashPassword, ToNumber, AgentExt, Name, JpgImageName, BigImageName, WbmpImageName
 FROM ExtItem WHERE 
ExtensionGUID >= @first_id 
ORDER BY ExtensionGUID

SET ROWCOUNT 0

-- GEt the total rows 

SELECT @totalRows = COUNT(ExtensionGUID) FROM ExtItem

END

C# Code

 public bool GetPagedResults(string startRowIndex, string maxRows, ref double totalRowsReturned)
    {
        bool IsSuccess = false;
        string clearPassword = "";
        Log.WriteLine("GetExtList : ENTERED GETEXTITEM: ", Log.DEBUG_LEVEL.VERBOSE);
        SqlConnection MyConnection = null;
        EnDecrypt hasher = null;

        try
        {
            if (SQLLookup.DatabaseString == "")
            {
                Log.WriteLine("GetPagedResults : SQLLookup.DatabaseString is empty:", Log.DEBUG_LEVEL.VERBOSE);
                SQLLookup.SQLFinder();
                Log.WriteLine("GetPagedResults : SQL FINDER RUN: SQLLookup.DatabaseString:'" + SQLLookup.DatabaseString + "'", Log.DEBUG_LEVEL.VERBOSE);
            }

            Log.WriteLine("GetPagedResults: SQL Server '" + SQLLookup.DatabaseString + "'", Log.DEBUG_LEVEL.VERBOSE);

            _extItemList.Clear();  // Keep new records from just being appended to existing list.

            hasher = new EnDecrypt("SetMyKey", "SaltGenerator");

            // Create a Connection to SQL Server
            MyConnection = new SqlConnection(@"Data Source= " + SQLLookup.DatabaseString + @"; Initial Catalog=PhoneDb;Integrated Security=True");

            SqlCommand myCommand = new SqlCommand("TestPagingProcedure", MyConnection);
            myCommand.CommandType = CommandType.StoredProcedure;

            /* ASSIGN PARAMETERS */
            myCommand.Parameters.Add(new SqlParameter("@startRowIndex", startRowIndex));
            myCommand.Parameters.Add(new SqlParameter("@maximumRows", maxRows));
            myCommand.Parameters.Add("@totalRows", SqlDbType.Int, 4);
            myCommand.Parameters["@totalRows"].Direction = ParameterDirection.Output;


            Log.WriteLine("GetPagedResults:3 After try ", Log.DEBUG_LEVEL.VERBOSE);
            Log.WriteLine("GetPagedResults:3 startRowIndex = " + startRowIndex + "  maxRows = " + maxRows, Log.DEBUG_LEVEL.VERBOSE);
            MyConnection.Open();
            SqlDataReader Reader = myCommand.ExecuteReader();

            Log.WriteLine("GetPagedResults  BEFORE WHILE LOOP", Log.DEBUG_LEVEL.VERBOSE);
            while (Reader.Read())
            {
                /* BUILD EXT ITEM*/
                ExtItem extItem = new ExtItem();
                if (Reader.IsDBNull(0) || Reader.GetGuid(0) == Guid.Empty)
                    extItem.ExtensionGUID = Guid.Empty;
                else
                    extItem.ExtensionGUID = Reader.GetGuid(0);

                if (Reader.IsDBNull(1) || Reader.GetString(1) == "")
                    extItem.AesExt = "No value";
                else
                    extItem.AesExt = Reader.GetString(1);


                /* ADD ITEM TO LIST */
                AddItem(extItem);

                //Log.WriteLine("GetExtList extItem: " + extItem.ToString(), Log.DEBUG_LEVEL.VERBOSE);
            }

            // get the total rows 
            Log.WriteLine("GetPagedResults: New Total number of pages: " + (int)myCommand.Parameters[2].Value, Log.DEBUG_LEVEL.TERSE);
            // totalRowsReturned = myCommand.Parameters["@totalRows"];

            IsSuccess = true;

            MyConnection.Close();
            Log.WriteLine("GetPagedResults: RETURNING:", Log.DEBUG_LEVEL.VERBOSE);
        }

        catch (Exception ex)
        {
            Log.WriteLine("GetPagedResults: Unable to retrieve Extension list. Caught Exception " + ex.Message,
                Log.DEBUG_LEVEL.TERSE);
            IsSuccess = false;
        }

        MyConnection.Close();

        return IsSuccess;
    }
1
  • It is much better to use SELECT TOP(@maximumRows) ... rather than SET ROWCOUNT. This way the query optimizer knows that you only want the top rows and may generate a plan optimized for that. Commented Jun 16, 2011 at 18:26

1 Answer 1

50

According to, http://msdn.microsoft.com/en-us/library/ms971497, you must close the datareader before you process the output parameters.

Sign up to request clarification or add additional context in comments.

2 Comments

Thank you Johan, thats was what I needed to do. I was putting it after my loop, not after the close. Thank you sir!! and i will keep that in mind Remus
Thanks alot, I know the answer was here somewhere. I had just the same problem.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.