none
Data Reader throwing object reference not set RRS feed

  • Question

  • in my store proc i have begin Tran & commit tran and with in it i have many insert statement to insert data in table.

    after commit i have join data and populate result into ## temporary table and at end i write like select * from ## temporary table

    so when i execute that store procedure by data reader then i am getting error like object reference not set

    this is my sample code

    try
                {
                    // Calling store proc to insert data into db
                    //DataTable table = new DataTable();
                    SqlDataReader reader = null;
    
                    using (SqlConnection con = new SqlConnection(sConnectionString))
                    {
                        using (SqlCommand cmd = new SqlCommand("USP_InsertDataFromXML", con))
                        using (var da = new SqlDataAdapter(cmd))
                        {
                            cmd.CommandTimeout = 240; // Setting command timeout to 2 minutes
                            cmd.CommandType = CommandType.StoredProcedure;
                            cmd.Parameters.Add("@IPAddress", SqlDbType.VarChar).Value = IPAddress;
                            cmd.Parameters.Add("@LIConfigXML", SqlDbType.Xml).Value = (LIConfigXML.Trim() == "" ? null : LIConfigXML.Trim());
                            cmd.Parameters.Add("@10QKXML", SqlDbType.Xml).Value = (_10QKXML.Trim() == "" ? null : _10QKXML.Trim());
                            cmd.Parameters.Add("@CustomFormula", SqlDbType.Xml).Value = (CustomFormulaXML.Trim() == "" ? null : CustomFormulaXML.Trim());
                            cmd.Parameters.Add("@Comments", SqlDbType.Xml).Value = (CommentXML.Trim() == "" ? null : CommentXML.Trim());
                            cmd.Parameters.Add("@ToggleData", SqlDbType.Xml).Value = (TransFormedToggleDataXML.Trim() == "" ? null : TransFormedToggleDataXML.Trim());
                            cmd.Parameters.Add("@BrokerHistoryOptionXML", SqlDbType.Xml).Value = (BrokerHistoryOptionXML.Trim() == "" ? null : BrokerHistoryOptionXML.Trim());
                            cmd.Parameters.Add("@BrokerGridXML", SqlDbType.Xml).Value = (BrokerGridHistXML.Trim() == "" ? null : BrokerGridHistXML.Trim());
                            cmd.Parameters.Add("@PrevEarningXML", SqlDbType.Xml).Value = (PrevEarningXML.Trim() == "" ? null : PrevEarningXML.Trim());
                            cmd.Parameters.Add("@BrokerBogey", SqlDbType.NVarChar).Value = (BrokerBogeyXML.Trim() == "" ? null : BrokerBogeyXML.Trim());
                            cmd.Parameters.Add("@PeriodListXML", SqlDbType.Xml).Value = (PeriodListXML.Trim() == "" ? null : PeriodListXML.Trim());
                            cmd.Parameters.Add("@existHist", SqlDbType.VarChar).Value = existHist;
                            cmd.Parameters.Add("@existHistFY", SqlDbType.VarChar).Value = existHistFY;
                            cmd.Parameters.Add("@lblEarning", SqlDbType.VarChar).Value = lblEarning;
    
    
                            SqlParameter outPutParameter = new SqlParameter();
                            outPutParameter.ParameterName = "@STATUS";
                            outPutParameter.SqlDbType = System.Data.SqlDbType.VarChar;
                            outPutParameter.Direction = System.Data.ParameterDirection.Output;
                            outPutParameter.Size = 200;
                            cmd.Parameters.Add(outPutParameter);
    
                            con.Open();
                            cmd.ExecuteNonQuery();
                            reader = cmd.ExecuteReader();
                            output = outPutParameter.Value.ToString();
    
                            if (output.Contains("SUCCESSFULLY"))
                            {
                                retvalue = true;
                                //reader = cmd.ExecuteReader();
    
                                if (reader.HasRows)
                                {
                                    while (reader.Read())
                                    {
                                        
                                    }
                                }
                                reader.Dispose();
                            }
                            else
                            {
                                retvalue = false;
                            }
                        }
                    }
                }
                catch (Exception ex)
                {
                    //MessageBox.Show("Error " + ex.Message.ToString());
                }
                finally
                {
    
                }

    please tell me where i made the mistake in my c# code which causes object reference not set.

    tell me should i create two store procedure one will insert data into table and second SP which will select data from ## temporary table ?

    can i work with data reader & #temporary table i mean local temporary table instead of global temporary table...is it possible?

    please guide me to fix the error. thanks

    Wednesday, August 7, 2019 1:48 PM

Answers

  • You only call an `Execute` method once. Here's the rules

    - ExecuteNonQuery : Called when either it returns no values or you don't care about the result (e.g. update, delete)

    - ExecuteScalar : Called when you want the first value from the first row, if any (e.g. insert that returns the PK of the inserted row or calling a sproc that has a RETURN statement)

    - ExecuteReader: Called to stream the results of a query back to the client (e.g. select)

    Since your sproc returns results that you want then you need to use ExecuteReader. After the reader has been closed you can then get the output parameter value (not until).

    using (reader = cmd.ExecuteReader())
    {
       while (reader.Read())
       {
           //Get fields from current row
       };
    };
    
    //Can now read output/inputoutput parameter values 
    var result = outputParameter.Value;


    Michael Taylor http://www.michaeltaylorp3.net

    • Marked as answer by Sudip_inn Tuesday, August 13, 2019 7:15 AM
    Friday, August 9, 2019 1:28 PM
    Moderator

All replies

  • Can you more specifically tell about the line on which you are getting exception or post the stack trace of exception, currently it's hard to figure out where something went wrong. You can also attach debugger and check which object is null and causing the exception.

    [If a post helps to resolve your issue, please click the "Mark as Answer" of that post or click Answered "Vote as helpful" button of that post. By marking a post as Answered or Helpful, you help others find the answer faster. ]


    Blog | LinkedIn | Stack Overflow | Facebook
    profile for Ehsan Sajjad on Stack Exchange, a network of free, community-driven Q&A sites

    Wednesday, August 7, 2019 2:47 PM
  • Output parameters don't have their Value set until the reader closes. So you need to close the reader before trying to extract the output parameter's value.

    //Don't do both - use ExecuteNonQuery if you don't care about the resultset
    cmd.ExecuteNonQuery();
    /*using (reader = cmd.ExecuteReader())
    {
    };*/
    
    //Handle no output
    output = outPutParameter.Value?.ToString() ?? "";


    Michael Taylor http://www.michaeltaylorp3.net

    Wednesday, August 7, 2019 2:58 PM
    Moderator
  • MY same store proc doing inserting data in table and also at the return data from ## temporary table 

    so i need to read data return from ## temporary table and that is why i use both execute query and and data reader.

    using (reader = cmd.ExecuteReader())
    {
    
    };

    Thursday, August 8, 2019 7:21 AM
  • you said attach debugger which debugger you are talking about ?
    Thursday, August 8, 2019 7:21 AM
  • You don't use both query calls. You are executing the sproc twice if you do that. If you expect results to come back from a query (not output parameters) then use ExecuteReader. Don't call the `Execute` methods multiple times unless you want to run the query multiple times. Irrelevant of which `Execute` method you use inputoutput/output parameter values can be read after the command completes. For ExecuteReader that means when the reader is closed. For the others it is available immediately.

    You keep mentioning a temp table but there is no such logic in your query. Your code is calling a sproc. What that sproc does internally is completely irrelevant. Whether it is selecting from a real table, temp table, table variable or anything else doesn't matter to the calling code. That's the whole benefit of a sproc. The only thing the sproc needs to do is ultimately select from something so the results are returned to your code.

    If your sproc doesn't have a select clause that returns results then ExecuteReader isn't useful as you'll never get anything. Use ExecuteNonQuery. This is standard for update and delete requests. If you're inserting data then generally the PK of the inserted row is returned. That is where ExecuteScalar comes in as it returns the first column from the first result (or the RETURN if a sproc has one). If you're querying data then ExecuteReader is used to enumerate the results.


    Michael Taylor http://www.michaeltaylorp3.net

    Thursday, August 8, 2019 1:51 PM
    Moderator
  • My store procedure 

    first set value to output type variable and next statement is select * from ##temp1

    using c# code i need to read output variable value and as well as data return from select * from ##temp1

    so tell me do i need to call executenonquery & execute reader both or execute reader is sifficient only to read value from select query & output variable data?

    if still not clear then let me know i will provide a sample code of my store proc then you can understand what i am trying to achieve. thanks

    Friday, August 9, 2019 6:54 AM
  • Hi Sudip_inn, 

    Thank you for posting here.

    For your question, Could you provide some code about your store proc?

    Besides, please provide more details about your exception with which line of the code thrown the exception and some code about ‘first set value to output type variable’? It will help us to do the test.

    We are waiting for your update.

    Best Regards,

    Xingyu Zhao


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, August 9, 2019 8:55 AM
    Moderator
  • You only call an `Execute` method once. Here's the rules

    - ExecuteNonQuery : Called when either it returns no values or you don't care about the result (e.g. update, delete)

    - ExecuteScalar : Called when you want the first value from the first row, if any (e.g. insert that returns the PK of the inserted row or calling a sproc that has a RETURN statement)

    - ExecuteReader: Called to stream the results of a query back to the client (e.g. select)

    Since your sproc returns results that you want then you need to use ExecuteReader. After the reader has been closed you can then get the output parameter value (not until).

    using (reader = cmd.ExecuteReader())
    {
       while (reader.Read())
       {
           //Get fields from current row
       };
    };
    
    //Can now read output/inputoutput parameter values 
    var result = outputParameter.Value;


    Michael Taylor http://www.michaeltaylorp3.net

    • Marked as answer by Sudip_inn Tuesday, August 13, 2019 7:15 AM
    Friday, August 9, 2019 1:28 PM
    Moderator