?
Solved

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

Posted on 2010-08-13
4
Medium Priority
?
848 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 500 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 500 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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

770 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