Re: How to lock an MSSQL server recordset for read?

Tech-Archive recommends: Fix windows errors by optimizing your registry

Anders Eriksson wrote:
I have a database which has several tables. The main table has a
column called OrderNo. I need to get the first available OrderNo from
this table, mark it as not available and the process the order.

since there are multiple programs doing this simultaneously I have
found that just opening a recordset and setting the Available value
to false and save it back to the database will not work since during
the time it takes
to set the Available column and update the database one or more other
programs will have done the same and multiple programs are now
processing the order.

So what I want is a way of locking the record in the database so that
only the first program can read this record and orderno.

I hope that I have expressed myself clear enough. If not please ask!

That's better. What you need to do is to start a transaction when you
retrieve the order number from the table, and not commit the transaction
until the order is completely processed. This will essentially lock the
record from being updated by another process until the transaction is
complete, as well as relieve you of the necessity of marking the record
"unavailable" while the process is occurring. Whether you use ADO's
transaction functionality (connection.begintran) or SQL Server's in a stored
procedute depends on your process. How long will the processing take? Do you
already have all the data collected to perform the processing? If so, I
would use a stored procedure. If your process involves getting the order
number, and then collecting more data from the user, you shuld probably use
ADO's functionality ... but I would rethink this, what if the user does not
complete the process? Are you prepared to have to handle unlocking tables,
etc.?


--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"


.

Relevant Pages

  • Updating multipile rows from an order history form ( ie same id)
    ... CUSTOMERS PRODUCTS ORDERS ORDERDETAILS ... custid* productid* orderno* orderno ... I have forms to interact with the database to add/search/display etc. ... hidden form field 'custid' to link the data, but am not sure how to ...
    (alt.php)
  • Re: How to lock an MSSQL server recordset for read?
    ... I have a database which has several tables. ... I need to get the first available OrderNo from this table, ... In most cases if you open the recordset using a server-side keyset cursor, ... the value of the identity column will be available in the recordset after ...
    (microsoft.public.data.ado)
  • Re: sql select statement not working
    ... Is OrderNo a numeric or text field? ... Sue Mosher, Outlook MVP ... > I am taking information from a custom form i.e. order ... > select that order form the database. ...
    (microsoft.public.outlook.program_forms)
  • Re: Updating multipile rows from an order history form ( ie same id)
    ... >> Yes orderno is unique in the Orders table in the database, ... >> are multiple products possible for each orderno. ... and in a bit of a mess. ... //Close connection with MySQL ...
    (alt.php)