C# Data grid view, SQL Server: Select multiple Rows then insert selected rows with specified columns into SQL table

Question

In my current application I have a snippet of code that allows me to select a single row in a data grid view and store all the columns information into a variable to do with what I want. I use this primarily to send information from one SQL database table to another. It's great for only sending specified cells within a row.

Here is how I do a single row:

string ID = dataGridView1.SelectedRows[0].Cells[0].Value + string.Empty;
            string itemOne= dataGridView1.SelectedRows[0].Cells[1].Value + string.Empty;
            string itemTwo= dataGridView1.SelectedRows[0].Cells[2].Value + string.Empty;
            string itemThree= dataGridView1.SelectedRows[0].Cells[3].Value + string.Empty;


            var vItemOne = itemOne;
            var vItemTwo= itemTwo;
            var vItemThree= itemThree;
            // ETC..

However, I now want to be able to select Multiple Rows and only insert specified columns within those rows to a SQL database.

I've tried modifying the above code to work... obviously it doesn't work.

I believe I need a loop, I haven't really used loops much so I'm not sure how to make it loop, skip certain columns, then insert into database.

This is what I am currently attempting, however I seem to be messing up somewhere.

using (SqlConnection con = new SqlConnection(Connection.MTRDataBaseConn))
            {

            for (int i = 0; i < dataGridView1.SelectedRows.Count; i++)
            {
                con.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = "INSERT INTO dbo.[" + txtJobName.Text + "] ([Item One], [Item Two], [Item Three]) VALUES(@ItemOne,@ItemTwo,@ItemThree)";

                cmd.Connection = con;

                string strItemOne = this.dataGridView1.SelectedRows[i].Cells[1].Value + string.Empty;
                string strItemTwo = this.dataGridView1.SelectedRows[i].Cells[2].Value + string.Empty;
                string strItemThree = this.dataGridView1.SelectedRows[i].Cells[3].Value + string.Empty;


                //Parameters
                cmd.Parameters.AddWithValue("@ItemOne", strItemOne);
                cmd.Parameters.AddWithValue("@ItemTwo", strItemTwo);
                cmd.Parameters.AddWithValue("@ItemThree", strItemThree);

                //execute
                cmd.ExecuteNonQuery();

                //close connection
                con.Close();
            }
        }

...

While Debugging My dataGridView.SelectedRows.Count; i++ doesn't seem to be increasing and is staying at 0... I'm receiving the error when I try to return the selected row to a string. Shouldn't my selected rows still return a value?

I'm under the assumption my loop is wrong.

Can anyone help me with my issue?

you need to do the foreach loop and also use something like this `foreach (DataGridViewRow row in dataGridView.SelectedRows)` from there you would need to have a variable declared outside for any and all columns so that you can pass that as a parameter — MethodMan, Jan 26 '17 at 16:33
Possible duplicate of [What is a NullReferenceException, and how do I fix it?](http://stackoverflow.com/questions/4660142/what-is-a-nullreferenceexception-and-how-do-i-fix-it) — Owen Pauling, Jan 26 '17 at 16:35
Thanks @MethodMan, I'll try it out. It's not a duplicate post... — Mokey, Jan 26 '17 at 16:38
also you do not need this line `con.Close();` since you are wrapping the connection object around a `using(){}` I would also suggest you create your CRUD functionality as Stored procedures and stay away from creating queries this way are you familiar with `SQL INJECTION` read up on it and how to avoid it also wrap the SQL COMMAND Object around an inner `Using(){}` as well look up nested `using()` — MethodMan, Jan 26 '17 at 16:41
I'm learning on it... However, still very fresh on it. Reading up on Foreach loops now. lol, sometimes I wish I went to school for this. — Mokey, Jan 26 '17 at 16:49
Well it is now past that point... Which is great... but it's now saying the selected range is wrong... But it's not... Thanks though.. time for more research :) — Mokey, Jan 26 '17 at 17:05
I never went to school for coding I taught myself by reading and learning by example either way please update your question with your refactored code so that we can see what you are doing and where you are going wrong.. thanks — MethodMan, Jan 26 '17 at 17:07

score 0 · Accepted Answer · edited Jan 26 '17 at 19:23

Simply have to use a for each statement

string itemOne= dataGridView1.SelectedRows[0].Cells[1].Value + string.Empty;
string itemTwo= dataGridView1.SelectedRows[0].Cells[2].Value + string.Empty;
string itemThree= dataGridView1.SelectedRows[0].Cells[3].Value + string.Empty;

var vItemOne = itemOne;
var vItemTwo= itemTwo;
var vItemThree= itemThree;

foreach (DataGridViewRow row in dataGridView1.SelectedRows)
{
    //Insert Query Here
}

C# Data grid view, SQL Server: Select multiple Rows then insert selected rows with specified columns into SQL table

1 Answers1