title: VB.NET update same table inside a DataReader loop
body: <p>I'm reading from a SQL database and depending on the fields I check I need to update a different field in that same table.  I'm looping through the dataset and trying to send an UPDATE back while looping.  It worked on my TEST table but isn't working for my production table.  When I execute the "ExecuteNonQuery" command, I get a timeout expired error.  If I actually close the first connection and then call the ExecuteNonQuery it runs instantly.</p>
  
  <p>Here's the code...</p>
  
  <pre><code>Public Class Form1
  
      Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
  
          Dim sqlConn As New SqlConnection
          Dim sqlComm As New SqlCommand
          Dim sqlDR As SqlDataReader
  
  
  
  
          sqlConn.ConnectionString = "Data Source=10.2.0.87;Initial Catalog=Inbound_Orders_DB;User ID=xxxxx;Password=xxxxxx;"
          sqlConn.Open()
          sqlComm.CommandText = "SELECT * FROM RH_Orders  where Order_DateTime is NULL ORDER by Order_Date DESC"
          sqlComm.Connection = sqlConn
  
          sqlDR = sqlComm.ExecuteReader
  
          If sqlDR.HasRows Then
              While sqlDR.Read
                  If Trim(sqlDR("Order_Date")) &lt;&gt; "" Then
                      Dim order_Date As String = Trim(sqlDR("Order_Date"))
                      Dim order_DateTime As String = ""
                      If Len(order_Date) = 14 Then
                          order_DateTime = order_Date.Substring(0, 4) &amp; "-" &amp; order_Date.Substring(4, 2) &amp; "-" &amp; order_Date.Substring(6, 2) &amp; " "
                          order_DateTime = order_DateTime &amp; order_Date.Substring(8, 2) &amp; ":" &amp; order_Date.Substring(10, 2) &amp; ":" &amp; order_Date.Substring(12, 2)
                          Dim myId As String = sqlDR("ID")
  
                          Dim sqlConn2 As New SqlConnection
                          Dim sqlComm2 As New SqlCommand
  
                          sqlConn2.ConnectionString = "Data Source=10.2.0.87;Initial Catalog=Inbound_Orders_DB;User ID=xxxx;Password=xxxx;"
                          sqlConn2.Open()
  
                          sqlComm2.CommandText = "UPDATE [RH_Orders] SET order_DateTime = '" &amp; order_DateTime &amp; "' WHERE ID=" &amp; myId
                          sqlComm2.Connection = sqlConn2
  
                          sqlComm2.ExecuteNonQuery()
                          sqlConn2.Close()
  
                      End If
                  End If
              End While
  
          End If
  
  
      End Sub
  End Class
  </code></pre>