Question Save dataGridView data for the selected data records

andrewmanuja

Well-known member
Joined
May 30, 2019
Messages
75
Programming Experience
Beginner
Hi All,
I am working on a small application which got a dataGridView and the first columns got a "CheckBox".
My requirement is, if the user has selected/checked the records of the dataGridView, the said records should save in to the database table when hit the "Save" button.
However, i have come across that, the foreach command and the if condition (to check whether the user has selected an item) do not work together as per my code.
Please find the respective code below;

C#:
 SqlConnection con = new SqlConnection(@"Data Source=55504-CGHS\\SQLEXPRESS;Initial Catalog=Test_CheckBox;Integrated Security=True");
            SqlCommand cmd;

            using (con)
            {
                foreach (DataGridView row in dataGridViewProducts.Rows)
                {
                    if (Convert.ToBoolean(dataGridViewProducts.Rows[row].Cells["selectItem"].Value) == true)
                    {
                        cmd = new SqlCommand(@"INSERT INTO tbl_Product(productID,productName,quantity) VALUES (@aproductID,@aproductName,@aquantity)", con);
                        cmd.Parameters.AddWithValue("@aproductID", Convert.ToInt32(row.Cells["textProductID"].Value == DBNull.Value ? "0" : row.Cells["textProductID"].Value.ToString()));
                        cmd.Parameters.AddWithValue("@aproductName", row.Cells["textProductName"].Value == DBNull.Value ? "0" : row.Cells["textProductName"].Value.ToString());
                        cmd.Parameters.AddWithValue("@aquantity", Convert.ToInt32(row.Cells["textQuantity"].Value == DBNull.Value ? "0" : row.Cells["textQuantity"].Value.ToString()));

                        cmd.ExecuteNonQuery();
                    }
                }
            }

My database table looks like below;

1580690640713.png


Please advise me how to correct the above said issue.

Thank you in advance.
Kind regards,
Andrew
 
Don't do it like that. What I would suggest is that you loop through the grid, get the underlying DataRow and add it to a new DataTable, then use a data adapter to save the lot in one go. I'll provide an example when I'm able.
 
Hi Jmcilhinney,

Thank you very much for your reply.

I'll work according to what you said will wait for your example too.

Kind regards,

Andrew
 
Actually, it occurs to me that there may be a better way, depending on specifics. How EXACTLY did the grid get populated in the first place? Here's what I'm envisioning:
C#:
private DataTable table = new DataTable();

private void button2_Click(object sender, EventArgs e)
{
    // Get data from source.
    using (var adapter = new SqlDataAdapter("SELECT * FROM SourceTable", "source connection string here"))
    {
        adapter.Fill(table);
    }

    // Add column for selection.
    table.Columns.Add("Flag", typeof(bool));

    // Bind to grid.
    dataGridView1.DataSource = table;
}

private void button1_Click(object sender, EventArgs e)
{
    // Mark selected rows ready for insert.
    foreach (DataRow row in table.Rows)
    {
        if ((bool)row["Flag"])
        {
            row.SetAdded();
        }
    }

    // Save data to destination.
    using (var connection = new SqlConnection("destination connection string here"))
    using (var command = new SqlCommand("INSERT INTO DestinationTable (Column1, Column2) VALUES (@Column1, @Column2)", connection))
    using (var adapter = new SqlDataAdapter { InsertCommand = command })
    {
        command.Parameters.Add("@Column1", SqlDbType.VarChar, 50, "Column1");
        command.Parameters.Add("@Column2", SqlDbType.Int, 0, "Column2");

        adapter.Update(table);
    }
}
That code puts the check box column into the DataTable, which is not necessary but, I think, more convenient. The principle is the same regardless though. Populate a DataTable, bind it, make selections, loop and flag as added, then save.
 
Hi Jmcilhinney,
I followed your steps but still getting issues when loading data and saving the data to the database table.
When retrieving data, I am getting the error message, " Format of the initialization string does not conform to specification starting at index 0). I checked online and it says that it is something to do with the connection string, However, can't find any issues in regards to the connection string.
Please find my code below,
C#:
 SqlConnection source_con = new SqlConnection(@"Data Source=55504-CGHS\\SQLEXPRESS;Initial Catalog=Test_CheckBox;Integrated Security=True");

        private DataTable table = new DataTable();

        private void btnSave_Click(object sender, EventArgs e)
        {
            //Mark selected rows ready for insert
            foreach (DataRow row in table.Rows)
            {
                if((bool)row["selectItem"])
                {
                    row.SetAdded();
                }
            }
            //Save data to destinaion
            using (var connection = new SqlConnection(@"Data Source=55504-CGHS\SQLEXPRESS;Initial Catalog=Test_CheckBox;Integrated Security=True"))
            using (var command = new SqlCommand("INSERT INTO tbl_Product (productID,productName,quantity) VALUES (@aproductID,@aproductName,@aquantity)", connection))
            using (var adapter = new SqlDataAdapter { InsertCommand = command })
            {
                command.Parameters.Add("@aproductID", SqlDbType.Int, 0, "textProductID");
                command.Parameters.Add("@aproductName", SqlDbType.VarChar , 50, "textProductName");
                command.Parameters.Add("@aquantity", SqlDbType.Int, 0, "textQuantity");

                adapter.Update(table);              
            }            
        }

        private void btnGetData_Click(object sender, EventArgs e)
        {
            //Get data from source
            using (var adapter = new SqlDataAdapter("Select productID, productName, quantity from tbl_Product", "source_con"))
            {
                adapter.Fill(table);
            }

            //Add column for selection
            table.Columns.Add("selectItem", typeof(bool));

            //Bind to grid
            dataGridViewProducts.DataSource = table;
        }


The application does not save the dataGridView data into the database table.

Appreciate your valuable feedback.
Apologies if I am doing a silly mistake here.
Thank you.

Kind regards,

Andrew
 
There's two mistakes there. Firstly, what are you doing here:
C#:
using (var adapter = new SqlDataAdapter("Select productID, productName, quantity from tbl_Product", "source_con"))
You are using an overload of the SqlDataApapter constructor that expects a connection string as the second argument. Is "source_con" a valid connection string? No, it's not, any more than "Hello World" is a valid connection string. You do have a variable named source_con that refers to a SqlConnection object, so maybe you should use the overload that takes a SqlConnection object:
C#:
using (var adapter = new SqlDataAdapter("Select productID, productName, quantity from tbl_Product", source_con))
Secondly, you are using a verbatim string literal but you are still trying to escape the backslash:
C#:
SqlConnection source_con = new SqlConnection(@"Data Source=55504-CGHS\\SQLEXPRESS;Initial Catalog=Test_CheckBox;Integrated Security=True");
A string literal is any text between double-quotes. A verbatim string literal is any string literal preceded by an @ symbol. The point of using a verbatim string literal is that you don;t have to escape backslashes. C# uses C-style escape sequences, which are characters preceded by a backslash, e.g. you can add line breaks with appropriate escape sequences:
C#:
var text = "First Line\r\nSecond Line";
Because the backslash character denotes an escape sequence, literal backslashes also have to be escaped. This is particularly annoying in file paths, e.g.
C#:
var filePath = "C:\\Folder\\File.ext";
Two avoid having to double up all the backslashes in such literals, verbatim string literals were introduced. Verbatim string literals treat all backslashes as literal characters, so no escape sequences are possible. This is not a problem for things like file paths, e.g.
C#:
var filePath = @"C:\Folder\File.ext";
In your code, both your connection strings use verbatim string literals but the first one is still doubling the backslash, while the second is not.
 
Hi,
Thanks for your valuable reply.
Now I am able to retrieve the data but getting an error called "Specified cast is not valid".
However, I'll give it a try and get back to accordingly.
Thank you.
Kind regards,
Andrew
 
You should have specified, but I'm guessing that that happens here:
C#:
if((bool)row["selectItem"])
I didn't take into account that each row would actually contain DBNull.Value in that column by default. There are a number of ways around that but the simplest is probably to change that code to this:
C#:
if(row["selectItem"] as bool? == true)
 
Hi Jmcilhinney,

Thanks for your above meaningful feedback.

Had to do a minor change to the code and now it is working fine. Thanks for your guidance.

Need a small clarification from you. The added column (Select Item) needed to be set as the first column to display on the dataGridView.
Tried using the "SetOrdinal" command to accomplish it, but didn't work. Should I be considering the order at the data binding step?Any suggestions?

Please find the fine tuned one below and appreciate your comments on the code.

C#:
SqlConnection source_con = new SqlConnection(@"Data Source=55504-CGHS\SQLEXPRESS;Initial Catalog=Test_CheckBox;Integrated Security=True");

private DataTable table = new DataTable();

private void btnSave_Click(object sender, EventArgs e)
{
    //"row.SetAdded()" was giving an error called"SetAdded and SetModified can only be called on DataRows with Unchanged DataRowState". Thus, after adding "table.AcceptChanges()", the issue was able to resolve
    table.AcceptChanges();
    //Mark selected rows ready for insert
    foreach (DataRow row in table.Rows)
    {
        if (row["Select Item"] as bool? == true)
        {
            row.SetAdded();
        }
    }
    //Save data to destinaion

    using (var command = new SqlCommand("INSERT INTO tbl_Product (productID,productName,quantity) VALUES (@aproductID,@aproductName,@aquantity)", source_con))
    using (var adapter = new SqlDataAdapter { InsertCommand = command })
    {
        command.Parameters.Add("@aproductID", SqlDbType.Int, 0, "productID");
        command.Parameters.Add("@aproductName", SqlDbType.VarChar, 50, "productName");
        command.Parameters.Add("@aquantity", SqlDbType.Int, 0, "quantity");

        try
        {
            adapter.Update(table);
            MessageBox.Show("Data Saved");
        }
        catch (Exception ee)
        {
            MessageBox.Show(ee.Message);
        }
    }
}

private void btnGetData_Click(object sender, EventArgs e)
{
    //Get data from source
    using (var adapter = new SqlDataAdapter("Select productID, productName, quantity from tbl_Product", source_con))
    {
        adapter.Fill(table);
    }

    //Add column for selection
    table.Columns.Add("Select Item", typeof(bool)).SetOrdinal(0);

    //Bind to grid
    dataGridViewProducts.DataSource = table;
}

Thank you very much.

Kind regards,

Andrew
 
The position of the column in the data source and the position of the column in the grid can be independent, although they will be the same by default. You can add the extra column to the DataTable before calling Fill, so it will be the first column in the data source. Alternatively, you can set the DisplayIndex property of the grid column after binding. The Index of the grid column is its position in the Columns collection while the DisplayIndex is its position in the UI.
 
Back
Top Bottom