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;

            }

        }
    }
}

Answer 1

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;

Answer 2

Maybe you can refer to this link

And based on that post, you can:

1. Open only one connection per transaction (not per row)
2. 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();