0

Community,

I acquired some VB code from this thread in order to read worksheet contents into data tables.

text

Public Shared Function ReadExcelIntoDataTable(ByVal FileName As String, ByVal SheetName As String) As DataTable
    Dim RetVal As New DataTable
    Dim strConnString As String
    strConnString = "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=" & FileName & ";"

    Dim strSQL As String 
    strSQL = "SELECT * FROM [" & SheetName & "$]"

    Dim y As New Odbc.OdbcDataAdapter(strSQL, strConnString)
    y.Fill(RetVal)

    Return RetVal
End Function

The code works the first 3-4 uses, but then this error occurs:

ERROR [08004] [Microsoft][ODBC Excel Driver] Too many client tasks.

<< IMAGE HERE >> enter image description here

I must exit/restart the application to continue development. Again after about four runs, the error occurs again and I must restart the app.

I conclude the connection is not closing and therefore results in "too many client tasks". A search on the error message also suggests issues with a closed connection.

Any advice on how to change the code to prevent the error would be appreciated

Researching the error message did not help to identify any specific methods to close the connection.

Chenmunka
  • 685
  • 4
  • 21
  • 25
BernsJ
  • 23
  • 4
  • It's not necessarily the immediate problem, but `OdbcDataAdapter` (indirectly) implements `IDisposable`, so you must either put `y` in a `Using` block or manually call `Dispose` on it when you're done with it. – Craig Aug 14 '23 at 13:29
  • Such as this? [code block] Dim y As New Odbc.OdbcDataAdapter(strSQL, strConnString) y.Fill(RetVal) y.Dispose Return RetVal – BernsJ Aug 14 '23 at 13:46
  • Apologies - first time posting and have not figured out how to post code correctly in comments. – BernsJ Aug 14 '23 at 13:50
  • Comments don't really support posting code. More or less that's right, but I'd favor a `Using` block i.e. `Using y = New Odbc.OdbcDataAdapter...`. The end of the block will take care of calling `Dispose`. – Craig Aug 14 '23 at 14:04
  • I switched to a Using block and have run the program 10+ times sequentially. No more errors! Thank you so much @Craig! – BernsJ Aug 14 '23 at 14:57
  • Glad it helped. Since this actually was the problem, I wrote it up as an answer. – Craig Aug 14 '23 at 15:41

1 Answers1

1

It looks like this is a new manifestation of a common issue. OdbcDataAdapter implements IDisposable (indirectly, in a base class a couple of levels up). Whenever you are using something that implements IDisposable, this is a signal that you must call Dispose when you are done with it. Usually it's because of some kind of resource allocation that needs to be released.

In this case, the right thing to do is to use a Using block which will take care of calling Dispose for you when exiting the block (and also take care of ensuring that it happens even in the event of an exception):

Using y = New Odbc.OdbcDataAdapter(strSQL, strConnString)
    y.Fill(RetVal)
End Using
Craig
  • 2,248
  • 1
  • 19
  • 23