dataadapter fill missing parameter

Question

I recieve the following error when I try to execute this code. But I added it to my commands. Can someone point out the step that overlooked? Thanks.

Procedure or function 'usps_getContactDetails' expects parameter '@aspContactID', which was not supplied.

SqlConnection conn = new SqlConnection(GetConnString()); SqlCommand cmd = new SqlCommand("usps_getContactDetails", conn);

        SqlParameter parmContactID = new SqlParameter("@aspContactID", Convert.DBNull);
        cmd.Parameters.Add(parmContactID);
        parmContactID.Direction = ParameterDirection.Input;

        SqlDataAdapter da = new SqlDataAdapter();
        da.SelectCommand = cmd;

        conn.Open();
        DataSet cusDS = new DataSet();
        da.Fill(cusDS, "Contacts");

Answer 1

When doing a SqlCommand and calling a stored procedure, you need to implicity set your SqlCommand to be a StoredProcedure.

using(SqlConnection con = new SqlConnection(""))
{
  //Set up your command
  SqlCommand cmd = new SqlCommand("[Procedure]", con);
  cmd.CommandType = CommandType.StoredProcedure;
  //Add your parameters
  cmd.Parameters.AddWithValue("@aspContactID", "");
  //Declare your data adapter
  SqlDataAdapter sda = new SqlDataAdapter(cmd);
  DataSet ds = new DataSet();
  sda.Fill(ds, "Contacts");
}

Follow the above format and you should be fine. Your procedure isn't working for one of two reasons, you were missing the line of code which makes your code work in this case is cmd.CommandType = CommandType.StoredProcedure; or because your parameter is DBNull the procedure says it doesn't have any recognition of that parameter. If you have a parameter which can be null or empty in a stored procedure then do the following:

Create Procedure [dbo].[Example]

@Test as Varchar(100) = ''

As

Answer 2

    protected void Page_Load(object sender, EventArgs e)
    {

    }
    private void OpenCon()
    {
        con = new SqlConnection(ConfigurationManager.ConnectionStrings["DbPrepConnectionString"].ConnectionString.ToString());
        try
        {
            con.Open();
        }
        catch (Exception ex)
        {
            Response.Write(ex.Message);
        }
    }



    private void SubmitData()
    {
        OpenCon();
        string sp = "sp_InsertRecord";
        cmd = new SqlCommand(sp, con);

        cmd.CommandType = CommandType.StoredProcedure;
        //add parameters...
        cmd.Parameters.Add(new SqlParameter("@Name", SqlDbType.VarChar, 50));
        cmd.Parameters.Add(new SqlParameter("@UserId", SqlDbType.Int));
        cmd.Parameters.Add (new SqlParameter ("@ProductName",SqlDbType .VarChar,50));
        cmd.Parameters.Add(new SqlParameter("@Price", SqlDbType.Money));

        //set paarameters....
        cmd.Parameters["@Name"].Value = txtName.Text.ToString();
        cmd.Parameters["@UserId"].Value = txtUserId.Text.ToString();
        cmd.Parameters["@ProductName"].Value = txtProductName.Text.ToString();
        cmd.Parameters["@Price"].Value = txtPrice.Text.ToString();
        cmd.ExecuteNonQuery();
        lblMessage.Text = "data inserted successfully";
    }

    protected void btnSubmit_Click(object sender, EventArgs e)
    {
        SubmitData();
    }

    private void FindData()
    {
        OpenCon();
        string s = "sp_FindRecord";
        cmd = new SqlCommand(s, con);
        cmd.Parameters.Add(new SqlParameter("@Id", SqlDbType.Int));
        cmd.Parameters["@Id"].Value = txtName.Text.ToString();
        cmd.CommandType = CommandType.StoredProcedure;
        ad = new SqlDataAdapter(cmd);
        ds = new DataSet();
        ad.Fill(ds);
        dt = ds.Tables[0];
        currow = 0;

        FillControls();

    }

    private void FillControls()
    {
        txtOrderId.Text = dt.Rows[currow].ItemArray[0].ToString();
        txtUserId.Text = dt.Rows[currow].ItemArray[1].ToString();
        txtProductName.Text = dt.Rows[currow].ItemArray[2].ToString();
        txtPrice.Text = dt.Rows[currow].ItemArray[3].ToString();
    }

    protected void btnFind_Click(object sender, EventArgs e)
    {
        FindData();
    }

}

}'