Query Timeout Expired when updating SQL Server from VB,NET

Question

I'm updating SQL Server from VB.NET and keep getting the 'Query Timeout Error', I have lot's of sub routines that I run in sequence that look like the following:

Public Shared Sub Update_DailyRatings()
    Dim stallStats As String = ""
    Dim win As Integer = 0
    Dim mSplit As Array

    Dim cn As OleDbConnection = New OleDbConnection(MainForm.connectStringPublic)
    cn.Open()

    Dim selectString As String = "Select * FROM DailyRatings"

    Dim cmd As OleDbCommand = New OleDbCommand(selectString, cn)
    Dim reader As OleDbDataReader = cmd.ExecuteReader()

    While (reader.Read())
        stallStats = Get_Stall_Stats(reader("Track").ToString, CInt(reader("Stall")), CDbl(reader("Distance")))
        If stallStats = "" Then
            MainForm.NonQuery("UPDATE DailyRatings SET StallWin = 999 WHERE Horse = '" & reader("Horse").ToString & "'")
        Else
            mSplit = Split(stallStats, ",")
            win = mSplit(0)

            MainForm.NonQuery("UPDATE DailyRatings SET StallWin = " & win & " WHERE Horse = '" & reader("Horse").ToString & "'")
        End If
    End While

    reader.Close()
    cn.Close()
End Sub

The NonQuery sub looks like this:

    Public Sub NonQuery(ByVal SQL As String)

    Dim query As String = SQL

    Try

        Dim cn3 As OleDbConnection = New OleDbConnection(connectStringPublic)

        cn3.Open()

        Dim cmd As OleDbCommand = New OleDbCommand(query, cn3)
        cmd.CommandTimeout = 90
        cmd.ExecuteNonQuery()
        cn3.Close()
        cn3.Dispose()
        cmd.Dispose()
        OleDbConnection.ReleaseObjectPool()
    Catch e As System.Exception
        Clipboard.SetText(query)
        MsgBox(e.Message)
    Finally

    End Try
End Sub

As you can see I've been trying ideas to fix this that I found in other threads such as extending the timeout and using the Dispose() and ReleaseObjectPool() methods but it hasn't worked, I still get query timeout error at least once when running all my subs in sequence, it's not always the same sub either.

I recently migrated from Access, this never used to happen with Access.

your OleDbDataReader may be locking your table in 'Read` method causing the deadlock. — vendettamit
– vendettamit, Commented Feb 13, 2020 at 17:04
Instead of using the OleDbDataReader you can use OleDbDataAdapter to fill the data into a DataTable and then loop through the rows to run the update. — vendettamit
– vendettamit, Commented Feb 13, 2020 at 17:13

Mary · Accepted Answer · 2020-02-14 04:09:48Z

If you are dealing with Sql Server why are you using OleDb? I guessed that is was really access.

While your DataReader is open, your connection remains open. With the amount of processing you have going on, it is no wonder that your connection is timing out.

To begin, connections and several other database objects need to be not only closed but disposed. They may contain unmanaged resources which are released in the .Dispose method. If you are using an object that exposes a .Dispose method use Using...End Using blocks. This will take care of this problem even if there is an error.

Actually you have 2 distinct operations going on. First you are retrieving DailyRatings and then you are updating DailyRatings base on the data retrieved. So we fill a Datatable with the first chunk of data and pass it off to the second operation. Our first connection is closed and disposed.

In operation 2 we create our connection and command objects just as before except now our command has parameters. The pattern of the command is identical for every .Execute, only the values of the parameters change. This pattern allows the database, at least in Sql Sever, to cache a plan for the query and improve performance.

Public Shared Function GetDailyRatings() As DataTable
    Dim dt As New DataTable
    Using cn As New OleDbConnection(MainForm.connectStringPublic),
            cmd As New OleDbCommand("Select * FROM DailyRatings", cn)
        cn.Open()
        dt.Load(cmd.ExecuteReader)
    End Using
    Return dt
End Function

Public Sub UpdateDailyRatings()
    Dim dt = GetDailyRatings()
    Using cn As New OleDbConnection(connectStringPublic),
            cmd As New OleDbCommand("UPDATE DailyRatings SET StallWin = @Stall WHERE Horse = @Horse")
        cmd.Parameters.Add("@Stall", OleDbType.Integer)
        cmd.Parameters.Add("@Horse", OleDbType.VarChar)
        cn.Open()
        For Each row As DataRow In dt.Rows
            cmd.Parameters("@Horse").Value = row("Horse").ToString
            Dim stallStats As String = Get_Stall_Stats(row("Track").ToString, CInt(row("Stall")), CDbl(row("Distance")))
            If stallStats = "" Then
                cmd.Parameters("@Stall").Value = 999
            Else
                cmd.Parameters("@Stall").Value = CInt(stallStats.Split(","c)(0))
            End If
            cmd.ExecuteNonQuery()
        Next
    End Using
End Sub

Private Function GetStallStats(Track As String, Stall As Integer, Distance As Double) As String
    Dim s As String
    'Your code here
    Return s
End Function

Note: OleDb does not pay attention to parameters names. It is the order that they appear in the query statement must match the order that they are added to the Parameters collection.

Yes I was using Oledb because of Access. I'm actually going to use a work around by adding all the update queries to a list of string and executing them once the first connection is closed as it's too much code to update, but your answer explains a lot, thanks.
Actually, I guess it depends on how much code is in GetStallStats. Building strings should work but remember each string will have to executed independently.

vendettamit · Accepted Answer · 2020-02-13 17:23:55Z

1

It's possible that OleDbDataReader is locking your table or connection as it get the data with busy connection. You can store the data in a DataTable by using OleDbDataAdapter and loop through it to run your updates. Below is the snippet how your code would look like:

Dim cmd As OleDbCommand = New OleDbCommand(selectString, cn)
Dim adapter As OleDbDataAdapter = New OleDbDataAdapter(cmd)
Dim dt As New DataTable()
adapter.Fill(dt)

For Each reader As DataRow In dt.Rows
    stallStats = Get_Stall_Stats(reader("Track").ToString, CInt(reader("Stall")), CDbl(reader("Distance")))
    If stallStats = "" Then
        MainForm.NonQuery("UPDATE DailyRatings SET StallWin = 999 WHERE Horse = '" & reader("Horse").ToString & "'")
    Else
        mSplit = Split(stallStats, ",")
        win = mSplit(0)

        MainForm.NonQuery("UPDATE DailyRatings SET StallWin = " & win & " WHERE Horse = '" & reader("Horse").ToString & "'")
    End If
Next
cn.Close()

answered Feb 13, 2020 at 17:23

vendettamit

14.7k2 gold badges36 silver badges56 bronze badges

2 Comments

user2096512 Over a year ago

Ok cheers, thats a lot of code that I need to change but I'll give it a try.

vendettamit Over a year ago

No, it's not. It's just 4-5 lines to replace reader by adapter and the loop that will be changed everything else will remain same.

Collectives™ on Stack Overflow

Query Timeout Expired when updating SQL Server from VB,NET

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related