Ben,

This can be done inside of a stored procedure (are you using SQL Server?). I'm not up-to-date on VFP anymore, so I can only explain it in terms of SQL Server. You will have to translate it.

CREATE PROCEDURE doUpdate (
    @ClientVersion varchar(10)
   ,@MenuData1 varchar(20),
   ,@MenuData2 int
    .
    .
    .
) AS

BEGIN TRANSACTION

    DECLARE @CurrentVersion varchar(10)
    SELECT @CurrentVersion = [ver] FROM [tblTSDBSystem] WITH (SERIALIZABLE)

    IF @ClientVersion > @CurrentVersion
        BEGIN
            UPDATE [tblMenuStructure] SET
                [MenuData1] = @MenuData1
               ,[MenuData2] = @MenuData2
                .
                .
                .

            UPDATE [tblTSDBSystem] SET
                [ver] = @ClientVersion
        END
    ELSE
        BEGIN
            ROLLBACK
        END

    IF @@TRANCOUNT > 0
        COMMIT

GO

The key to making it work is the SERIALIZABLE table locking hint. This will override whatever the default locking method is set to on the server. When client #2 executes this same stored procedure, he will wait until client #1 is finished unless his connection times out first. You will have to handle this possibility by catching the SqlException in the application.

It is still possible that a different application or query will be able to perform a SELECT against the [tblTSDBSystem] table while [tblMenuStructure]is upating if they use the READUNCOMMITED locking hint. But this won't affect your application if all clients are using SERIALIZABLE.

And it is not possible to let the client know in advance that someone else is updating the table. The only way to know if a lock is blocking a request is to execute the request. If it times out, then it is probably being blocked by someone else's update.

If this proves too cumbersome to translate to VFP, you can accomplish the same thing by starting the transaction in your .NET code. Once the transaction has begun, UPDATE the [tblTSDBSystem] with the value that's already in the table to force an exclusive row lock. This lock will be maintained until the transaction is complete.



>I am not sure if I fully understand some of the ideas you supplied below, but let me see if I can address some of them and give you a better understanding of what I need.
>
>Here is my "project"... I need to implement Menu Security Permission for Users of my Client/Server application. After discussing with my fellow programmers, it was decided that the following "structure" would be implemented. There would be a Field added to our system table (tblTSDBSystem), which houses exactly one(1) record only. This field would hold the Version number of the last Client application to have updated the Menu Structure Table (tblMenuStructure). The tblMenuStructure table would be used to help define security permissions in yet another table (which I need not discuss at the moment). When a Client logs into the system, it checks this Field and compares the Version number to its own version number. If the Version stored in the DB is older then the Client's version, then the Client would open the tblMenuStructure table and update all the records inside to make sure that the table had the most up-to-date definition of what the Client's menu structure
>looks like.
>Problem is that using this scenario there is the possibility that more then one Client application (if more than one user happened to have loaded-up at the same time) could be accessing the tblMenuStructure table attempting to do the same exact routine. To avoid this I was hoping to have the Client do the above mentioned check of version number, but then before updating the tblMenuStructure table, it would check if the table is locked, and if not, immediatly lock it. This way no other client can touch this table. Then, any other client that begins the same process, when it checks to see if the table is locked, would find that it is and instead of updating the table, would loop, waiting for the table to unlock before reading the now updated table information.
>
>Your "Stored Procedure" Idea
>I am not sure if I understand how to use a Stored procedure to create/update/delete records from a table that only the Client application has the information to. Also, how would the client know that the table is currently being updated, and when the updating is finished?
>
>Your "Using Begin/Commit Transactions" Idea
>Maybe I am misunderstanding what a transaction is, but I thought transactions were used to prevent a "partial" change from commiting to a table in case of some sort of failure, allowing the system to rollback to a state prior to any data changes. I understand that no other Client will be able to update any records that have been modified during the transaction phase, but this would be a "Record Locking" situation which is not good for what I need.
>
>Static/Shared Class
>I never used these Keywords on any classes yet (still relatively new to .Net) and I am not sure what they would do for me...Could you elaborate what a Static/Shared Class is or would do for me?
>
>Thank you for your input, look forward to hearing your ideas. :-)
>
>>Ben,
>>
>>The techniques available depend on the database provider. ADO.NET is designed to let the database control it's own locks. From your description of the problem, it appears that the entire process can be encapsulated in a single stored procedure/function. If you declaratively begin and commit a transaction inside of the sp/function, the problem will be solved.
>>
>>If you absolutely have to do it in multiple client-side steps, you can begin and commit a transaction in ADO.NET with:
>>
>>

>>Dim myConnection As SqlConnection = New SqlConnection(.......)
>>myConnection.Open()
>>Dim t As SqlTransaction = myConnection.BeginTransaction()
>>
>>' Do database updates here
>>
>>t.Commit()
>>myConnection.Close()
>>

>>
>>Keep in mind that a transaction may or may not prevent a user from viewing the data depending on the locking hints in the SELECT that they are running, but updates cannot happen to the same rows while the transaction is open.
>>
>>Perhaps the problem is better solved by creating a static/shared class that performs the routine. Add a property called IsUpdating and check it before allowing the update to proceed. With this method you don't have to worry about database locking at all.