firstly, take a look at the FAQ on this stuff - it's called
"TIP:PdoxWin:Incrementing Primary Keys:2001.05.26" on the
Paradox-faqs newsgroup and on the Paradox Community site, and
"Incrementing Values (Keys)" in the FAQs on my site
(http://mirwin.homestead.com). Either way, you should get a
download of a set of files to illustrate how to achieve things
with Paradox tables.
"But I'm using SQL" you protest. Well, a bit of explanation
never hurt ! Anyhow, you haven't specified which server you're
Let's assume that you're using Microsoft SQL Server, which has
Transact-SQL for a language. When you are using MS Sql Server
you should, really, be using an autoincrement field within the
SQL Server table.
Alternatively, it's fairly simple to write a stored procedure on
MS SQL Server to increment a value in a table and return the
result to you.
For example, let's say that you have several tables, each of
which has an Integer (32 bit) key. You'll want to provide the SP
with the table name and receive back the new value ...
and execute the SP as needed. So long as you always use the SP
to add new records you shouldn't have any problems.
Finally, you can add a trigger to your SQL Server table, that
fires after insertion of a new record, and which does the same
thing as the code above, in that it goes and gets a value and
uses that. In this way an "auto" field in SQL Server is like a
I don't have an instance of SQL Server here in front of me to
try things out on, so I'll check it tonight and post again then
> If I access an SQL table (one record, one longint field) from Paradox via an
> OBDC connection with a tcursor
> and if I can lock the record I grab the value, increment it by one and then
> post and unlock the record.
> While I have the record locked (tc.lockrecord() returns true) is there any
> sort of SQL or other mechanism that prevents any other non-Paradox app from
> accessing the table and grabbing and incrementing at the same time?
> These are order numbers. Twice in 4 months (125,000 orders) we have seen
> duplicate order numbers retrieved by the Paradox app and by another
> non-Paradox app getting order numbers from the same source.
> Roger Engdahl
> 651 438 5931
> 612 819 7088