query-question: mark the Row that is selected in the query

[DraguVaso]

Hi,

I have different VB.NET-applications that threat records from my table. To
be sure a record isn't threated by two applications at the same time, I want
to leave a mark in the record on the moment it is selected, so the other
applications know they don't have to use that record.

It should be something like this:
SELECT TOP 1 * FROM tblMyTable
WHERE Mark IS NULL
UPDATE Mark = 'Application1' WHERE RecID = TheIdOfTheRowThatWasJustSelected

Does anybody knwos how to do this?

Thanks a lot in advance!

Pieter

 

[Jacco Schalkwijk]

You can use one statement:

UPDATE tblMyTable
SET Mark = 'Application1'
WHERE ID = (SELECT TOP 1 ID FROM tblMyTable WHERE Mark IS NULL)

Or you can use a transaction and an exclusivelock, so the row is locked
until you commit the transaction.

BEGIN TRANSACTION
SELECT TOP 1 * FROM tblMyTable WITH (XLOCK )
WHERE Mark IS NULL

UPDATE Mark = 'Application1' WHERE RecID = TheIdOfTheRowThatWasJustSelected
COMMIT TRANSACTION

 

[Keith Kratochvil]

You have not provided a lot of information, so I am going to ask a few
questions..
Are you trying to prevent the row from being updated in multiple places?
There are better methods available. I am sure that others will agree with
that statement: A couple of options that come to mind right away: SQL
Server has a timestamp column that can be used for this type of thing.
Another option: data comparison when you decide to update the row.

Why do you want to "lock" a record when it might not be updated? How do you
unlock the record if the user decides to abandon whatever it is that they
are doing?

 

[DraguVaso]

Well, my application is liek this:

I habe a table that contains SMS, and several SMS-Modems that send the SMS.
But when 1 SMS-Modem is sending an SMS, it shouldn't been send by another
one at the same time.

So I don't really need advanced security-technologies or need to see if it
has been changed orn ot, but only need to be able to see if it's alreaddy
selected by another SMS Modem or not...

I hope this is enough explanation?

Thanks in advance,

Pieter