Database Question

**Xmas79** · May 13th, 2003, 08:27 AM

In my project I build up the DB section and I'm building the GUI. As my project has to work in a LAN, I'm facing now with the problem of the same request from two different clients, in the sense that both clients request the same record for modifying it. I use data in disconnected mode. How can I manage this problem? I don't have idea on that, any help is appreciate.

Thx a lot,
Xmas

**Lunatic3** · May 13th, 2003, 08:44 AM

You have answered the question yourself. Disconnected data model. If by a little chance two users try to update one record at exactly the same time, i think SQL Server (or MSDE which is proably your backend database server) will handle the transaction.

**Xmas79** · May 13th, 2003, 09:34 AM

My program is well structured, so it's impossible that two users will update the database in the same time... The problem is that two users access the same record the same time for modifying. The first finishes and update the database. The second, that is still working on it, has now "a bad version" of the record (the old, because the new one is which the first updated) , and updating will overwrite the work of the first. I was asking about that.

Thx

**DevGrp** · May 13th, 2003, 10:17 AM

If this happens the program will throw a DBConcurrency exception. You can address this in 3 ways, overwrite the data or you can have a form presented to the user with the new data and the current data and ask if they want to keep the new data or overwrite it, or you can just go ahead and lock that row when its being updated.

**Xmas79** · May 13th, 2003, 10:56 AM

Ok, I just thrown this exception... So the Update method must be in a try catch block. If I want to discard the changes I do nothing. What if I want to overwrite?

**Lunatic3** · May 13th, 2003, 11:02 AM

The second, that is still working on it, has now "a bad version" of the record (the old, because the new one is which the first updated) ...

I guess you are running a competetion in your program

Think about this situation:
User A and B access the same time a record and want to modify a data of the Column called 'Name'
1- User A and B read then 'Name' and its ="Xmas79"
2-User A modifes it and updates it, 'Name' is now changed to "Xmas74"
3-User B still thinks that 'Name' has not changed and modifies it to 'Xmas80' and updates it.
4-Name is now 'Xmas80'
Okay, but what should the filed 'Name' should be? The one that User A has enterd or the one that User B? Are they supposed to enter different data? If yes who has the priority, if not why two user are assgined to modify at a same time? and what will happen that user B tries to update the row with the same data is what DEVGREP said and that is DBConcurrency exception.You can 'use optimistic cuncurrency' when building your dataadapters.

**Xmas79** · May 13th, 2003, 11:24 AM

Yes, it's like that. But as DEVGRP said, your step 4 will never be executed because an exception is thrown. I cannot know what the data must be, users will know, but as programmer, I have to be aware from all the pessimistic things that can happen, like this one, isn't it? I have to look more in detail your advice... in the meanwhile, other ideas/suggestions/jokes are welcome

Xmas.

**Lunatic3** · May 13th, 2003, 11:31 AM

As i told you try to implement "use optimistic cuncurrency" . To test it add a datadapter to your form at desing time and run a query builder and remove the tick for 'use optimistic cuncurrency' . Then look at the wizard generated update command. Then go back and use it now and again look at the update command. You will see the difference.

**Xmas79** · May 13th, 2003, 11:33 AM

Ok, thanks. I'll do it later, maybe tomorrow, now I've to do other things... Sure I'll let you know.

Best regards,
Xmas79

**Xmas79** · May 14th, 2003, 11:11 AM

Lunatic3, I tried to do what you say. I can see the differences, depending on the complexity of the table they can be a LOT!!! Do I have to do it by code? Is there another way, like a method, a property in the dataadapter??

Thx

**Xmas79** · May 14th, 2003, 11:20 AM

Or DEVGRP, If I update it will thrown always that exception. How to overwrite?

Thx

**Xmas79** · May 15th, 2003, 02:59 AM

*** BUMP ***

**Lunatic3** · May 15th, 2003, 04:37 AM

Do I have to do it by code?

If you are a coder then YES

Anyway you can add adapters at desing time, not run time. However DEVgrp method would be fine too. Try catching the exception and then deal with it. In that case try to catch the most specific exception.

**Xmas79** · May 15th, 2003, 08:47 AM

http://msdn.microsoft.com/msdnmag/is...y/default.aspx

DevGrp's way... Good.

Thx

Thread: Database Question

Thread Tools

Display

Database Question

* BUMP *

Posting Permissions