Avatar of Codingitup
Codingitup
 asked on

unable to retrieve output parameter sql 2008

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
C#

Avatar of undefined
Last Comment
Codingitup

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
sachinpatil10d

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
sachinpatil10d

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

Codingitup

ASKER
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
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck