The parameterized query ... expects the parameter ... which was not supplied

Question

I was trying to create a simple data form with check boxes. Its like a duty request form with where check boxes represents each duty slot.

I'm testing it so the userID is simply selected from a dropdown list (ddlID).

Whenever the ddlID's selected index is changed, the duty data is pulled from the database and shown to the user with the form therefore user can request the available slot or release the slot if he/she have requested before. If someone has already requested a particular slot before, that particular checkbox will be disabled.

Below is the code behind of my page.

string PCS1, R1S1 = "somevalue";
    protected void Page_Load(object sender, EventArgs e)
    {
        lblDate.Text = DateTime.Now.ToLongDateString();
    }
    protected void ddlID_SelectedIndexChanged(object sender, EventArgs e)
    {
        if (IsPostBack)
        {
            SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["DBConn"].ConnectionString);
            SqlCommand cmd = sqlConn.CreateCommand();

            cmd.CommandText = "SELECT * FROM tbl_duty_location WHERE Shift = 'S1'";

            sqlConn.Open();
            SqlDataReader reader = cmd.ExecuteReader();

            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    if (reader["PC"].ToString() == "n")
                    {
                        PCS1 = "n";
                    }
                    else if (reader["PC"].ToString() != "n")
                    {
                        PCS1 = reader["PC"].ToString();
                    }

                    if (reader["R1"].ToString() == "n")
                    {
                        R1S1 = "n";
                    }
                    else if (reader["R1"].ToString() != "n")
                    {
                        R1S1 = reader["R1"].ToString();
                    }

                }
            }

            if (PCS1 == ddlID.SelectedItem.ToString())
            {
                chkPCS1.Enabled = true;
                chkPCS1.Checked = true;
            }
            else if (PCS1 == "n")
            {
                chkPCS1.Enabled = true;
                chkPCS1.Checked = false;
            }
            else if (PCS1 != ddlID.SelectedItem.ToString())
            {
                chkPCS1.Enabled = false;
                chkPCS1.Checked = false;
            }

            if (R1S1 == ddlID.SelectedItem.ToString())
            {
                chkR1S1.Enabled = true;
                chkR1S1.Checked = true;
            }
            else if (R1S1 == "n")
            {
                chkR1S1.Enabled = true;
                chkR1S1.Checked = false;
            }
            else if (R1S1 != ddlID.SelectedItem.ToString())
            {
                chkR1S1.Enabled = false;
                chkR1S1.Checked = false;
            }
            reader.Close();
            sqlConn.Close();
        }
    }
    protected void btnSubmit_Click(object sender, EventArgs e)
    {
        if (chkPCS1.Enabled == false)
        {
            Trace.Write("PCS1 is: ", PCS1);
        }
        else if (chkPCS1.Checked == false)
        {
            PCS1 = "n";
        }
        else if (chkPCS1.Checked)
        {
            PCS1 = ddlID.SelectedItem.ToString();
        }

        if (chkR1S1.Enabled == false)
        {
            Trace.Write("R1S1: ", R1S1);
        }
        else if (chkR1S1.Checked == false)
        {
            R1S1 = "n";
        }
        else if (chkR1S1.Checked)
        {
            R1S1 = ddlID.SelectedItem.ToString();
        }

        SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["DBConn"].ConnectionString);
        SqlCommand cmd = sqlConn.CreateCommand();

        cmd.CommandText = "UPDATE tbl_duty_location SET PC = @PC1, R1 = @R1 WHERE Shift = '1'";
        cmd.Parameters.AddWithValue("PC1", PCS1);
        cmd.Parameters.AddWithValue("R1", R1S1);

        sqlConn.Open();
        cmd.ExecuteNonQuery();
        sqlConn.Close();
    }

Currently, in the table, column "PC" already has userID which means that particular slot is unavailable.

However, when I tried to request another slot using different userID, I got the follow error.

The parameterized query '(@PC1 nvarchar(4000),@R1 nvarchar(9))UPDATE tbl_duty_location SE' expects the parameter '@PC1', which was not supplied.

Actually, @PC1 is supposed to be PCS1 which is loaded from the database but PCS1 immediately become null once the btn_submit click event is occurred.

What could be the problem?

bAN bAN · Accepted Answer · 2011-04-24T12:35:46

If I well understand your problem :

ASP.NET work as disconnected like other web languages. So I think your variable filled in the ddlID_SelectedIndexChanged event is gone when you triggs btnSubmit_Click

Between the two events the variables are gone.. You have multiple ways to store the values you want, in a hidden field in the page for example or in a session variable..

The parameterized query ... expects the parameter ... which was not supplied

2 Answers