Data Reader throwing object reference not set

[Sudip_inn]

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

Continue reading...