how to lock the row or table when update?

Hi,
I am working on a project which need to produce a sequential certificate number, Everytime I need a new certificate number, I need to find out what is the max number in the database and then the new certificate number just max+1.
But how can I block another
transaction to check what is max certi. number while this transaction is in the middle of writing the new certificate number(max+1) into database . Does ADLockOptimistic work in this case? Here is the code:
My database sql 2000.

cmdTemp.CommandText="Select max(certificateNumber) from product_table where certificateNumber<> 8888888"
set cert_info=Server.CreateObject("ADODB.RecordSet")
cert_info.Open cmdTemp, , AdOpenKeySet, adLockOptimistic
If Not cert_info.EOF then

certnumber=cert_info(0)+1

End if
cert_info.close
set cert_info=nothing

Use update lock to hold lock for the duration of the transaction.

Select max(certificateNumber) from product_table WITH (UPDLOCK) where certificateNumber<> 8888888

Skhanal,
Thank you so much. The very interesting
thing is when I search in the google.
There are a lot of article about concept sharelock, updatelock etc., I just got a hint I need to use update lock, but I just didn't find an easy sample there.
Thanks for your valuable posting and it save me a lot of time.
Betty

This is listed in sql books online.

Is there a reason why you can't simply use an identity field?

Skhanal,
following the code junk in the first posting. I have the following code which I check if the new record is in the database,if not, I add a new record with the new certificate number I just got.
/***********
cmdTemp.CommandText = "select * from Product_table where order_id='" & order_id & "'"
Set order_info = Server.CreateObject("ADODB.Recordset")
order_info.Open cmdTemp, , adOpenKeyset, adLockOptimistic
if order_info.EOF then
order_info.AddNew
end if

order_info("CertificateNumber").Value=certnumber
...
'fill with other field data
order_info.update
/********
Now the question is: will the second select statement from the product_table will still execute. Is the lock going to be released after the line order_info.update? thanks
Betty

Rawhide,rmiao
I think you mean the priamry ID key.
we have very specific requirements for our certificate number. If I want to use Identity field, I need to reset seed for the identity field. I am just a developer, I just don't know much about database. Usually don't want to touch which I don't know much.
rmiao, would you send me the link for this issue. I want to read more.
Thanks
Betty

This method will cause you some serious performance issues it is a moderately busy site. While you are locking the table, other queries will build up in the queue. At the very least, you will experience timeouts. At the worst, deadlocking.

A field doesn't have to be the primary key to be an identity field.

You should only need to set the seed and increment value once for an identity field.

Rawhide,
The problem is the new identity is known after the insertion of the record.
while I need this certificate number to write to another document before the insertion.
By the way, do you mean after
the statement:
Select max(certificateNumber) from product_table WITH (UPDLOCK) where certificateNumber<> 8888888
I cannot have the following statement:
cmdTemp.CommandText = "select * from Product_table where order_id='" & order_id & "'"

No, UPDLOCK wouldn't block you from reading. It would only block you from performing other inserts or updates