CODEWITHSUNDEEP
c#sql-serverdatagridviewparameters
JasonX2000
JasonX2000

Reputation: 35

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

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

Upvotes: 3

Views: 1432

Answers (1)

Kay Lee
Kay Lee

Reputation: 952

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..

Upvotes: 1

Related Questions