[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Concurrency error VB.NET - SQL

Posted on 2009-02-23
11
Medium Priority
?
616 Views
Last Modified: 2012-08-13
Hi Experts,
I have built a VB.Net application with an SQL DataSource. The application works fine with a single user, but does not work correctly with multiple users. When two users add a new row I get an error that the unique key value is already in use. Below I summarize all the steps I do to prevent this error and I highlighted which line of code gives the error with error code.

Help is really appreciated on this one.

I have the following steps:
1. Declare public variables used by all Forms in the application
Public daBookings As SqlDataAdapter
Public cbBookings As SqlCommandBuilder

2. Set up the DataSet in the load event of the MainForm
daBookings = New SqlDataAdapter("SELECT * FROM Bookings", Connection)
cbBookings = New SqlCommandBuilder(daBookings)
DS = New DataSet
daBookings.FillSchema(DS, SchemaType.Source, "Bookings")
daBookings.Fill(DS, "Bookings")

3. When loading a Form on which rows are added I first update the datatable
MainForm.DS.Tables("Bookings").Rows.Clear()
MainForm.daBookings.Fill(MainForm.DS, "Bookings")

4a. After entering the data for the new row and pressing the save button I first do an update of the datatable to make sure I have the newest data.
MainForm.daBookings.Update(MainForm.DS.Tables("Bookings"))
MainForm.DS.Tables("Bookings").Rows.Clear()
MainForm.daBookings.Fill(MainForm.DS, "Bookings")

4b. Then I fill the DataRow with data and when I try to store the DataRow I get the following error "Column 'BookingID' is constrained to be unique.  Value '111' is already present." on this line "MainForm.DS.Tables("Bookings").Rows.Add(DR)"

4b. After save I also so an update to make sure the database is up to date.
MainForm.daBookings.Update(MainForm.DS.Tables("Bookings"))
MainForm.DS.Tables("Bookings").Rows.Clear()
MainForm.daBookings.Fill(MainForm.DS, "Bookings")

I have no clue at which step the database can not be up to date.

Regards, MB
0
Comment
Question by:mark_norge
  • 5
  • 4
  • 2
11 Comments
 
LVL 2

Expert Comment

by:yatin_81
ID: 23709248
I suggest you add a column of data type "rowtimestamp" in your table(s). This would greatly reduce chances for concurrency errors.
Try it.
0
 
LVL 2

Expert Comment

by:yatin_81
ID: 23709256
I'm sorry the data type is "timestamp"
0
 

Author Comment

by:mark_norge
ID: 23709274
Thanks for your reply,
I am not sure I follow you correctly. It seems I have a communication problem between the application and the database, since even if I update datatable and database before and after each and every step I get an error.

Can you give me an example on how to implement your suggestion. I already have a column "DateTimeStamp", but you are suggesting a special columntype and how would this help solving the problem?

Regards, MB
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 4

Expert Comment

by:Thunder724
ID: 23714761
It sounds like when you are adding a new row you are using a value in the 'BookingID' column that has already been used.  If the 'BookingID' column is constrained to be unique and you are trying to insert 111 in a new record when there is already a 111 that resides in that table you will get this error.  

I think what yatin 81 is trying to say is to create a unique column in your table that uses a timestamp as a unique value. I would rather suggest a GUID however; but that is up to you.  The bottom line is that you need to control the unique record id going in.
0
 

Author Comment

by:mark_norge
ID: 23715618
Thanks for your reply,

My understanding has been that SQL took care of unique numbering (which seems logical being the central database). What I have been trying to achieve is that when I add a row I synchronize the database and datatable before and after. This however does not seem to work (I would even think that this manual synchronization should not even be needed, since the synchronization mechanism between VB.NET and SQL must be smarter then this...)

If I understand the suggested solutions correctly I need to use a GUID (/datetimestamp) as unique number and not depend on SQL to take care of this.

Q:
1. There is no easy synchronization mechanism between SQL and multiple applications that takes care of the unique numbering (incremented integer)?
2. Can you please let me know how you build up your GUID normally?

Best regards, MB
0
 
LVL 4

Accepted Solution

by:
Thunder724 earned 2000 total points
ID: 23715831
On the SQL Side use the NewID() function. You can put this in the default value of the column too.
On the VB.NET side you would create a GUID by using
Dim NewID$ = Convert.ToString(System.Guid.NewGuid())
Now you can assign the records id as NewID$
Keep in mind though this is a VarChar(50) data type, not a number
 
You should not have to manually sync; unless you are meaning refresh your front end.
What may be happening though is that when you are doing your update the previous ID is being sent and an attempt to update your id column is being made.
Or when you insert a new record the id column you are using has been assigned the previous value viewed.
0
 

Author Comment

by:mark_norge
ID: 23719882
Thanks, this got me further.
I do not get an error anymore that the key is not unique.

However I now still need to solve the following problem:

If user 1 selects a row to be modified the DataRow bound to the GridViewRow is taken in memory. Assume user 1 forgets to save his changes and in the meantime user 2 selects the same row, makes changes and saves the changes. If user 1 now saves his row I get a concurrency error.

How can I prevent this concurrency error. I would not mind if the changes of the second user are overwritten. I there a setting for this?

Regards, MB
0
 
LVL 4

Expert Comment

by:Thunder724
ID: 23720434
This should not happen. I don't usually bind object; but make my own connections and disconnect after reading; then reconnect when writing.  Try exclduding the record id in the update if you can.  You should also put a Try...Catch statement around your updates too to try to see what exactly is getting errored out.
0
 

Author Comment

by:mark_norge
ID: 23720579
Do you know if there is a way to force the update or just cancel the update if I have a concurrency violation. The user then just has to do it again, which is not problem to me.
0
 
LVL 4

Expert Comment

by:Thunder724
ID: 23725977

You can try capturing the error first with a Try...Catch statement around it.
Try not issuing an Update before adding a new record. Also try closing the connection after filling the dataset.
0
 

Author Closing Comment

by:mark_norge
ID: 31549975
Thanks for your help. I just converted an application from using autoincrement to guids and it works perfect.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This tutorial demonstrates one way to create an application that runs without any Forms but still has a GUI presence via an Icon in the System Tray. The magic lies in Inheriting from the ApplicationContext Class and passing that to Application.Ru…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
Suggested Courses

830 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