ADO Recordset.EOF

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

:-)

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?

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..

:-)

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

I'm usng ADOBD. I have no idea what ADOBC is.

That's a typo! I meant ADODB.

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.

I've just tried compiling my DLL and it says "Method or Data Member not found"

Mmm.....

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

Regards
Dave

Hi,

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

Are your codes similar to that?

Regards
Dave

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
    ...
    ...

Mmm....

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

Regards
Dave

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..

:-)

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.

Tried mine?

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

:-)

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..

:-)

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

Nah, the Last was a long shot from a rusty mind..

:-)

Did you try counting the number of records and looping a similar number of times?

:-)

That's okay, I have that problem sometimes, even when I'm coding quite frequently :-(

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?

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

:-)

Oops, I meant For..Next.

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.

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?

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?

:-)

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 :-(

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?

Maybe that's another question altogether.

Thanks for the help!