如何控制访问同一数据库的多个用户 [英] How can I control the multiple users accessing the same database

问题描述

我是编程新手，使用vb.net和访问创建我的第一个项目。我正在尝试开发一个项目，其中从不同的柜台添加患者的数据。当只有一个计数器在数据库中添加数据时，我开发的工作正常，但当两个计数器访问同一个数据库并尝试保存记录时，它会产生错误，主键不能重复。



i做什么，首先我生成一个主要密钥号码，即患者不是每个患者都有的。病人没有。是最后保存记录的一个增量。然后用户输入（计数器数据输入操作员）添加患者详细信息，然后点击保存按钮。



在多用户环境中，两个操作员都生成相同的患者编号。当他们点击新的记录按钮时，两者都看到同一个上次保存的记录。保存记录时，一个操作员成功保存记录，但其他操作员得到重复的主键错误。



悲观和乐观的锁对我不起作用或我不理解如何使用它们。



rsS As New ADODB.Recordset rsS.Open（str，conn，ADODB.CursorTypeEnum.adOpenDynamic，ADODB.LockTypeEnum.adLockPessimistic）



plz有人引导我



我尝试了什么：



i试图通过挽救病人来解决这个问题。在另一个变量oldPatientno和beore保存记录检查如果数据库中有任何变化，如果是，那么重新生成患者号。但这不起作用

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

推荐答案

首先，不要使用Access。虽然可以让Access在多用户环境中工作，但它总是会导致令人讨厌的问题。



相反，使用像Sql Server或MySql这样的多用户数据库系统设计用于多个同时用户。



如果您已正确编写代码，则应该只使用SqlConnection而不是OdbcConnection，SqlCommand而不是OdbcCommand ，等等 - 实际的SQL SELECT，INSERT和UPDATE命令应该几乎不需要改变。



但是当你这样做时，改变你的数据库设计。切勿使用last record plus one来预生成ID - 改为使用数据库中的IDENTITY列，让数据库引擎为您排序唯一值 - 在将数据插入数据库之前不需要ID所以ID预生成总是一个麻烦的方法，因为它总是给你看到的问题。锁定在这里没有用，实际上，它会导致更多问题，而不是更少。

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.

这篇关于如何控制访问同一数据库的多个用户的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！