C# The parameterized query X expects the parameter Y which was not supplied

Question

I have some problems when trying to update values in my SQL Server database.

I have the following DataGridView:

DataGridView-Data

I want to change the checkbox then write the boolean back to SQL Server when I hit the save button below.

I added the BindingSource to the SQL Server table with the designer. That works fine. It reads the values from SQL and shows them.

The save button contains the following code:

private void cmdChange_Click(object sender, EventArgs e)
{
    var PID = new SqlParameter("@PID",SqlDbType.Int);
    var Programm = new SqlParameter("@Installed",SqlDbType.Int);

    string prgUpdate = "UPDATE Programme SET Installed=@Installed WHERE PID=@PID";

    using (SqlConnection update = new SqlConnection(altdbconnect))
    {
        update.Open();

        for (int i = 0; i < dGVUpdates.Rows.Count; i++)
        {
            using (SqlCommand insert3 = new SqlCommand(prgUpdate, update))
            {
                insert3.Parameters.AddWithValue("@PID", dGVUpdates.Rows[i].Cells["pIDDataGridViewTextBoxColumn1"].Value);
                insert3.Parameters.AddWithValue("@Installed", dGVUpdates.Rows[i].Cells["installedDataGridViewTextBoxColumn"].Value);
                insert3.ExecuteNonQuery();
            }
        }

        update.Close();
    }

    dGVUpdates.Update();
    dGVUpdates.Refresh();
}

But when I hit the button to save the values I changed in the DGV, I got the following error:

Additional information: The parameterized query '(@PID nvarchar(4000),@Installed nvarchar(4000))UPDATE Programme ' expects the parameter '@PID', which was not supplied.

I compared all used statements, to another DataGridView. They look the same. And like in the linked picture shown, there is a parameter @PID set.

The column pIDDataGridViewTextBoxColumn1 was generated by the designer. This is the column for PID.

I compared it to this one that is working fine. Can't find the difference:

var kdNummer = new SqlParameter("@Kundennummer", SqlDbType.Int);
var kdName = new SqlParameter("@Kundenname", SqlDbType.VarChar);
var kdMail = new SqlParameter("@Kundenmail", SqlDbType.VarChar);
var kdTele = new SqlParameter("@Telefon", SqlDbType.VarChar);
var kdOffen = new SqlParameter("@Offen", SqlDbType.Int);

string kdquery = "INSERT INTO gfcKunden VALUES (@Kundennummer, @Kundenname, @Kundenmail, @Telefon, @Offen)";

using (SqlConnection updatedb = new SqlConnection(altdbconnect))
{
    updatedb.Open();

    for (int i = 0; i < dataGridView1.Rows.Count; i++)
    {
        try
        {
            using (SqlCommand insert = new SqlCommand(kdquery, updatedb))
            {
                insert.Parameters.AddWithValue("@Kundenname", dataGridView1.Rows[i].Cells["Kundenname"].Value);
                insert.Parameters.AddWithValue("@Kundennummer", dataGridView1.Rows[i].Cells["Kundennummer"].Value);
                insert.Parameters.AddWithValue("@Kundenmail", dataGridView1.Rows[i].Cells["Kundenmail"].Value);
                insert.Parameters.AddWithValue("@Telefon", dataGridView1.Rows[i].Cells["Telefon"].Value);
                insert.Parameters.AddWithValue("@Offen", 0);

                insert.ExecuteNonQuery();
            }
        }
        catch { }
    }

    updatedb.Close();
}

Screenshot from the SQL Server table:

SQL-Table

may be your `dGVUpdates.Rows[i].Cells["pIDDataGridViewTextBoxColumn1"].Value` is empty? — SᴇM, May 19 '16 at 15:25
Why do you keep creating parameter variables and then ignoring them? — LarsTech, May 19 '16 at 15:29
And your error message looks strange..Did you edit or is this error message original one generated by debugger? — Kay Lee, May 20 '16 at 00:03
No, the error message is not edited by me. Thats how its generated by debugger. — JasonX2000, May 20 '16 at 06:04

Kay Lee · Accepted Answer · 2016-05-19T23:56:11.070

As commented by LarsTech, all the lines of var declaration are meaningless. This means below lines don't play any role.

var PID = new SqlParameter("@PID",SqlDbType.Int);
var Programm = new SqlParameter("@Installed",SqlDbType.Int);

However, above change might not influence the result.

But, as you can see, you're bit confused of DataType. In your error message, there's @PID nvarchar(4000). This means the column PID is designed to use text data. But you're declaring it as Int as above. Id is usually numbers but your SQL database PID column is designed as nvarchar in the past by someone. However, C# language can easily convert numbers to text and text to numbers. It's no problem.

And I recommend to use more specifically like,

insert3.Parameters.Add(new SqlParameter("@PID", SqlDbType.NVarChar));
insert3.Parameters["@PID"].Value= dGVUpdates.Rows[i].Cells["pIDDataGridViewTextBoxColumn1"].Value.ToString();

First, look into what DataTypes are used for each columns and specifically use right DataTypes respectively.

And additionally, you need to confirm the kdNummer column because you declare(but meaningless again) kdNummer as Int as of your code but you're inserting as text. If you want to convert the text value into Int numbers, you need to do this.

insert.Parameters.AddWithValue("@Kundennummer", Convert.ToInt32(dataGridView1.Rows[i].Cells["Kundennummer"].Value));

Hope this helps..

Hi, i added a screenshot from the sql table. The PID is declared as integer and as primary key. — JasonX2000, May 20 '16 at 06:09
I added the Convert.ToInt32 statement, that helped. You where right, i was confused, because i declared the SQL-Table as Integer and Primary Key but the error said the parameter was nvarchar(4000). Is it posible, that the designer messed something up when reading the data from SQL? Like i said, the first time i used the parameters they worked well. The difference is only, i populated the first DGV by code and not by designer. — JasonX2000, May 20 '16 at 06:19
I saw the screenshot. I only use codes and not familiar with designer. If you want to use designer, it seems you need to understand how designer works. I also upvoted your question. Have good day ! — Kay Lee, May 20 '16 at 07:25

C# The parameterized query X expects the parameter Y which was not supplied

1 Answers1