How to fix Messagebox popping twice and the error on ExecuteNonQuery

Question

I have this datagridview where I need to insert whatever it has on it. So I have 2 rows in my datagridview. Whenever I click the save button, it asks me the messagebox thrice. And this error comes out on the xcom.ExecuteNonQuery();

This is the error :

The parameterized query '(@id nvarchar(4000),@idtran nvarchar(4000),@qty nvarc' expects the parameter '@id', which was not supplied.

I checked the database and it inserts the right number of rows from the datagridview. I wonder why the message box goes out 3 times asking me and then followed by the error.

Please help me, I am new to c# and still learning.

private void button9_Click(object sender, EventArgs e)
        {
            foreach (DataGridViewRow row in dataGridView2.Rows)
            {
                string query = @"INSERT INTO MED (id,idtran,qty,user)
                                            Values(@id,@idtran,@qty,@user)";

                using (SqlConnection xcon = new SqlConnection(@"Server=MEAND;Database=SHC;Integrated Security=SSPI;"))
                {
                    using (SqlCommand xcom = new SqlCommand(query, xcon))
                    {
                        xcon.Open();
                        xcom.CommandType = CommandType.Text;
                        xcom.Parameters.AddWithValue("@id", row.Cells["id"].Value);
                        xcom.Parameters.AddWithValue("@idtran", row.Cells["idtran"].Value);
                        xcom.Parameters.AddWithValue("@qty", row.Cells["qty"].Value);
                        xcom.Parameters.AddWithValue("@user", row.Cells["user"].Value);
                        xcom.ExecuteNonQuery();

                        try
                        {
                            DialogResult result1 = MessageBox.Show("Are you sure you want to save this?",
                            "Important Question", MessageBoxButtons.YesNo, MessageBoxIcon.Question);

                            if (result1 == DialogResult.Yes)
                            {
                                Medi b = new Medi();
                                b.Show();
                                this.Hide();
                            }
                        }

                        catch (Exception)
                        {
                            throw;

                        }
                        finally
                        {
                            xcon.Close();
                        }
                    }
                }
            }
        }

MessageBox pops multiple times because it's in a foreach, it's trivial or do I miss something? — koviroli, Apr 05 '19 at 07:23
@koviroli did I messed up the arrangement of the code? It should only pop once no matter how rows I've got from my datagridview. — meandyou, Apr 05 '19 at 07:25
@meandyou Your MessageBox will pop same number of times like your datagridview's rows count now.If you look closer, It's inside of your foreach loop. — koviroli, Apr 05 '19 at 07:28
@koviroli thank you for letting me know. I have changed it after the curly brace of foreach loop. But the error "The parameterized query '(@id nvarchar(4000),@idtran nvarchar(4000),@qty nvarc' expects the parameter '@id', which was not supplied. " is still not fixed. :( — meandyou, Apr 05 '19 at 07:35
@meandyou I would also advice to take "using (SqlConnection xcon..." statement and the "xcon.Open()" statement outside the loop. You can have one open database connection and run multiple commands in the loop. — TSungur, Apr 05 '19 at 07:35
@TSungur I'm sorry. I didn't get how you would like it to be arranged. I'm really sorry. Can you please show? I'm kinda scared messing up the arrangement. — meandyou, Apr 05 '19 at 07:40

TSungur · Accepted Answer · 2019-04-05T09:26:24.263

0

Something like this: (I have ommited the messagebox code for simplicity)

        DataGridViewRow row;
        string query = @"INSERT INTO MED (id,idtran,qty,user)
                            Values(@id,@idtran,@qty,@user)";
        using (SqlConnection xcon = new SqlConnection(@"Server=MEAND;Database=SHC;Integrated Security=SSPI;"))
        {
            try
            {
                xcon.Open();
                for (int n=0;n<dataGridView2.Rows.Count-1;n++)
                {
                    row=dataGridView2.Rows[n];
                    using (SqlCommand xcom = new SqlCommand(query, xcon))
                    {
                        xcom.CommandType = CommandType.Text;
                        xcom.Parameters.AddWithValue("@id", row.Cells["id"].Value);
                        xcom.Parameters.AddWithValue("@idtran", row.Cells["idtran"].Value);
                        xcom.Parameters.AddWithValue("@qty", row.Cells["qty"].Value);
                        xcom.Parameters.AddWithValue("@user", row.Cells["user"].Value);
                        xcom.ExecuteNonQuery();xcom.Dispose();
                    }
                }                   
            }
            catch
            {
                //do what you need
            }
            finally
            {
                xcon.Close();
            }
        }

And also consider this post if you will have many rows in your data grid for perfomance issues.

edited Apr 05 '19 at 09:26

answered Apr 05 '19 at 07:49

TSungur

396
2
9

Thank you. I have changed it. I added a breakpoint in the button_click to see what's happening, I tried to input just 1 row in my datagridview, It reads the 1st row and after that, it goes back to the foreach loop, so that means it is already null that is why it is giving me the error. Is there a way to stop the loop? or count the rows first so it will know how many times it will loop? – meandyou Apr 05 '19 at 08:07
@meandyou at which statement do you get the null? You can stop a loop at any time with "break;" statement. – TSungur Apr 05 '19 at 08:48
when I have 1 row in my datagridview, it reads the foreach twice.. When I have 2 rows, it reads the foreach thrice. So, if I have 1 row, and reads it twice, the 2nd row is null. – meandyou Apr 05 '19 at 08:59
Ahh! now I see the problem. The datagridview has an empty row by default, which is convenient to others to add new row. So it will be better to replace the foreach loop like this "for (int i = 0; i < dataGridView2.Rows.Count - 1; i++)" I will update my answer accordingly. – TSungur Apr 05 '19 at 09:10
THANKYOOUUU!!! TSungur, thank you so much! It's working! I cry! Thank you for helping me out. – meandyou Apr 05 '19 at 09:21
@meandyou :)) You are very welcome. Glad I could be helpful – TSungur Apr 05 '19 at 09:25

How to fix Messagebox popping twice and the error on ExecuteNonQuery

1 Answers1