the parameterized query expects the parameter which was not supplied c#

Question

I'm trying to build an application for a fictional music store, but I keep getting this on the @Voornaamklant and @Achternaamklant:

parameterized query expects the parameter which was not supplied

I am retrieving the data from my dataGridView2, which gets its values from another dataGridView and a few TextBoxes. Then what I want to do here is to store everything in dataGridView2 into my database table 'Factuur'.

I have 9 rows in my dataGridView2 and all 9 are coming through in my database correctly. However, I have 10 rows in my table since my first column is an ID Auto_Incremented field, maybe that's something? I dont really think that is causing it though.

Here is my code:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Globalization;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace NieuwefactuurV2
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
            this.dateTimePicker1.Value = DateTime.Now;
        }

        private void afsluitenToolStripMenuItem_Click(object sender, EventArgs e)
        {
            Close();
        }

        private void dataGridView1_SelectionChanged(object sender, EventArgs e)
        {
            DataGridViewRow dr = dataGridView1.SelectedRows[0];
            Titel.Text = dr.Cells["titelDataGridViewTextBoxColumn"].Value.ToString();
            Prijs.Text = dr.Cells["prijsDataGridViewTextBoxColumn"].Value.ToString();
        }

        private void Aantal_TextChanged(object sender, EventArgs e)
        {
            try
            {
                double a = Convert.ToDouble(Aantal.Text);
                double b = Convert.ToDouble(Prijs.Text);

                Subtotaal.Text = (a * b).ToString();
            }

            catch
            {
            }
        }

        private void Prijs_TextChanged(object sender, EventArgs e)
        {
            try
            {
                double a = Convert.ToDouble(Aantal.Text);
                double b = Convert.ToDouble(Prijs.Text);
                double c = Convert.ToDouble(Subtotaal.Text);
                double d = Convert.ToDouble(Korting.Text);

                Subtotaal.Text = (a * b).ToString();
            }

            catch
            {
            }
        }

        private void Subtotaal_TextChanged(object sender, EventArgs e)
        {
            try
            {
                double a = Convert.ToDouble(Aantal.Text);
                double b = Convert.ToDouble(Prijs.Text);
                double c = Convert.ToDouble(Subtotaal.Text);
                double d = Convert.ToDouble(Korting.Text);
                if (d >= 1 && d < 100)
                {
                    Totaal.Text = (c - (c / 100 * d)).ToString();
                }
                if (d <= 0)
                {
                    Totaal.Text = (c).ToString();
                }
                if (d >= 100)
                {
                    Totaal.Text = (0).ToString();
                }
            }

            catch
            {
            }
        }

        private void Korting_TextChanged(object sender, EventArgs e)
        {
            try
            {
                double a = Convert.ToDouble(Aantal.Text);
                double b = Convert.ToDouble(Prijs.Text);
                double c = Convert.ToDouble(Subtotaal.Text);
                double d = Convert.ToDouble(Korting.Text);

                if (d >= 1 && d < 100)
                {
                    Totaal.Text = (c - (c / 100 * d)).ToString();
                }
                if (d <= 0)
                {
                    Totaal.Text = (c).ToString();
                }
                if (d >= 100)
                {
                    Totaal.Text = (0).ToString();
                }
            }

            catch
            {
            }
        }

        private void Add_Click(object sender, EventArgs e)
        {
            try
            {
                string str;
                string titel = Titel.Text;
                string voorupdate;
                str = dataGridView1.Rows[dataGridView1.SelectedRows[0].Index].Cells[4].Value.ToString();
                int a = Convert.ToInt32(str);
                int b = Convert.ToInt32(Aantal.Text);
                voorupdate = (a - b).ToString();
                if (a - b >= 0)
                {
                    Print.Enabled = true;
                    string vnaam = Voornaam.Text;
                    string anaam = Achternaam.Text;
                    string lpcd = LPCD.Text;
                    string dateColumn = dateTimePicker1.Text;
                    string firstColumn = Titel.Text;
                    string secondColumn = Aantal.Text;
                    string thirdColumn = Prijs.Text;
                    string fourthColumn = Subtotaal.Text;
                    string fifthColumn = Korting.Text;
                    string sixthColumn = Totaal.Text;
                    string[] row = { vnaam, anaam, firstColumn, secondColumn, thirdColumn, fourthColumn, fifthColumn, sixthColumn, lpcd, dateColumn };
                    dataGridView2.Rows.Add(row);



                    string cmd = "Update Album set [Actuele Voorraad]='" + voorupdate + "' where Titel='" + Titel.Text + "'";
                    using (SqlConnection connection = new SqlConnection("Data Source=Evan-PC;Initial Catalog=DatabaseProject;Integrated Security=True"))
                    {

                        using (SqlCommand command1 = new SqlCommand(cmd, connection))
                        {
                            connection.Open();
                            command1.ExecuteNonQuery();
                        }
                    }
                }
                else
                {
                    Print.Enabled = false;
                    MessageBox.Show("Fout");
                }
            }
            catch
            {
                MessageBox.Show("Vul waarden in");
            }
        }

        private void Remove_Click(object sender, EventArgs e)
        {
            string str;
            string str2;
            string titel;
            string voorupdate;

            titel = dataGridView2.Rows[dataGridView2.SelectedRows[0].Index].Cells[2].Value.ToString();
            str2 = dataGridView1.Rows[dataGridView1.SelectedRows[0].Index].Cells[5].Value.ToString();
            str = dataGridView2.Rows[dataGridView2.SelectedRows[0].Index].Cells[3].Value.ToString();

            int a = Convert.ToInt32(str2);
            int b = Convert.ToInt32(str);
            voorupdate = ((a + b) - b).ToString();


            string cmd = "Update Album set [Actuele Voorraad]='" + voorupdate + "' where Titel='" + titel + "'";
            using (SqlConnection connection = new SqlConnection("Data Source=Evan-PC;Initial Catalog=DatabaseProject;Integrated Security=True"))
            {

                using (SqlCommand command1 = new SqlCommand(cmd, connection))
                {
                    connection.Open();
                    command1.ExecuteNonQuery();
                }
            }



            if (this.dataGridView2.SelectedRows.Count > 0)
            {
                dataGridView2.Rows.RemoveAt(this.dataGridView2.SelectedRows[0].Index);
            }
            else
            {
            }
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            // TODO: This line of code loads data into the 'databaseProjectDataSet.Album' table. You can move, or remove it, as needed.
            this.albumTableAdapter1.Fill(this.databaseProjectDataSet.Album);
            // TODO: This line of code loads data into the 'database_ProjectDataSet2.Album' table. You can move, or remove it, as needed.
            this.albumTableAdapter.Fill(this.database_ProjectDataSet2.Album);
        }

        private void Print_Click(object sender, EventArgs e)
        {
            try
            {
                string mail = Email.Text;
                string voor = Voornaam.Text;
                string achter = Achternaam.Text;
                string adres = Adres.Text;
                string woon = Woonplaats.Text;
                string post = Postcode.Text;
                string tele = Telefoonnummer.Text;
                try
                {
                    string addcity = "If Not Exists(select * from Woonplaats where Woonplaats='@woon') Begin insert into Woonplaats (Woonplaats)" + "Values('" + woon + "')END";

                    string dataquery = "INSERT INTO Klant([E-mail], Voornaam, Achternaam, Adres, Woonplaats, Postcode, Telefoonnummer) " +
                                       "Values('" + mail + "', '" + voor + "', '" + achter + "', '" + adres + "', '" + woon + "', '" + post + "', '" + tele + "')";

                    using (SqlConnection connection = new SqlConnection("Data Source=Evan-PC;Initial Catalog=DatabaseProject;Integrated Security=True"))
                    {

                        using (SqlCommand command1 = new SqlCommand(addcity, connection))
                        using (SqlCommand command = new SqlCommand(dataquery, connection))
                        {
                            connection.Open();
                            command1.Parameters.AddWithValue("@woon", woon);
                            command1.ExecuteNonQuery();
                            command.ExecuteNonQuery();
                            command.Parameters.Clear();
                            command1.Parameters.Clear();
                        }
                    }
                }
                catch
                {
                    string dataquery = "INSERT INTO Klant([E-mail], Voornaam, Achternaam, Adres, Woonplaats, Postcode, Telefoonnummer) " +
                                       "Values('" + mail + "', '" + voor + "', '" + achter + "', '" + adres + "', '" + woon + "', '" + post + "', '" + tele + "')";

                    using (SqlConnection connection = new SqlConnection("Data Source=Evan-PC;Initial Catalog=DatabaseProject;Integrated Security=True"))
                    {

                        using (SqlCommand command = new SqlCommand(dataquery, connection))
                        {
                            connection.Open();
                            command.ExecuteNonQuery();
                            command.Parameters.Clear();
                        }
                    }

                }
            }
            catch
            {
                MessageBox.Show("Klantgegevens niet goed ingevuld");
            }

            try
            {
                for (int i = 0; i < dataGridView2.Rows.Count; i++)
                {
                    string dataquery = "INSERT INTO Factuur VALUES(@Voornaamklant, @Achternaamklant, @Albumtitel, @Aantal, @Prijs, @Subtotaal, @Korting, @Totaal, @CDofLP, @Datuminvoeren)";

                    using (SqlConnection connection = new SqlConnection("Data Source=Evan-PC;Initial Catalog=DatabaseProject;Integrated Security=True"))
                    {

                        using (SqlCommand command2 = new SqlCommand(dataquery, connection))
                        {
                            connection.Open();
                            command2.Parameters.AddWithValue("@Voornaamklant", dataGridView2.Rows[i].Cells["vnaam"].Value);
                            command2.Parameters.AddWithValue("@Achternaamklant", dataGridView2.Rows[i].Cells["anaam"].Value);
                            command2.Parameters.AddWithValue("@Albumtitel", dataGridView2.Rows[i].Cells["Albumtitels"].Value);
                            command2.Parameters.AddWithValue("@Aantal", dataGridView2.Rows[i].Cells["Aantal2"].Value);
                            command2.Parameters.AddWithValue("@Prijs", dataGridView2.Rows[i].Cells["Prijs2"].Value);
                            command2.Parameters.AddWithValue("@Subtotaal", dataGridView2.Rows[i].Cells["Subtotaal2"].Value);
                            command2.Parameters.AddWithValue("@Korting", dataGridView2.Rows[i].Cells["Korting2"].Value);
                            command2.Parameters.AddWithValue("@Totaal", Convert.ToDecimal((dataGridView2.Rows[i].Cells["Totaal2"].Value)));
                            command2.Parameters.AddWithValue("@CDofLP", dataGridView2.Rows[i].Cells["CDLP"].Value);
                            command2.Parameters.AddWithValue("@Datuminvoeren", Convert.ToDateTime(dateTimePicker1.Text));
                            command2.ExecuteNonQuery();
                            command2.Parameters.Clear();
                        }
                    }
                }

            }

            catch
            {
                MessageBox.Show("Kan de factuur niet in de database zetten", "Fout", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }
    }
}

Answer 1

Your code looks good to me. I would try putting a break point inside the for loop and debugging it. I suspect that you have one more row returning from dataGridView2.Rows.Count than you have data. This would explain why you are seeing the values inserted into Factuur that you want to see, but you are still getting that error message.

I would put a break point at the following lines of code so you can find out what your variable i's value is when you get your exception.

string dataquery = "INSERT INTO Factuur VALUES(@Voornaamklant, @Achternaamklant, @Albumtitel, @Aantal, @Prijs, @Subtotaal, @Korting, @Totaal, @CDofLP, @Datuminvoeren)";

MessageBox.Show("Kan de factuur niet in de database zetten", "Fout", MessageBoxButtons.OK, MessageBoxIcon.Error);

and also at the end of your for loop where you are looping through dataGridView2.Rows.

Answer 2

You will get this error if parameter's value is null

So check value before passing it to .Parameters

if(dataGridView2.Rows[i].Cells["vnaam"].Value == null)
    command2.Parameters.AddWithValue("@Voornaamklant", DBNull.Value);
else
    command2.Parameters.AddWithValue("@Voornaamklant", dataGridView2.Rows[i].Cells["vnaam"].Value);