asked on

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

yatin_81

I suggest you add a column of data type "rowtimestamp" in your table(s). This would greatly reduce chances for concurrency errors.
Try it.

yatin_81

I'm sorry the data type is "timestamp"

Mark Bakelaar

ASKER

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

CodeJunky

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.

Mark Bakelaar

ASKER

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

ASKER CERTIFIED SOLUTION

CodeJunky

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mark Bakelaar

ASKER

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

CodeJunky

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.

Mark Bakelaar

ASKER

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.

CodeJunky

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.

Mark Bakelaar

ASKER

Thanks for your help. I just converted an application from using autoincrement to guids and it works perfect.