Link to home
Start Free TrialLog in
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
ASKER CERTIFIED SOLUTION
Avatar of sachinpatil10d
sachinpatil10d
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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

Avatar of Codingitup
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