Lock A Record In An Access 2007 Database Using VB.NET

Hi Everybody

I am developing a Quotation Management System using VB.NET (Visual Studio 2008) and an Access 2007 Database. One Of The major auditing requirements is that each quotation detail record has to be uniquely identified by a Quotation Number which has to be generated by the application at the time the quotation detail record is saved.

Thiis Number (known as NextQuoteNo) is stored in a Table NextQuote and is the Only Entity in the table. When I access the table, I retrieve NextQuoteNo, allocate it to the New quotation detail record and I increment the NextQuoteNo by One and I save (Update) the Dataset. And this all works like a dream.

My problem however is: What do I do in a Multi-User environment to ensure the Uniqueness of this Quotation Number. This was very easily achieved in VB6. It is not so easy using ADO.NET since the Dataset in question is Disconnected from the Database.

The probability that a Lock will be needed frequently is very Low but it may very well be needed. I have done a search with Google regarding Record Locking in ADO.NET but the information I gather is not very convincing.

For this reason, I hope that someone can shed some lite on the problem for me. I have to ensure the Uniqueness of the number in question.

Many thanks.

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NolancAuthor Commented:

I omitted a very important piece of information for you viz. that the Quotation Number I generate is the Primary Key for the Quotation Detail record. Here is a snippet of my code:
Form Class Frm............
   Private dsDtl As New Dataset
   Private daDtl As OleDb.OleDBDataAdaptor
   Private dsNum as New Dataset
   Private daNum As OleDBDataAdaptor
Form Load Event  ......................
  sSQL = "Select * From NextQuote"
  Call dbConnect()
   daNum = New OleDb.OleDbDataAdapter(sSql, QtCon)
   daNum = Fill(dsNum, "QtNum")
  ....... Similarly For The Quotation Detail Dataset
   Call DbClose()
 BtnSave Event ..............
  Call GetNextNumber()
   ..... Create The Quotation Detail Record And Insert sNumber As The Quote Number
   .... Add The New Quotation Record
End Sub
Private Sub GetNextNumber()
   Dim cb As New OleDb.OleDbCommandBuilder(daNum)
   sNumber = dsNum.Tables("QtNum").Rows(0).Item("NextQuoteNo")
   dsNum.Tables("QtNum").Rows(0).Item("NextQuoteNo")  =   dsNum.Tables   ("QtNum").Rows(0).Item("NextQuoteNo")  + 1
   daNum.Update(dsNum, "QtNum")
End Sub
I trust that I have now provided sufficient information to warrant the necessary help.
Many thanks.

You could retrieve the quotation number and increment by one from your table NextQuote when the user presses the BtnSave button (or whatever in your logic triggers the save event). That only takes less than a second. The chances that several users will be trying to access it at once are low but you could have a retry routine. Surround the code that retrieves the next quote number and increments by one with a try/catch/finally statement. Trap the concurrency error and retry. Or if you want to lock it, lock it and then check for a lock record error and keep retrying. With the locking though you are 'holding up the traffic' since you are unable to move forward until the lock is released. If the lock is legit it wouldn't be long but if it is due to a system issue that left the lock hanging it could lock other users as well.
I would also do the same in your code that saves the record to your quotation detail table. I would check for the duplicate key error and if so do something. If it fits your flow automatically retrieve the next quote number and try saving it again.
These are uncomplicated basic ideas based on your statement that the chances of it happening are low.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Since you are working with datasets and -especially- since you are using Access as the back end data store you will be much more likely to get the functionality that you need if you modify the database such that the NextQuoteNo is NOT the primary key of the table.
More robust database systems allow you to specify a field as an Identity, which is an auto-incrementing number that allows you to perform scalar operations on them to insure the uniqueness of said number. Access does not have this functionality, however, and locking someone out of the -only- record that could allow them to generate a new number is not a good design.
Instead, allowing clients to request and increment the SQL MAX() of the NextQuoteNo field will prevent lock-wait conditions, and if your code checks, increments, and immediately updates this value you wont run into duplicates (especailly if the target table IS using the QuoteNo as the primary key, since you can then be guaranteed that it will generate a duplicate error on insert and allow you to modify that number before insert)
That is more work, I know, but its the reality of coding against Access databases.
(Just FYI: MySQL is free, full featured and has none of the DB size limitations, and you can code against it just as you would Access with the MySQL connectors for .NET)
NolancAuthor Commented:

I would like to thank both you guys for your comments and suggestions. It sounds very logical to me. I will certainly implement them as soon as I get back into the Work place on Monday. But I am accepting it and thus awarding the points to you. If I encounter any problems I will initiate a new question and get back to you.

In the mean time, my grateful thanks to you.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.