Contact US

Question

Error 5 record out of range strangeness

Dobs_seemore (IS/IT--Management)

(OP)

11 Sep 22 16:45

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 (Programmer) · Answer 1 · 2022-09-11 17:51:27

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 (IS/IT--Management) · Answer 2 · 2022-09-11 17:55:09

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 (Programmer) · Answer 3 · 2022-09-11 19:19:31

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 (IS/IT--Management) · Answer 4 · 2022-09-11 19:25:10

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 (Programmer) · Answer 5 · 2022-09-11 19:29:45

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 (IS/IT--Management) · Answer 6 · 2022-09-11 19:33:23

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 (Programmer) · Answer 7 · 2022-09-11 23:09:06

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 (IS/IT--Management) · Answer 8 · 2022-09-13 11:41:25

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 (Programmer) · Answer 9 · 2022-09-13 11:52:56

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 (IS/IT--Management) · Answer 10 · 2022-09-13 11:56:15

That would be embarrassing! Yes Debug is included.

Chris Miller (Programmer) · Answer 11 · 2022-09-13 12:01:53

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 (IS/IT--Management) · Answer 12 · 2022-09-13 12:39:12

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 (TechnicalUser) · Answer 13 · 2022-09-13 16:28:33

Hi,

Instead of

Quote:

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

CODE -->

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

hth

MarK

Chris Miller (Programmer) · Answer 14 · 2022-09-13 18:08:52

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 (IS/IT--Management) · Answer 15 · 2022-09-13 20:07:34

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 (TechnicalUser) · Answer 16 · 2022-09-14 06:21:41

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)

Quote:

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

CODE -->

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

or to avoid confusion

CODE -->

lcCustomerId = Customer.Customer_Id

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

hth

MarK

Dobs_seemore (IS/IT--Management) · Answer 17 · 2022-09-14 08:57:14

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

Chris Miller (Programmer) · Answer 18 · 2022-09-15 07:56:46

Quote (Dobs seemore)
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 (IS/IT--Management) · Answer 19 · 2022-09-15 11:47:55

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.

Dobs_seemore (IS/IT--Management) · Answer 20 · 2022-09-15 11:49:38

Just in case I need to take this deeper, how do you get the debugger to show the trace window once you enter a form? When I traced the program that starts it all the trace window stops on "read events" and stays there while I interact with the form.

Mike Lewis (Programmer) · Answer 21 · 2022-09-15 12:20:10

Quote:
how do you get the debugger to show the trace window once you enter a form?

The easiest way is to set a breakpoint. You do this by double-clicking in the left-hand margin of the code window, or by right-clicking and selecting "Toggle breakpoint". Either way, do this at the line of code at which you want to start debugging.

Although you can (and should) remove the breakpoint when you have finished debugging, no harm is done if you don't do that. Breakpoints only exist in the development environment, and are not carried through to the executable.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

Dobs_seemore (IS/IT--Management) · Answer 22 · 2022-09-15 21:02:44

Well this is unreal. Used it all day with no errors while in FoxPro running the source code. He left and used the EXE and boom got the error.

TamarGranor (Programmer) · Answer 23 · 2022-09-16 20:34:16

So the next question is what's different between the environment where you were running it and the one where the EXE is running:

- any FoxUser file?
- any Config.FPW?
- registry settings?
- paths?
- ...

Tamar

Dobs_seemore (IS/IT--Management) · Answer 24 · 2022-09-18 14:09:27

We finally got the error twice while running the source in VFP. It was on a thisform.frame.xxxxxx.refresh

I looked at all the open tables to see if any of them were at EOF(), one was that should not be that is tied to a COMBOBOX. I think I have narrowed it down to a "LIST" and a "COMBOBOX" that are local tables that get wiped (zapped) and reloaded when a new order is created or an existing order is edited. I think the combo or list is pointing to a record that becomes unavailable when the underlying table is reloaded and a refresh is issued. Why this started happening now versus years of trouble free run time is strange however I think issuing a requery on these objects before a refresh might be what's needed to rebuild the list/combo listindex. I'm also going to convert the list to a grid, as I have no issus reloading the underlying table with grids and then a form refresh or grid refresh.

Chris Miller (Programmer) · Answer 25 · 2022-09-18 14:39:21

You address an issue that's rarely talked about: Even when most controls are driven by data, i.e. a workarea, listbox and combobox are loading items into an internal items array. That's redone when you requery them, so REQUERY is not just a method to use when you use a rowsource type of SQL query, it's actually requerying the items of the control itself.

I made the experience this makes listbox and combobox more stable and less sensitive to lost rowsource. The grid is infamous to break, if the recordsource vanishes, even just for split seconds if you do a query INTO grid.recordsource and overwrite the existing grid cursor with new data. The grid is not sensitive if such a requery is done on a view, as the view workarea stays in place during the whole requery process (and here I'm talking about the REQUERY() function for views, the grid control has no requery method, as it works differently in that respect.

I can't answer why this didn't show up for years, but it's not the first time something works long lasting and finally breaks for some unknown reason.

I do remember commbo nd list breaking mostly when you change active forms. When something is askew while a form is reactivated it breaks. A form.refresh is quite similar to such a situation and I often see people putting way to much code into the refresh event, which they don't realize causes further refresh events in what is a cascading method anyway. I learned the hard w ost often a setfocus is the better alternative to a control.refresh, if it comes to a single control. If you want to refresh a form, the form.refresh() is a natural way, but you need to realize you're shaking up everything on the form. The less code you have in refresh, the less side effects. So that's a warning of what you say you plan to do.

I would take a "snapshot" of what happens with event tracking and coverage logging could also help. You might detect loopbacks where you don't expect them to happen.

But in detail you might be very right about the suspicion that a requery before a refresh would be good, as I think a refresh does cause many things but does not cause a requery in itself.

Chriss

Dobs_seemore (IS/IT--Management) · Answer 26 · 2022-09-18 15:06:33

Chriss
To keep things simple do you think a .requery in the refresh method of lists and combos would cure this?

Chris Miller (Programmer) · Answer 27 · 2022-09-18 15:53:52

It's worth a try. If not, you'd need to unbind the controls that trigger an error and rebind them after data has been redone.

Zapping a cursor is never disturbing a grid, it might so for combo/list because of the items, so changing to a grid also could already "heal" it.

Chriss

Dobs_seemore (IS/IT--Management) · Answer 28 · 2022-09-18 18:33:00

I created a test form with a pageframe and a combo box with some buttons to refresh and requery. Damn if I can get it to error so I can understand specifically what is going on here.

Contact US

Come Join Us!

Posting Guidelines

Error 5 record out of range strangeness

Error 5 record out of range strangeness

Error 5 record out of range strangeness

RE: Error 5 record out of range strangeness

RE: Error 5 record out of range strangeness

RE: Error 5 record out of range strangeness

RE: Error 5 record out of range strangeness

RE: Error 5 record out of range strangeness

RE: Error 5 record out of range strangeness

RE: Error 5 record out of range strangeness

RE: Error 5 record out of range strangeness

RE: Error 5 record out of range strangeness

RE: Error 5 record out of range strangeness

RE: Error 5 record out of range strangeness

RE: Error 5 record out of range strangeness

RE: Error 5 record out of range strangeness

Quote:

CODE -->

RE: Error 5 record out of range strangeness

RE: Error 5 record out of range strangeness

RE: Error 5 record out of range strangeness

Quote:

CODE -->

CODE -->

RE: Error 5 record out of range strangeness

RE: Error 5 record out of range strangeness

Quote (Dobs seemore)

RE: Error 5 record out of range strangeness

RE: Error 5 record out of range strangeness

RE: Error 5 record out of range strangeness

Quote:

RE: Error 5 record out of range strangeness

RE: Error 5 record out of range strangeness

RE: Error 5 record out of range strangeness

RE: Error 5 record out of range strangeness

RE: Error 5 record out of range strangeness

RE: Error 5 record out of range strangeness

RE: Error 5 record out of range strangeness

Red Flag This Post

Red Flag Submitted

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Join Tek-Tips® Today!

Join Tek-Tips^® Today!