unable to retrieve output parameter sql 2008

Codingitup
Codingitup used Ask the Experts™
on
Hi All,

I've got the below code that populates a dataset but has an output parameter as well. When it does to set the variable with the output parameter I get the error message: -

Object reference not set to an instance of an object.

            dt = new DataTable();
            MakeDataTable();
            SqlConnection conn = new SqlConnection("Data Source=*************)      conn.Open();
            SqlCommand cmd = new SqlCommand("Get_QuestionList", conn);
            SqlDataReader dr = null;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add(new SqlParameter("@SerialNo", txtSerialNo.Text));
            cmd.Parameters.Add(new SqlParameter("@StageID", 820));
            cmd.Parameters.Add(new SqlParameter("@MfgRoute", "MTO"));

            SqlParameter parm2 = new SqlParameter("@ErrReturn", SqlDbType.VarChar);
            parm2.Size = 255;
            parm2.Direction = ParameterDirection.Output; // This is important! 
            cmd.Parameters.Add(parm2);
            
            SqlParameter parm3 = new SqlParameter("@LoggedPartNo", SqlDbType.VarChar);
            parm3.Size = 255;
            parm3.Direction = ParameterDirection.Output; // This is important! 
            cmd.Parameters.Add(parm3);


            dr = cmd.ExecuteReader();

            LoggedPartNo = cmd.Parameters["@LoggedPartNo"].Value.ToString();

            if (dr.HasRows  == false )
            {
                MessageBox.Show("This product is not setup for prompted inspection as no questions have been assigned. Please contact your administrator");
                QuestionsFound = false;
                txtSerialNo.Text = "";
                return;
            }

Open in new window


What am I doing please?

Best Regards
Lee
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Use
dr.Close();
before  accessing the output parameters
LoggedPartNo = cmd.Parameters["@LoggedPartNo"].Value.ToString();

following code changes.
 
          dr = cmd.ExecuteReader();
            if (!dr.HasRows)
            {
                MessageBox.Show("This product is not setup for prompted inspection as no questions have been assigned. Please contact your administrator");
                QuestionsFound = false;
                txtSerialNo.Text = "";
                return;
            }
           dr.Close(); 
           LoggedPartNo = cmd.Parameters["@LoggedPartNo"].Value.ToString();

Open in new window

or
use dataadapter
Try this

            dt = new DataTable();
            MakeDataTable();
            SqlConnection conn = new SqlConnection("Data Source=*************") 
            conn.Open();
            SqlCommand cmd = new SqlCommand("Get_QuestionList", conn);
            SqlDataReader dr = null;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add(new SqlParameter("@SerialNo", txtSerialNo.Text));
            cmd.Parameters.Add(new SqlParameter("@StageID", 820));
            cmd.Parameters.Add(new SqlParameter("@MfgRoute", "MTO"));

            SqlParameter parm2 = new SqlParameter("@ErrReturn", SqlDbType.VarChar);
            parm2.Size = 255;
            parm2.Direction = ParameterDirection.Output; // This is important! 
            cmd.Parameters.Add(parm2);
            
            SqlParameter parm3 = new SqlParameter("@LoggedPartNo", SqlDbType.VarChar);
            parm3.Size = 255;
            parm3.Direction = ParameterDirection.Output; // This is important! 
            cmd.Parameters.Add(parm3);


            //dr = cmd.ExecuteReader();
            SqlDataAdapter da = new SqlDataAdapter();
            da.SelectCommand = cmd;
            da.Fill(dt);

            LoggedPartNo = cmd.Parameters["@LoggedPartNo"].Value.ToString();

            if (dt.Rows.Count == false )
            {
                MessageBox.Show("This product is not setup for prompted inspection as no questions have been assigned. Please contact your administrator");
                QuestionsFound = false;
                txtSerialNo.Text = "";
                return;
            }

Open in new window

Try this

 
           dt = new DataTable();
            MakeDataTable();
            SqlConnection conn = new SqlConnection("Data Source=*************")
            conn.Open();
            SqlCommand cmd = new SqlCommand("Get_QuestionList", conn);
            SqlDataReader dr = null;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add(new SqlParameter("@SerialNo", txtSerialNo.Text));
            cmd.Parameters.Add(new SqlParameter("@StageID", 820));
            cmd.Parameters.Add(new SqlParameter("@MfgRoute", "MTO"));

            SqlParameter parm2 = new SqlParameter("@ErrReturn", SqlDbType.VarChar);
            parm2.Size = 255;
            parm2.Direction = ParameterDirection.Output; // This is important! 
            cmd.Parameters.Add(parm2);
            
            SqlParameter parm3 = new SqlParameter("@LoggedPartNo", SqlDbType.VarChar);
            parm3.Size = 255;
            parm3.Direction = ParameterDirection.Output; // This is important! 
            cmd.Parameters.Add(parm3);


            dr = cmd.ExecuteReader();
            bool blnHasRows = dr.HasRows;
            dr.Close();

            LoggedPartNo = cmd.Parameters["@LoggedPartNo"].Value.ToString();

            if (blnHasRows == false )
            {
                MessageBox.Show("This product is not setup for prompted inspection as no questions have been assigned. Please contact your administrator");
                QuestionsFound = false;
                txtSerialNo.Text = "";
                return;
            }

Open in new window

Author

Commented:
Hi,

That works great thank you. the only issue I have now is that I had a data reader below it which does not work now: -

            while (dr.Read())
            {
                DataRow dr2 = dt.NewRow();
                dr2["QuestionID"] = dr["InspProcedureID"].ToString();
                dr2["QuestionType"] = dr["QuestionType"].ToString();
                dr2["PromptMessage"] = dr["PromptMessage"].ToString();
                dr2["ExpectedResponse"] = dr["ExpectedResponce"].ToString();
                dr2["PictureLink"] = dr["PictureLink"].ToString();
                dr2["FailMessage"] = dr["FailMessage"].ToString();
                dr2["StringStart"] = dr["StringStart"].ToString();
                dr2["StringStop"] = dr["StringStop"].ToString();
                dr2["PartNo"] = dr["PartNo"].ToString();
                dt.Rows.Add(dr2);
            }

Open in new window


Best Regards
Lee

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial