What's the right way in SQL to handle multiple users trying to update a record? Is there any way to 'lock' the row?

The "right way" may be based on multiple factors in your company - workflow, etc.


In SQL Server, you use isolation levels.

Additionally, if you're using the more "optimistic" approach, you may wish to use a RowVersion data type.

Locking in SQL Server is different than it is in, say, Fox. Even in comparable functions (e.g. SQL Server has a serializable isolation level that is "close" to a file lock, there are still functionality differences)

I would study isolation levels - there is definitely a learning curve, so don't expect to "get it" overnight.

I wrote some articles on this a few years ago: here's one you can read, with some code samples:
http://www.setfocus.com/TechnicalArticles/Articles/sql-server-2005-newsnapshotisolationlevel.aspx