>>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
>> 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...
If it's not broken, fix it until it is.
My Blog