OdbcDataAdapter.Fill causes error

[robert0106]

Hi,

I have a VB.NET app and I am using the Microsoft ODBC .NET driver to access data from an Intersystems (Cache) database.

My program uses a class that contains several modules that return a dataset from a function. The code segment is listed below:

Public Function OpenDataTableWithSQL(ByVal strTableName As String, ByVal strSQL As String) As DataSet
Dim dsCache As DataSet
Dim daCache As OdbcDataAdapter
Try
daCache = New OdbcDataAdapter()
dsCache = New DataSet()
daCache.SelectCommand = New OdbcCommand(strSQL, cnCache)daCache.SelectCommand.CommandTimeout = intCommandTimeout
Try
daCache.Fill(dsCache, strTableName)
Return dsCache
Catch ex1 As Exception
Call LogClassError(ex1, "class claLoadList", "OpenDataTableWithSQL - EX1", 0, lngLine, True)
Return Nothing
End Try
Catch ex As Exception
Call LogClassError(ex, "class claDataCommands", "OpenDataTableWithSQL", 0, lngLine)
Return Nothing
Finally
If daCache Is Nothing = False Then daCache.Dispose()
If dsCache Is Nothing = False Then dsCache.Dispose()
End Try



End Function



As you can see by the code, I put the Fill command inside of it's own Try..Catch construct so that I can determine if the error happens at that point.

The problem that i am having is that this line of code works for a while (I'd say 10-15 times). At odd times after that, however, the FILL command causes an "Object not set to a reference to an object" error message. At this point, the program's user must exit from the application and restart it. Occasionally, they must restart their PC in order to clear the memory conditions that exist.

Has anyone had similar problems like this before? Can anyone suggest an alternate way to open a DataSet? I use similar logic to open a data table and the logic is pretty much exactly the same as the above code (but specific to DataTable creation).

FYI, If anyone is interested in seeing the stack trace for this error that I receive, I include it below:
at System.Data.Common.Odbc32.SQLExecDirectW(IntPtr StatementHandle, String StatementText, Int32 TextLength)\n at Microsoft.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method)\n at Microsoft.Data.Odbc.OdbcCommand.ExecuteReader(CommandBehavior behavior)\n at Microsoft.Data.Odbc.OdbcCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)\n at System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)\n at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)\n at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable)\n at IMS_Program.claDataCommands.OpenDataTableWithSQL(String strTableName, String strSQL)

I would appreciate any help that you could give me towards solving this issue -- it's starting to drive me crazy!

Rob

 

[Outoftheblue9]

Rob,

I am having the exact same problems (I signed up here to hopefully get an answer to this aswell). I am moving data between databases (Sybase & MSSQL) for reporting purposes and about once every 15-20 days, I am getting the error "Object Reference not set to an instance of an object". The next time the app runs it works perfectly. I'm confused because the object does have an instance otherwise it wouldn't work in the first place. I had a theory yesterday, that maybee its actually a timeout error thats occuring but they are sending the wrong exception back. It takes between 60-62 seconds for this error to occur, (I log and timestamp just before, and log and timestamp the error). ADO timeout's seem to be 60 seconds, so I think that it might have something todo with that. If it was an actual referencing error, then I would think it would be instant.

If you find anything out, keep me post, and I will do the same.

Thanks