Click here to Skip to main content
15,891,248 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
hi i have datagridview in our form that have 360 rows,
i want insert this rows to database but Sqlcommand not run more than 60 times in a foreach loop

this my code:
C#
try
            {

                con.Open();
                foreach (DataGridViewRow row in dataGridViewX1.Rows)
                {
                    SqlCommand com = new SqlCommand();
                    com.Connection = con;
                    com.CommandType = CommandType.StoredProcedure;
                    com.CommandText = "add_data_to_calander";
                    com.Parameters.AddWithValue("@date", row.Cells[2].Value.ToString());
                    com.Parameters.AddWithValue("@dayinweek", row.Cells[1].Value.ToString());
                    com.Parameters.AddWithValue("@dayinyear", row.Cells[0].Value.ToString());
                    var _value = row.Cells[3].Value;
                    if (_value != null && (bool)_value)
                        com.Parameters.AddWithValue("@isdisable", "1");
                    else
                        com.Parameters.AddWithValue("@isdisable", "0");
                    counter++;
                    if (con.State == ConnectionState.Closed)
                        con.Open();
                    com.ExecuteNonQuery();
                    com.Parameters.Clear();
                }
            }
            catch (Exception)
            {
            }
            finally { con.Close(); }
            MessageBox.Show(counter.ToString());

finally counter value is 60.
Posted
Comments
Ramug10 5-Mar-14 7:20am    
close the connection after executenonquery.
mosi98 5-Mar-14 7:22am    
i try it but don't work
Ramug10 5-Mar-14 7:25am    
put the code in using statement

1 solution

Try disposing of your SqlCommand objects:
C#
using (SqlCommand com = new SqlCommand())
    {
    com.Connection = con;
    com.CommandType = CommandType.StoredProcedure;
    com.CommandText = "add_data_to_calander";
    com.Parameters.AddWithValue("@date", row.Cells[2].Value.ToString());
    com.Parameters.AddWithValue("@dayinweek", row.Cells[1].Value.ToString());
    com.Parameters.AddWithValue("@dayinyear", row.Cells[0].Value.ToString());
    var _value = row.Cells[3].Value;
    if (_value != null && (bool)_value)
        com.Parameters.AddWithValue("@isdisable", "1");
    else
        com.Parameters.AddWithValue("@isdisable", "0");
    counter++;
    if (con.State == ConnectionState.Closed)
        con.Open();
    com.ExecuteNonQuery();
    com.Parameters.Clear();
    }
SqlCommands are scarce resources, and the chances are you are running out in SQL server.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900