How can I control the multiple users accessing the same database

Question

0.00/5 (No votes)

See more:

i am new in programming, making my first project using vb.net and access. i am trying to develop a project in which the data of patients of the is added from different counters. what i developed is working fine when only one counter adds data in database but when two counters access the same database and try to save the record it gives error primary key can not be duplicate.

i doing what, first i am generating a primary key number i.e. patient no that is unique to every patient. patient no. is one increment to the last saved record. then the user enter (counter data entry operator) adds the patient details and then hit the save button.

in multiuser environment both the operators generate the same patient no. when they hit the new record button as both see the same last saved record. while saving the record one operator save the record successfully but other operator get the duplicate primary key error.

Pessimistic and optimistic locks are not working for me or i am not understanding how to use them.

rsS As New ADODB.Recordset rsS.Open(str, conn, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockPessimistic)

plz someone guide me

What I have tried:

i tried to solve this problem by saving the patient no. in another variable oldPatientno and beore saving the record checked if there is any change in the database if so then regenerate the patient no. but this is not working

Posted 3-Oct-17 19:33pm

Member 13444338

Updated 3-Oct-17 19:45pm

Add a Solution

Comments

Richard Deeming 5-Oct-17 14:12pm

If you're using .NET, don't use the ancient and obsolete ADODB library. Switch to using ADO.NET[^] instead.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2017-10-03T19:45:00

First off, don't use Access. While it is possible to get Access working in a multi-user environment, it always causes nasty problems.

Instead, use a multiuser database system like Sql Server or MySql which is designed to work with multiple simultaneous users.

Provided you have written your code correctly, it should just be a case of using SqlConnection instead of OdbcConnection, SqlCommand instead of OdbcCommand, and so forth - the actual SQL SELECT, INSERT, and UPDATE commands should need little change.

But while you are doing that, change your DB design. NEVER use "last record plus one" to pregenerate an ID - use an IDENTITY column in your DB instead, and let the database engine sort out the unique value for you - you do not need the ID before the data has been inserted into the DB so ID pregeneration is always a recipe for trouble, as it always gives the problems you are seeing. Locking won't help here, indeed, it will cause more problems, not less.