Hello,

I am facing a weird problem using the DataReader.

This is what my code looks like.

---------------
Dim dbCommand As OleDbCommand
Dim dReader As OleDbDataReader
dbCommand = New OleDbCommand("SELECT * FROM CmpDet where cmpDet_CompanyID = " & CurrentCompany.CompanyID, DBCompany)
dReader = dbCommand.ExecuteReader 'Retrieve info from the database
dReader.Read() 'Read the record

'Load the company details
Try
CurrentCompany.Address = dReader.Item("cmpDet_MailAddress")
CurrentCompany.CompanyName = dReader.Item("cmpDet_CompanyName")
CurrentCompany.City = dReader.Item("cmpDet_CityName")
CurrentCompany.State = dReader.Item("cmpDet_State")
Catch ex As Exception
MsgBox(ex.Message)
Stop
End Try

'Empty the objects

dReader.Close()
dbCommand.Dispose()
dReader = Nothing
dbCommand = Nothing

---------------------

My problem is that on the dReader.item("cmpDet_MailAddress") line (When I try to read the contents of the dReader object). I sometimes get an error 'Invalid Row Handler'

This error does not occur all of the time, only some of it. Could it be because the records that the dReader retrieves were added to the db in a line just above this code block? I am using a dbCommand object with an "INSERT INTO" query to add the contents to the DB so I expect that there should be no problems retrieving data.

This error only occurs some of the time, and at other times it runs fine.

Any clues why this could be happening?

Thanks
Cyril Gupta

I would suspect your data provider. It's either JET or some other OLE DB data source which are notorious for lazy writes.

  I am facing a weird problem using the DataReader.

  This is what my code looks like.

  ---------------
  Dim dbCommand As OleDbCommand
  Dim dReader As OleDbDataReader
  dbCommand = New OleDbCommand("SELECT * FROM CmpDet where cmpDet_CompanyID = " & CurrentCompany.CompanyID, DBCompany)
  dReader = dbCommand.ExecuteReader 'Retrieve info from the database
  dReader.Read() 'Read the record

  'Load the company details
  Try
   CurrentCompany.Address = dReader.Item("cmpDet_MailAddress")
   CurrentCompany.CompanyName = dReader.Item("cmpDet_CompanyName")
   CurrentCompany.City = dReader.Item("cmpDet_CityName")
   CurrentCompany.State = dReader.Item("cmpDet_State")
  Catch ex As Exception
   MsgBox(ex.Message)
   Stop
  End Try

  'Empty the objects

  dReader.Close()
  dbCommand.Dispose()
  dReader = Nothing
  dbCommand = Nothing

  ---------------------

  My problem is that on the dReader.item("cmpDet_MailAddress") line (When I try to read the contents of the dReader object). I sometimes get an error 'Invalid Row Handler'

  This error does not occur all of the time, only some of it. Could it be because the records that the dReader retrieves were added to the db in a line just above this code block? I am using a dbCommand object with an "INSERT INTO" query to add the contents to the DB so I expect that there should be no problems retrieving data.

  This error only occurs some of the time, and at other times it runs fine.

  Any clues why this could be happening?

  Thanks
  Cyril Gupta

Hello Bill,

I think you've got a breakthrough right there. Yep my data provider is Jet. Any ideas on how I can make sure the data is updated before I do the retrieval operation?

Thanks for that keyword 'lazy write' I will try to do google on it and see if any one else has had the problem.

Thanks
Cyril

There are a couple of ways, but the easiest is to perform updates in a transaction.

  Hello Bill,

  I think you've got a breakthrough right there. Yep my data provider is Jet. Any ideas on how I can make sure the data is updated before I do the retrieval operation?

  Thanks for that keyword 'lazy write' I will try to do google on it and see if any one else has had the problem.

  Thanks
  Cyril

Hello Bill!

Thanks for that tip about updating using Transactions, it worked wonderfully. I also found out that this problem occurs with ADO too, but there's an easy worked using the Jet Access & Replication Objects (JRO).

Thanks for the help!

Cheers!

We all get by with a little help from friends. :)

2nd layer of filtering?
Problem connecting to sql server
I'd like to programatically move through a table
History of Data Access
Generate DataSet in 2.0?