cmd.executeNonWQuery() freezes

I run this code below which is part of method. ds.Tables["tabelka"].Rows.Count has always value 13117 but sometimes the code executes only 1000 or 12000 times etc and freezes without error or exception not coming to the end of the method. In debug window it prints "before" which means it stops during or before executing upd.ExecuteNonQuery(); I don' know what is going on. Thanks for help. for (int y = 0; y < ds.Tables["tabelka"].Rows.Count; y++) { string account_id = ds.Tables["tabelka"].Rows[y][0].ToString(); string date = ds.Tables["tabelka"].Rows[y][1].ToString(); int temp1 = 0; double temp2 = 0.00; int val = 0; val = y/1874; string updateCommand = "update wielka set "+columnName+"="+val.ToString()+ " where account_id="+account_id+" and date="+date; SqlConnection sqlConnection3 = new SqlConnection(connString); SqlCommand upd = new SqlCommand(updateCommand, sqlConnection3); if (sqlConnection3.State.ToString() == "Closed") { sqlConnection3.Open(); } try { Console.WriteLine("before"); upd.ExecuteNonQuery(); Console.WriteLine("discretization "+y); } catch (Exception exc) { Console.WriteLine("Exception 3: "+exc.ToString()); Console.WriteLine("UpdateCommand: "+updateCommand); } sqlConnection3.Close(); }

it is probably failing because you are opening and closing the database connection so much in such a little bit of time. Try opening your connection for the for loop and closing it after the for loop ended, as so: SqlConnection sqlConnection3 = new SqlConnection(connString); if (sqlConnection3.State.ToString() == "Closed") { sqlConnection3.Open(); } try { Console.WriteLine("before"); upd.ExecuteNonQuery(); Console.WriteLine("discretization "+y); } catch (Exception exc) { Console.WriteLine("Exception 3: "+exc.ToString()); Console.WriteLine("UpdateCommand: "+updateCommand); } for (int y = 0; y < ds.Tables["tabelka"].Rows.Count; y++) { string account_id = ds.Tables["tabelka"].Rows[y][0].ToString(); string date = ds.Tables["tabelka"].Rows[y][1].ToString(); int temp1 = 0; double temp2 = 0.00; int val = 0; val = y/1874; string updateCommand = "update wielka set "+columnName+"="+val.ToString()+ " where account_id="+account_id+" and date="+date; SqlCommand upd = new SqlCommand(updateCommand, sqlConnection3); } sqlConnection3.Close();

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

icemanind21y ago
it is probably failing because you are opening and closing the database connection so much in such a little bit of time. Try opening your connection for the for loop and closing it after the for loop ended, as so: SqlConnection sqlConnection3 = new SqlConnection(connString); if (sqlConnection3.State.ToString() == "Closed") { sqlConnection3.Open(); } try { Console.WriteLine("before"); upd.ExecuteNonQuery(); Console.WriteLine("discretization "+y); } catch (Exception exc) { Console.WriteLine("Exception 3: "+exc.ToString()); Console.WriteLine("UpdateCommand: "+updateCommand); } for (int y = 0; y < ds.Tables["tabelka"].Rows.Count; y++) { string account_id = ds.Tables["tabelka"].Rows[y][0].ToString(); string date = ds.Tables["tabelka"].Rows[y][1].ToString(); int temp1 = 0; double temp2 = 0.00; int val = 0; val = y/1874; string updateCommand = "update wielka set "+columnName+"="+val.ToString()+ " where account_id="+account_id+" and date="+date; SqlCommand upd = new SqlCommand(updateCommand, sqlConnection3); } sqlConnection3.Close();