>>>I am writing my first c/s app using VFP5 and SQL Server 6.5. My problem is this:
>>>
>>>I want to add, for an example, a new record into my customer table. My fox app contains a remote view containing all fields in the customer table, parameterised so that it fetches back only one record, any record. I then do an INSERT into the view, to insert the basic information, allowing my user to fill in the rest.
>>>
>>>I do not fill in the cust_id field, as this is automatically populated by SQL Server using IDENTITY. Therefore in my view it is zero (although the record that has just been added on the server has a cust_id). How do I find out the cust_id of the customer that I have just added?
>>
>>You need to query the server for the global variable
>>
>>@@identity
>>
>>Unfortunately this will mean two SQLs for every insert. I always specify what the id should be from the front end myself.
>>
>>Ian
>
>Hi Ian, it's nice to hear from you again!
>
>As you may have gathered from the last time that you helped me out, I am now using Fox and SQL Server rather than Fox and a remote Fox DB. The problem with allocating an Id from the front end (as I see it) is that all of my users will have their own local databases and exe's and therefore their individual number allocation will get out of step if I store the next valid reference for each table in their local DB's. The only way that I can see that I could do this in that way would be to have a common Fox DB or something located on a file server that they can all access. This would mean a SQL DB on the server, a Fox DB on the Server and DB on each workstation. Is this the way that you do it?

Hi Steve - its nice to be of use again!

The current system I'm working on, using a table with one record on the SQL Server. We have unique Id's across the whole system (therefore no Customers with the same ids as Employees etc..)

We have a stored procedure which returns back the next id. It perofrms the locking and manages conflicts.

You could have one row for each table in your system of course.

Something you might try is having two connections to your database - one for accessing the data ( we use SQLEXEC not views, but you can share all the views on one connection), and one for accessing the next id. That way, if you have any transactions, then the id table won't be locked or rolled back if something goes wrong!

Hope this helps.

Ian

One other thing - be wary of transactions - If you use them in SQL

BEGIN TRAN

BEGIN TRAN

ROLLBACK

the above will rollback both transactions and not just the middle one!