Saving only the first row of a gridview to DB

Question

I need to save multiple rows in a gridview to the database. However my current code only saves the first row only. I wander why it is not looping my foreach line. What is my code missing to perform the intended task. Thanks!

My event is

protected void btnSaveAll_Click(object sender, EventArgs e)
{
    using (SqlConnection conn = DB_Connect.GetConn())
    {
        SqlCommand newCmd = conn.CreateCommand();
        newCmd.Connection = conn;
        newCmd.CommandType = CommandType.StoredProcedure;

        foreach (GridViewRow row in grdStyle.Rows)
        {

                    if (Convert.ToString((row.FindControl("txtStyle") as TextBox).Text.Trim()) == string.Empty)
                    {
                        WebMsgBox.Show("Style cannot be empty.");
                        return;
                    }
                    if (Convert.ToString((row.FindControl("txtMSMV") as TextBox).Text.Trim()) == string.Empty)
                    {
                        WebMsgBox.Show("MSMV cannot be empty.");
                        return;
                    }
                    if (Convert.ToString((row.FindControl("txtTSMV") as TextBox).Text.Trim()) == string.Empty)
                    {
                        WebMsgBox.Show("TSMV cannot be empty.");
                        return;
                    }
                    string TeamID = Convert.ToString((row.FindControl("TeamID") as Label).Text.Trim());
                    string Style = Convert.ToString((row.FindControl("txtStyle") as TextBox).Text.Trim());
                    string MSMV = Convert.ToString((row.FindControl("txtMSMV") as TextBox).Text.Trim());
                    string TSMV = Convert.ToString((row.FindControl("txtTSMV") as TextBox).Text.Trim());

                    newCmd.CommandText = "[DailyProductionOutput].[dbo].[sp_InsertTeamStyle]";
                    newCmd.Parameters.Add("@TeamID", SqlDbType.Int).Value = TeamID;
                    newCmd.Parameters.Add("@CompanyID", SqlDbType.Int).Value = companyID;//global variable
                    newCmd.Parameters.Add("@Style", SqlDbType.NVarChar).Value = Style;
                    newCmd.Parameters.Add("@MSMV", SqlDbType.Decimal).Value = MSMV;
                    newCmd.Parameters.Add("@TSMV", SqlDbType.Decimal).Value = TSMV;

                    try
                    {
                        if (conn.State.ToString() == "Closed")
                        {
                            conn.Open();
                        }
                        newCmd.ExecuteNonQuery();

                    }
                    catch (Exception ex)
                    {
                        WebMsgBox.Show("Saving failed." + ex);
                    }

                    conn.Close();

                    ((TextBox)(row.FindControl("txtStyle"))).Text = string.Empty;
                    ((TextBox)(row.FindControl("txtMSMV"))).Text = string.Empty;
                    ((TextBox)(row.FindControl("txtTSMV"))).Text = string.Empty;

            }

        }
    }
}

I am sure you must be receiving an exception here. If yes, then what and where in your code ? — Dhrumil, Jun 08 '15 at 06:54
Grids are bound to a datasource - a datatable, dataset, list of objects. You save the objects of the datasource, not the grid rows. Check a tutorial on ADO.NET and databinding because even if you fix the many places your code breaks, it is far too complicated and error prone. — Panagiotis Kanavos, Jun 08 '15 at 06:55
@Prosper - So does it loop for all rows and inserts only one row ? — Dhrumil, Jun 08 '15 at 06:58
Numerous problems - opening/closing the connection inside the loop despite the `using` clause, adding parameters for each row to the same command, searches for textboxes instead of reading values. Clearing the value of the textbox (after an unnecessary search for the same control). — Panagiotis Kanavos, Jun 08 '15 at 06:58
It should loop thru.. isn't it? second why are you setting `string.empty` to `textboxes`? — Amnesh Goel, Jun 08 '15 at 07:15
@AmneshGoel Once I saved the row values, I need to clear them. I am thinking of doing it another away. — Prosper, Jun 08 '15 at 08:10

score 1 · Answer 1 · edited May 23 '17 at 11:51

1

Maybe you can refer to this link

And based on that post, you can:

Open only one connection per transaction (not per row)
Close connection after you execute all row

Your code only save the last row for every btnSaveAll clicked since you are not followed this condition.

It would be like this:

SqlConnection conn = DB_Connect.GetConn();
conn.Open();
....

using(SqlCommand ....)
{
    foreach (GridViewRow row in GridView1.Rows)
    {
        .....
        your transaction here
        .....
        newCmd.Parameters.Clear();
        ...filling parameter
        newCmd.ExecuteNonQuery();
    }
}
conn.Close();

edited May 23 '17 at 11:51

Community

1
1

answered Jun 08 '15 at 07:45

Heinz Siahaan

355
2
10

Thanks, I'll give it a try and let you know. – Prosper Jun 08 '15 at 08:13
Clearing the parameter list did the trick ie. newCmd.Parameters.Clear(); – Prosper Jun 08 '15 at 08:20

score 0 · Answer 2 · edited Jun 08 '15 at 07:32

0

May be the following code is your problem, as you are making them clear at the end of first loop. If the value is cleared of those control then second time loop will not run..

Don't make them clear and check again..

((TextBox)(row.FindControl("txtStyle"))).Text = string.Empty;
((TextBox)(row.FindControl("txtMSMV"))).Text = string.Empty;
((TextBox)(row.FindControl("txtTSMV"))).Text = string.Empty;

edited Jun 08 '15 at 07:32

Amnesh Goel

2,617
3
28
47

answered Jun 08 '15 at 07:09

Neeraj Singh Chouhan

656
7
13

Sorry mate. It is not the problem. I have label which won't get cleared, so I expect the foeach to loop. – Prosper Jun 08 '15 at 07:16

Saving only the first row of a gridview to DB

2 Answers2