CODEWITHSUNDEEP
sqlvb.net
Byran Beck
Byran Beck

Reputation: 1

VB.NET update same table inside a DataReader loop

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

Upvotes: 0

Views: 2627

Answers (1)

Tim Schmelter
Tim Schmelter

Reputation: 460118

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)

Upvotes: 1

Related Questions