VB.NET update same table inside a DataReader loop

Question

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.

Here's the 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")) <> "" 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) & "-" & order_Date.Substring(4, 2) & "-" & order_Date.Substring(6, 2) & " "
                        order_DateTime = order_DateTime & order_Date.Substring(8, 2) & ":" & order_Date.Substring(10, 2) & ":" & 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 = '" & order_DateTime & "' WHERE ID=" & myId
                        sqlComm2.Connection = sqlConn2

                        sqlComm2.ExecuteNonQuery()
                        sqlConn2.Close()

                    End If
                End If
            End While

        End If


    End Sub
End Class

Use parametrized queries and don't concatenate strings, then use a SqlParameter with SqlDbType.Datetime and assign a real DateTime instead of a formated string. — Tim Schmelter
– Tim Schmelter, Commented Jan 31, 2017 at 16:08
You could fill a DataTable with SqlDataAdapter.Fill(table), then loop the table's Rows, change each DataRow and use SqlDataAdapter.Update(table) to send all changes in one batch after the loop. No need for the SqlDataReader loop — Tim Schmelter
– Tim Schmelter, Commented Jan 31, 2017 at 16:14
I can't see anything that even requires a loop here, why not just us a normal update statement to update all the rows at once. What exactly is your logic for updating? Also, you should always use Using blocks when dealing with objects that implement IDisposable, especially unmanaged resourses like SqlConnection and SqlCommand — GarethD
– GarethD, Commented Jan 31, 2017 at 16:20

Tim Schmelter · Accepted Answer · 2017-01-31 16:33:32Z

Use parametrized queries and don't concatenate strings, then use a SqlParameter with SqlDbType.Datetime and assign a real DateTime instead of a formatted string.

But maybe it would be more efficient here to fill a DataTable with SqlDataAdapter.Fill(table), loop the table's Rows, change each DataRow and use SqlDataAdapter.Update(table) to send all changes in one batch after the loop. No need for the SqlDataReader loop.

For example (untested):

Using con = New SqlConnection("Data Source=10.2.0.87;Initial Catalog=Inbound_Orders_DB;User ID=xxxxx;Password=xxxxxx;")
    Using da = New SqlDataAdapter("SELECT * FROM RH_Orders  where Order_DateTime is NULL ORDER by Order_Date DESC", con)
        da.UpdateCommand = New SqlCommand("UPDATE RH_Orders SET order_DateTime = @order_DateTime WHERE ID = @Id", con)
        da.UpdateCommand.Parameters.Add("@order_DateTime", SqlDbType.DateTime).SourceColumn = "order_DateTime"

        Dim table = New DataTable()
        da.Fill(table)

        For Each row As DataRow In table.Rows
            Dim orderDate = row.Field(Of String)("Order_Date")
            Dim orderDateTime As DateTime
            If DateTime.TryParse(orderDate.Substring(0, 4) & "-" & orderDate.Substring(4, 2) & "-" & orderDate.Substring(6, 2), orderDateTime) Then
                row.SetField("order_DateTime", orderDateTime)
            End If
        Next

       da.Update(table)
    End Using
End Using

Side-note: Instead of building a new string "2017-01-31" from "20170131" you could also use DateTime.TryParseExact:

DateTime.TryParseExact("20170131", "yyyyMMdd", nothing, DateTimeStyles.None, orderDateTime)

Collectives™ on Stack Overflow

VB.NET update same table inside a DataReader loop

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related