>>>My table had a default value for ID field lNextID('Regions'). In the form I use a p-view based on Regions table only (no other tables). In add method I put the following code (quoted by memory);
>>>

>>>if thisform.EditMode
>>>   return dodefault()
>>>else && Means we want to add
>>>   select RegionsInfo
>>>   lnRegionID=lNextID('Regions')
>>>   insert into RegionsInfo (customerID, RegionID, ...) ;
>>>      values (oJC.CustID, m.lnRegionID,..)
>>>   ...
>>>endif
>>>...
>>>

>>>
>>>Now, the first strange thing is, that it picks up lNextID SP from the wrong database.
>>
>>You mean a similarly named table in a different DBC? If so, it's sounds like the database you think is current isn't. Do you have stored procs with the same name?
>
>In each Database there is a table called NextID. We have lNextID as a function in our wgutlity (common procedure file used across different applications) and we also had the SP with the same name in several databases. In order to avoid the problem I found (not the correct SP was used), I renamed SP in my DBC to GetNext_ID.
>
>>>The second problem is that my RegionID updates like 4 6 8 10 and sometimes in strange reverse order.
>>
>>I'm not sure that I understand this. Is RegionID a PK or a numeric regional indentifier that's used to associate a given area grouping?
>>
>Yes, it's an integer PK in Regions table and FK for RegionTowns table.
>
>>>So, currently in order to solve both problems I renamed my SP to GetNext_ID (I have to change each table in DBC to call this procedure by its name, I didn't change for all tables yet) and I also removed default value from Regions table, though it doesn't seem right, because if I ever would need to edit this table directly, I would be unable to do it.
>>
>>Again, I'm not sure what your doing. It's sounds like you're trying to retrieve a PK. If so, is the appropriate record being locked before you try to retrieve the value and then increment it?
>
>Yes, procedure locks the record. The problem is: I have a p-view based on Regions table called RegionsInfo. When I insert records into this view, I excplicitly call my SP, retrieve the next number and insert record into the view. When I do TableUpdate, the record is inserted into underlaying table (Regions) and by default invokes GetNext_ID again. So I got strange and unwanted results... Currently I get rid of default value in Regions table RegionID, but it doesn't seem right. I can also try to control it through some property or variable (e.g. something like:
>in Add method: (it serves to save also)
>
>private pnRegionID
>pnRegionID=RegionsInfo.RegionID
>....
>
>in Regions RegionID default value: if vartype(pnRegionID="N", m.pnRegionID, GetNext_ID('Regions'))
>
>But it doesn't look kosher to me...

Nadya,

One thought did occur to me. You mention that you have stored procedures by the same name in different databases. VFP may be caching the first one called in memory, the same way it would do with a regular program (prg). Afterall, stored procedures in VFP DBC's are nothing more than compiled Fox code. If it is doing this, it may lead to the wrong code being executed. If so, it might, to some degree, explain the inconsistent results you're seeing. To test this, you could try the same code with and without a CLEAR PROGRAM issued after the call to the stored proc.