[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 623
  • Last Modified:

ADO Recordset.EOF

Hi,

I'm trying to iterate through a recordset object by using the methods movefirst, movelast, and movenext. The objective is to test to see if a column in the recordset contains the same value as a user input (dSD) a row at a time. I've got a counter that keeps track of any matches, so that if variable iDuplicate is not 0 then I know there has been at least one match. If it iterates through the entire recordset without any matches, iDuplicate will be 0.

  iDuplicate = 0                              
  objRST.MoveFirst
  Do While Not objRST.EOF
    If dSD = objRST("sd") Then
      iDuplicate = iDuplicate + 1
    End If
    objRST.MoveNext
  Loop

The problem is, I keep getting this error:

"3021,ADODB.Field,Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record."

I used to write DAO in similar fashion without problems. Obviously there is a problem somewhere in the above snippet of code, but I can not see where it's at. Can anyone pick it up?

Regards,
Frohike
0
Frohike
Asked:
Frohike
  • 16
  • 10
  • 4
1 Solution
 
andrebaCommented:
Try:

iDuplicate = 0                              
  objRST.Refresh
  objRST.MoveFirst
  Do While Not objRST.EOF
    If dSD = objRST("sd") Then
      iDuplicate = iDuplicate + 1
    End If
    objRST.MoveNext
  Loop

:-)
0
 
FrohikeAuthor Commented:
I don't seem to have the Refresh method as I am using VB6.

If I may ask, what is the logic behind the recordset refresh?
0
 
andrebaCommented:
When I used VB6, it did have it.. double check on that please

Are you sure your connection is adequately created (the Jet Engine and link to DB)?

Refreshing should get the data back from the DB before proceeding with manipulation: very useful to test if everything is adequately set up..

:-)
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
suprapto45Commented:
Hi Frohike,

You use ADODC, am I right?

  iDuplicate = 0                            
if objRST.recordcount > 0 then  
  objRST.MoveFirst
  Do While Not objRST.EOF
    If dSD = objRST("sd") Then
      iDuplicate = iDuplicate + 1
    End If
    objRST.MoveNext
  Loop
endif

Try that codes :). However, please apologize if that is not working. I am now Java developer but last time I was VB Developer :D.

Regards
Dave
0
 
FrohikeAuthor Commented:
I'm usng ADOBD. I have no idea what ADOBC is.
0
 
FrohikeAuthor Commented:
That's a typo! I meant ADODB.
0
 
FrohikeAuthor Commented:
By the way, the error is occuring somewhere in the loop when it has completed iterated through the recordset.

There is not problem with the movefirst as there are always records in the recordset.
0
 
FrohikeAuthor Commented:
I've just tried compiling my DLL and it says "Method or Data Member not found"
0
 
suprapto45Commented:
Mmm.....

I am sorry, I mean ADODB too. Sorry...I just forgot about that :). Let me try to find samples for you.

Regards
Dave
0
 
suprapto45Commented:
Hi,

http://www.vbmysql.com/articles/visual-basic/no_data_binding.html

Are your codes similar to that?

Regards
Dave
0
 
FrohikeAuthor Commented:
As far as I can see, it looks similar. Here's partial code from the beginnng:

Public Function InsertTable(objConn, ByVal TableName, ByVal WhereClause, FieldNames, FieldValues) As Boolean
  On Error GoTo error_handler

  Dim objCMD As New ADODB.Command
  Dim objRST As New ADODB.Recordset
  Dim sCMD As String

  Dim sPool As String
  Dim sRN As String
  Dim dSD As Date
  Dim iRate As Integer
  Dim iDuplicate As Integer
 
  Set objRST = rsGetGeneralRecordset(objConn, TableName, WhereClause)
  'There will always be at least one record.
  objRST.Sort = "sd"    'Sort asc on sd column.
  objRST.MoveLast       'Go to latest record.
  sPool = "'" & UCase(objRST("pool")) & "'"
  sRN = UCase(Trim(Mid(WhereClause, InStr(WhereClause, "=") + 1)))
  dSD = FieldValues(0)
  iRate = FieldValues(1)
 
  'Check for duplicate Start Date
  iDuplicate = 0
  objRST.MoveFirst
  Do While Not objRST.EOF
    If dSD = objRST("sd") Then
      iDuplicate = iDuplicate + 1
    End If
    objRST.MoveNext
  Loop
 
  Set objCMD.ActiveConnection = objConn

  If iDuplicate = 0 Then
 
    If Not (iRate = objRST("rate")) Then
    ...
    ...
0
 
suprapto45Commented:
Mmm....

It seems okay with me too. Which line of codes that the debug send you into?

Regards
Dave
0
 
andrebaCommented:
Try this

 Do While Not objRST.Last
   
   objRST.MoveNext
   If dSD = objRST("sd") Then
      iDuplicate = iDuplicate + 1
    End If
   
  Loop

Let us know if your error is gone please.. It doesn't solve your prob, but it will help usdetermine where does the problem lie..

:-)
0
 
FrohikeAuthor Commented:
That's a good question. I did not have it on debug mode because I don't know how to debug this in a DLL. I've written a test harness on another project to call this module, but I can only break at the test harness and not at the DLL. Do you know a way to use breakpoints at both the DLL and the test harness?

To overcome this, I have written some more code within the DLL module so that it writes a stream to a text file. This also helps, I guess. Using this, I found the problem somewhere in the Do..Loop.

I've done a recordcount of the recordset and it returned 4.

The Do..Loop had already completed 4 iterations before it fails.

It fails when it tries to do MoveNext after EOF has already been reached and tries to go one more. I read on MSDN that when EOF is reached, there is no current record. So if it tried to go forward once more, it throws an error.

My loop structure seems logically sound. I think. I can't see the problem at all.

0
 
andrebaCommented:
Tried mine?

I see the problem.. Let me work a solution..

:-)
0
 
andrebaCommented:
Man.. I'm so rusty in this..

Your problem is that you enter the loop when your LAST record is being analyzed (as it should) but then it tries to MoveNext into EOF, which gives an error.. Don't we have a way to only go through the loop X amount of times by counting the total number of records prior to the comparison?

Or is there something like the objRST.Last that I mentioned? That would be enough..

:-)
0
 
FrohikeAuthor Commented:
andreba, from my partial code, I have sorted the recordset and then moved the cursor to the last record to obtain the required data. From what I can gather, your code will not execute from the very outset of the loop, since the current record is already the very last. That is why I have a MoveFirst outside the loop.

You must be using another version of VB because I don't have the Last method either. I'm using VB6 SP5 with the following References:

1. VB for Apps
2. VB Runtime Objs and Procs
3. VB Objs and Procs
4. OLE Automation
5. MS ActiveX Data Objects 2.5 Lib
6. MS Scripting Runtime
0
 
andrebaCommented:
Nah, the Last was a long shot from a rusty mind..

:-)
0
 
andrebaCommented:
Did you try counting the number of records and looping a similar number of times?

:-)
0
 
FrohikeAuthor Commented:
That's okay, I have that problem sometimes, even when I'm coding quite frequently :-(
0
 
FrohikeAuthor Commented:
I've counted 4 from the recordset. But, I haven't tried looping through it for 4 times.

Are you recommending that I use a For..Loop to do this instead of Do While..Loop?

0
 
andrebaCommented:
Yups, as long as you can get the number of records in advance..

:-)
0
 
FrohikeAuthor Commented:
Oops, I meant For..Next.
0
 
FrohikeAuthor Commented:
Okay, will give it a whirl.

The question still remains why doesn't it work by testing for EOF using Do While..Loop or While..Wend.

I mean, I used this all the time with DAO in Access and it never failed once.
0
 
FrohikeAuthor Commented:
I think this method is only good if you want to iterate the columns by using Index.

I mean, how would you select the column and also iterate through the rows using this method?
0
 
andrebaCommented:
WARNING, NO IDEA ON SYNTAX :-p

objRST.MoveFirst
process the first record outside the loop
For i = 1 to 10 do
objRST.movenext
operations
next i

Am I making any sense?

:-)
0
 
FrohikeAuthor Commented:
I tried doing this and it doesn't work. It produces the same error. I think this is because we are still using MoveNext , and we should be using the i count to control record movement. But I don't know how :-(
0
 
FrohikeAuthor Commented:
Uhhh *sigh*

Silly me, I found where the problem is at. And all this time I thought it was inside the Do While..Loop.

In my partial code at the second IF block, I had inadvertently used objRST whilst EOF is true as a result of the Do While..loop. I should have stored that value of 'rate' in a var first prior to doing the loop:

 If iDuplicate = 0 Then
 
    If Not (iRate = objRST("rate")) Then
    ...
    ...

I knew it was something silly.

The lesson to be learnt here for me is to debug properly. One other question if I may. How do I get VB6 into debug mode for the DLL when I'm using a test harness in another project. Basically, I have two VB6 sessions opened, and when I call a DLL function from the test harness, I want to step through the DLL?

0
 
andrebaCommented:
lol
Glad you solved your first prob..
No idea on your second prob either..

:-)
0
 
FrohikeAuthor Commented:
Maybe that's another question altogether.

Thanks for the help!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 16
  • 10
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now