Concurrency error VB.NET - SQL

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
mark_norgeAsked:
Who is Participating?
 
Thunder724Connect With a Mentor Commented:
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
 
yatin_81Commented:
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
 
yatin_81Commented:
I'm sorry the data type is "timestamp"
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
mark_norgeAuthor Commented:
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
 
Thunder724Commented:
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
 
mark_norgeAuthor Commented:
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
 
mark_norgeAuthor Commented:
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
 
Thunder724Commented:
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
 
mark_norgeAuthor Commented:
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
 
Thunder724Commented:

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
 
mark_norgeAuthor Commented:
Thanks for your help. I just converted an application from using autoincrement to guids and it works perfect.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.