Solved

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

Posted on 2010-08-13
4
806 Views
Last Modified: 2013-11-26
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.

0
Comment
Question by:Nolanc
  • 2
4 Comments
 

Author Comment

by:Nolanc
ID: 33427606
Hi

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.

0
 
LVL 16

Accepted Solution

by:
13598 earned 125 total points
ID: 33433356
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.
 
0
 
LVL 11

Assisted Solution

by:ladarling
ladarling earned 125 total points
ID: 33433448
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)
0
 

Author Closing Comment

by:Nolanc
ID: 33435614
Hi

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.
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Suggested Solutions

Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now