Error 5 record out of range strangeness

[Dobs_seemore]

Usually this error is simple, you tried to go where a record does not exist, however this one is strange and is happening repeatedly on two computers out of 60 that run it. The rest of the people who use it have not seen the error which makes this all the more interesting, but could simply be that they use it in a slightly different way, now to the facts.

The application runs locally and it not a shared or network application. It is a form with a 6 tab page frame. The error 5 when trapped is telling me that on page frame 1 a list object with an SQL statement as it's row source is what's causing the problem. What is interesting is that when the error occurs it's happening while the user is interacting with objects on page frame 2 which programmatically does not touch page frame 1 or any of it's objects nor does it cause any type of requery. As a test I disabled everything on page frame 1 while they are on page frame 2 using .setall so every object was disabled, still the error persisted.

Example error messages
Error number : 5
Program : OE.FRAME.ORDERGUIDE.OITEMSEARCH.LOSTFOCUS
Line number : 32
Line of code : select invoice_date,lines,quantity,sales from saleshistory where customer_id=customer.customer_id order by invoice_date desc into cursor saleshist
Error message: Record is out of range.

Error number : 5
Program : OE.FRAME.ORDERGUIDE.OITEMSEARCH.GOTFOCUS
Line number : 11
Line of code : select invoice_date,lines,quantity,sales from saleshistory where customer_id=customer.customer_id order by invoice_date desc into cursor saleshist
Error message: Record is out of range.

Error number : 5
Program : OE.EXISTINGORDER
Line number : 173
Line of code : select invoice_date,lines,quantity,sales from saleshistory where customer_id=customer.customer_id order by invoice_date desc into cursor saleshist
Error message: Record is out of range.

oe.existingorder is a method on the base form that I call when they edit an order, the other two got and lost focus are on page frame 2 of a search text box. Again these objects/methods do not touch or refresh anything on page frame 1 where the list is with the SQL statement. It is one of those strange things that started out of nowhere and in happening on 2 of the 60 people who use it. We cannot duplicate the problem ourselves using the application, usually we can.

Now to make things more complex this message happens on occaision
Error number : 202
Program : OE.FRAME.ORDERGUIDE.DONE.CLICK
Line number : 99
Line of code : select invoice_date,lines,quantity,sales from saleshistory where customer_id=customer.customer_id order by invoice_date desc into cursor saleshist
Error message: Invalid path or file name.

BTW just to be clear on all of the errors above the "Line of Code" is the list box with a row source SQL statement that you see and is located on
OE.FRAME.CUSTOMER.SALESHISTORY <- Saleshistory being the list box object.

The error is related to the same object on page frame 1, the list with a SQL statement as the row source, yet there is nothing related to a path or file name,the statement returns results to a named cursor not a dbf file with a path. Very confusing and I'm beginning to think some other application on the local machines are causing it in some odd way. This is Windows 11 and Fox 9 with SP2 and the group header hot fix installed from article 968409

Thanks, this has been going on for a week and I've been through some strange issues with Fox over the years but this one finally has me asking for help here.

 

[Chris Miller]

The error can only happen, if an index node points to a record number that does not exist in the DBF anymore, or vice versa, the index already has an entry for a record that's not yet stored in the DBF. In any case CDX or IDX and DBF are out of sync.

REINDEX might help, but the question is why the data gets out of sync and that could be an SMB protocol issue.

Chriss

 

[Dobs_seemore]

Normally yes I'd agree however the offending line of code is a SQL statement returning records to a SQL CURSOR. A DBF and or Index does not exist in this case which has me really confused.

However I'm wondering if it is a local DBF/Index issue and the line of code returned by the error is not related at all, hrmmm?

 

[Chris Miller]

Well, your SQL Statement could easily be optimized by an index. And it must be. It's the only way you can get access to a non existent recno.

Chriss

 

[Dobs_seemore]

I was not considering the index of the table (selecting from). The table it selects from is using an index on the customer_id however that table and index are copied over to their laptop or desktop every morning when they update their system. I'd think that everyone would get the error as the original copy of that DBF and index would be corrupt before it's copied locally. I will however issue a reindex when I open that table and see what happens.

thanks for all the replies.

 

[Chris Miller]

Well, you can easily have a mismatch of index and dbf if you copy files. Depending on how large they are, one is copied before the other and if it changes during that time it would explain the index pointing to non-existing records.

Chriss

 

[Dobs_seemore]

Understood. It's not the case though. All of the tables are empty to start with, our server loads the tables with new info from scratch. Once they are loaded they are not touched and are copied down to the laptop/desktop when they update their system. Each user has a folder on the server with their own fresh copy to download as not everyone gets the same info.

I think your original comment about the index of the source table selecting from sounds like the problem. We shall see what happens this week. It is still strange that users interacting with objects on another frame that programmatically do not touch or refresh objects on the other frame are causing this to happen.

 

[Chris Miller]

Yes, it's strange, as it means the query has to run once more without a listbox.requery()

I never use a query as a rowsource, so one of the things you can also do is put that query into init of the listbox and set the rowsourcetype to alias or fields instead.

Chriss

 

[Dobs_seemore]

Finally getting somewhere.

I found out that using the application as a stand alone .EXE the error routine is reporting the WRONG line of code. If I run the application in FoxPro development and force a record out of range by running "go 50000" where there is only 3900 records the error routine give the correct line of code "go 50000" however when I run it as an executable it give me a line of code that has nothing to do with the offending code.

Is there a way to fix this or a trick you need for .exe to report the correct line of code? Until them I'm going to have some people run the application in VFP and not an EXE to find out what is going on.

 

[Mike Lewis]

Just a guess, but could this be happening because you have not ticked "Debug info" in the Project Information dialogue?

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Dobs_seemore]

That would be embarrassing! Yes Debug is included.

 

[Chris Miller]

You could also use SET COVERAGE to log what's executed and the last line before an ON ERROR fires will be the offending line. The coverage file easily grows very fast, but doesn't effect the performance very much. So you could start it when you start the form and end it when the form is closed to record a form session.

Besides that, Mike is right about the debug info being important to get correct line numbers of error handling with the LINENO() parameter or within AERROR or ASTACKINFO.

If the source is - as I suspect a QL query the line of code should be it, but you'll not find out the offending index entry or record number. The only other non code source of the problem could be by a RELATION automatically navigating by index to a non existing record number.

The problem could also be as easy as a GO RECNO, notice in buffered mode new records get negative RECNO() and that only changes when they have been stored to the DBF in a final position. So GOing to a record number within 1 to RECCOUNT() could not work for numbers near the end of the buffered workrea.

Chriss

 

[Dobs_seemore]

Chris thanks for all the replies on your comment about the "SQL query line of code should be it" when I put a "go 50000" just to trip the error it reports "go 50000" when running it in FoxPro, when it's an executable it reports an SQL statement instead of the "go 50000" as it should. Strange.

set coverage did not work, it shows the form being created line by line, inits, frames etc but not when I click the button I created that does "go 50000".

The error message while in FoxPro development is correct and reports
Error number : 5
Program : OE.COMMAND1.CLICK
Line number : 1
Line of code : go 50000 in order_temp
Error message: Record is out of range.

Run this as an EXE and you get

Error number : 5
Program : OE.COMMAND1.CLICK
Line number : 1
Line of code : select Created,cdow(order_date), order_date, left(po,14) po,totalquantity,totalamount,reserved,IIF(review=.t.,'Review',' ') review,spins,order_id from orders where customer.customer_id=orders.customer_id order by order_date into cursor customerorders
Error message: Record is out of range.

As you can see the line of code is incorrect - strange behavior as the other information is correct.

 

[mjcmkrsr]

Hi,

Instead of

Line of code : select Created,cdow(order_date), order_date, left(po,14) po,totalquantity,totalamount,reserved,IIF(review=.t.,'Review',' ') review,spins,order_id from orders where customer.customer_id=orders.customer_id order by order_date into cursor

you may want to try

select Created, cdow(order_date), order_date, left(po,14) [b][highlight #FCE94F]AS[/highlight][/b] po, totalquantity, totalamount, reserved, IIF(review=.t.,'Review',' ') [b][highlight #FCE94F]AS[/highlight][/b] review, spins, order_id from orders where customer.customer_id=orders.customer_id order by order_date into cursor

hth

MarK

 

[Chris Miller]

As Mike already said, you need to embed debug info into your EXE, especially to have the right line of source code in the error handling. Anyway, it should make the errors the same in ID vs EXE.

This - very briefly said - embeds all source code, too.

Chriss

 

[Dobs_seemore]

Chris Miller:
I replied that Debug info is checked.


mjcmkrsr:
Read more of the thread, that is not the actual line of code that is the error. For some reason the .EXE is returning different results then when in development and it's driving me a little crazy. Also that select SQL statement has been there for over a decade.


We are going to have someone run the application in FoxPro (not the EXE) and get the real error. I can't wait to see what this is.

Thanks for all the replies.

 

[mjcmkrsr]

Hi,

...

You forgot to list the second table in all the SQL statements and hopefully the field names are unique - otherwise you would have to precede the field name(s) with the table name(s)

select Created,cdow(order_date), order_date, left(po,14) po,totalquantity,totalamount,reserved,IIF(review=.t.,'Review',' ') review,spins,order_id from orders where customer.customer_id=orders.customer_id order by order_date into cursor customerorders

I suggest the following

select Created, cdow(order_date), order_date, left(po,14) [b][highlight #FCE94F]AS[/highlight][/b] po, totalquantity, totalamount, reserved, IIF(review=.t.,'Review',[highlight #FCE94F]Space(6)[/highlight]) [b][highlight #FCE94F]AS[/highlight][highlight #FCE94F][/highlight][/b] review, spins, order_id ;
[indent]from orders ;
[b][highlight #FCE94F]join[/highlight][/b] customer [b][highlight #FCE94F]on[/highlight][/b] customer.customer_id=orders.customer_id ;
order by order_date ;
into cursor customerorders[/indent]

or to avoid confusion

lcCustomerId = Customer.Customer_Id

select Created, cdow(order_date), order_date, left(po,14) [b][highlight #FCE94F]AS[/highlight][/b] po, totalquantity, totalamount, reserved, IIF(review=.t.,'Review',[highlight #FCE94F]Space(6)[/highlight]) [b][highlight #FCE94F]AS[/highlight][highlight #FCE94F][/highlight][/b] review, spins, order_id ;
[indent]from orders ;
[b][highlight #FCE94F]where[/highlight][/b] orders.customer_id = lcCustomerId ;
order by order_date ;
into cursor customerorders[/indent]

hth

MarK

 

[Dobs_seemore]

Mark, read through the thread that SQL statement is not the issue here and works perfectly fine.

 

[Chris Miller]

set coverage did not work, it shows the form being created line by line, inits, frames etc but not when I click the button I created that does "go 50000".

Well, as the first line of your Click method triggers ON ERROR this will be in the coverage log file.

What was the end of the coverage log file? Did you set coverage off somewhere? Btw, You could make it a lot shorter, if you put it at the end of form init.

Chriss

 

[Dobs_seemore]

I just put it in the form init. Good idea. Today we will use the application in VFP running the source code so I can get an accurate error.
Thanks will update when I know more later this afternoon.