Visual Basic

22-Oct-09 6:11

This is the code:

With Adodc1.Recordset
.MoveFirst()
Do While .EOF = False
Debug.Print(.Fields("Name").Value)
.MoveNext()
Loop
End With

The problem: Adodc1.Recordset.Eof is never set to False thus the loop never ends. Is this a bug in VB or is there some option/parameter that I'm not using?

Henry Minute22-Oct-09 6:51

Henry Minute

22-Oct-09 6:51

If you are using VB Express 2008, why are you still using RecordSet?

Shouldn't you be trying to move this to DataSet?

Henry Minute

Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”

Les Singletary22-Oct-09 7:11

22-Oct-09 7:11

Thanks for your reply. Use of Datasets is another topic. The VB Express documentation clearly states "You can use the BOF and EOF properties to determine whether an Recordset object contains rows or whether you have gone beyond the limits of an Recordset as you move from row to row."

Henry Minute22-Oct-09 9:36

Henry Minute

22-Oct-09 9:36

Sorry for the delay in responding, I had to step out for a while.

Les Singletary wrote:
The VB Express documentation clearly states "You can use the BOF and EOF properties to determine whether an Recordset object contains rows or whether you have gone beyond the limits of an Recordset as you move from row to row."

What a shame they don't give an example.

I searched on my local MSDN before my previous reply and all I could find was the ADO compared to ADO.Net page for a search for RecordSet. For EOF I got a reference to a completely unrelated Eof() function.

Since your reply I Googled Recordset.EOF and got quite a lot of hits although not many had code samples. One I looked at suggested

Do While Not .EOF
........
.......
Loop

If you haven't already solved this, I hope that this helps. Smile | :)

Les Singletary22-Oct-09 10:33

22-Oct-09 10:33

Thanks again. I too found lots of usless post on the web for RecordSet.Eof. The example you posted depends on VB correctly setting the .Eof property when that event occurs, which as far as I can determine, it does not.

Dave Kreskowiak22-Oct-09 10:56

22-Oct-09 10:56

Yes, it does. I tried it with a small query and had no issues. VB doesn't set that value, the internals of the ADODB object does.

A guide to posting questions on CodeProject[^]

Dave Kreskowiak
Microsoft MVP
Visual Developer - Visual Basic
2006, 2007, 2008
But no longer in 2009...

Les Singletary22-Oct-09 11:05

22-Oct-09 11:05

Yes, I know that the AdoDb works as I said. The problem is with AdoDc.

Dave Kreskowiak22-Oct-09 12:39

22-Oct-09 12:39

The answer doesn't change. That property value is only set by the internals of the object, not by VB.

A guide to posting questions on CodeProject[^]

Dave Kreskowiak
Microsoft MVP
Visual Developer - Visual Basic
2006, 2007, 2008
But no longer in 2009...

Henry Minute22-Oct-09 11:30

Henry Minute

22-Oct-09 11:30

I am trying to remember something weird about the .BOF and .EOF properties from certain data access controls from my Delphi Days. It went something along the lines that if you set .BOF to true it actually 'points' to an imaginary record one before the physical BOF. So I am wondering if there might be something similar going on in your case. i.e. Is there just one record returned and .EOF is pointing past that record.

I hope that that makes some sort of sense. Smile | :)

Trying to explain it to anyone always confused me in Delphi and it appears not to have got any better. Laugh | :laugh:

Dave Kreskowiak22-Oct-09 9:31

22-Oct-09 9:31

You have VB.NET Express and you're using the old ADO Data Control?? Why?? The ADO.NET methods are faster and more flexible.

No, this isn't a bug in VB. How many records were returned in the query?

A guide to posting questions on CodeProject[^]

Dave Kreskowiak
Microsoft MVP
Visual Developer - Visual Basic
2006, 2007, 2008
But no longer in 2009...

Les Singletary22-Oct-09 10:24

22-Oct-09 10:24

Thanks for your reply. Using ADO.NET is another matter. The number of records returned depends on the number of records in the database at the time. I know how to "get around" this problem. However, I am searching for an answer to how to get the ADODC control to function properly using the .RecordSet.Eof property. BTW, the ADODB object functions correctly.

Dave Kreskowiak22-Oct-09 10:55

22-Oct-09 10:55

Les Singletary wrote:
Using ADO.NET is another matter.

That doesn't answer the question. Using ADO.NET instead of deprecated, obsolete objects, would most assuredly solve your problem, as well as prevent others, such a binding controls to your data. You cannot bind any .NET control or class to the data held in the old ADOxx objects. This severely limits your ability to do data manipulation while using those objects.

A guide to posting questions on CodeProject[^]

Dave Kreskowiak
Microsoft MVP
Visual Developer - Visual Basic
2006, 2007, 2008
But no longer in 2009...

Les Singletary22-Oct-09 11:13

22-Oct-09 11:13

Thanks again for your reply. However, your last reply opens up a whole "can of worms" that I prefer not to get into with this particular problem. I would like to use ADODC if it can be made to work. I will try to post the complete zipped "solution" file later.

Dave Kreskowiak22-Oct-09 12:41

22-Oct-09 12:41

Don't bother to post the project. Noone will download it and execute untrusted code.

A guide to posting questions on CodeProject[^]

Dave Kreskowiak
Microsoft MVP
Visual Developer - Visual Basic
2006, 2007, 2008
But no longer in 2009...

Les Singletary22-Oct-09 11:33

22-Oct-09 11:33

Here is the code for the entire program demostrating the problem. I don't seem to be able to attach a zipped file. You will need to create a form with three controls: Button1, Button2, and Adodc1. Name the form "frmAdoDc". You will need a simple MS Access DB named "Stu" with a table named "Student". The table contains 2 columns: "Id" and "Name". Put the database in the same folder where you store the code below. I hope some one can find how to make this code work. Thanks!
---------------------------------------------------------
Imports ADODB

Public Class frmAdoDc
   Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
      Dim iX As Integer

      With Adodc1.Recordset
         .MoveFirst()
         Do While Adodc1.EOF = False And iX < 10
            iX = iX + 1   'needed because of problem
            Debug.Print(.Fields("Name").Value)
            .MoveNext()
         Loop
      End With
   End Sub

   Private Sub frmAdoDc_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
      Dim sDir As String = "AdoDc_Example"      'path of root folder for solution
      Dim sDb As String = "Stu.Mdb"               'name of database
      Dim sFid As String                                 'path & name of database
      Dim iPos As Integer
      '---build path where DB is regardless of where this test is stored
      sFid = My.Application.Info.DirectoryPath
      iPos = sFid.IndexOf(sDir)
      sFid = sFid.Substring(0, iPos + sDir.Length + 1) & sDb

      With Adodc1
         .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                              "Data Source=" & sFid & ";"
         .RecordSource = "Student"
         .Refresh()
      End With
   End Sub

   Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
      Dim iX As Integer
      Dim db As New ADODB.Connection
      Dim rs As New ADODB.Recordset

      db.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _
                           "Data Source=C:\VbNet_Test\AdoBasics\Stu2.mdb;")
      rs.Open("Select * From Student", db, CursorTypeEnum.adOpenDynamic)

      With rs
         .MoveFirst()
         Do While iX < 10 And .EOF = False
            Debug.Print(.Fields("Name").Value)
            iX = iX + 1
            .MoveNext()
         Loop
      End With

      rs.Close()
      db.Close()
   End Sub
End Class

Les Singletary22-Oct-09 15:05