About Locking a Table

[pvenkatesh2k4]

hi to all,
i develped an application with C# and Sql server2000. in that application, it will gets the maximum of Id from the Table and inserts a new record with maximum of ID+1. this function will be called by many threads.this leads to collision when two threads called the function at same time. so both thread will get the same Id. in order to avoid this i need to lock the table before selceting the maximum value of ID from Table. i dont know how to lock that table.Thanks in Advance.

[Plater]

Can you not use an auto-increment field in the table design?

[Curtis Rutland]

Can you not use an auto-increment field in the table design?

See, that's the first thing that I thought about for this guy's answer. However, I am interested in locking the tables too. There's a project I'm doing that involves working through a queue, and I don't want two clients to get the same row. If there's a way to lock a table, there must be a way to lock a row too. So if anybody knows...?

[Plater]

I am pretty sure there is. You do something to the tables in the master database or something I think. Can't say as I've ever done it though.
We have an ODBC connection to link SQL backend tables to ms access, and if someone's access program is looking at a record, the ODBC locks it down