如何使用c＃防止Access数据库中的重复条目？

i have a table of MS Access which contains one column and many rows. The value of a textbox saved to this table. I want to prevent duplicate entries from being submitted. For example, if I type "ankush" in the textbox and this entry already exists in table, then I want to display a msgbox saying that this already exists in the table. using c#

我有一个MS Access表,其中包含一列和多行。保存到此表的文本框的值。我想防止提交重复的条目。例如,如果我在文本框中键入“ankush”并且此条目已经存在于表中,那么我想显示一个msgbox,表示这已存在于表中。使用c#

5 个解决方案

#1

If the field is not your primary key filed you can set an index on the field that contains 'ankush' and make that index unique

如果该字段不是您的主键字段,则可以在包含“ankush”的字段上设置索引,并使该索引唯一

[Index=Yes(no duplicates)]

then the Jet DB Engine will not allow the insert and will show a default message.

然后Jet数据库引擎将不允许插入并将显示默认消息。

I am working with an older version of Access, so your mileage may vary, but to show a custom error message you would have to first do a query for the value in the table like this:

我正在使用较旧版本的Access,因此您的里程可能会有所不同,但要显示自定义错误消息,您必须首先查询表中的值,如下所示:

SELECT COUNT(MyField)as violated FROM MyTable WHERE MyField = 'input value here'

Then branch in your code if violated > 0 to show your message box.

如果违反> 0,则在代码中分支以显示您的消息框。

#2

You can create a unique key on the column.

您可以在列上创建唯一键。

#3

The focus of the question has completely changed, rendering the answer obsolete.

问题的焦点已完全改变,使答案过时。

#4

Just to add to what @Gary.Ray has suggested: if your table has multiple candidate keys ("If the field is not your primary key...") then the failure message the ACE/Jet engine returns unhelpfully does not tell you which key was violated:

只是添加@ Gary.Ray的建议:如果你的表有多个候选键(“如果该字段不是你的主键...”)那么ACE / Jet引擎无益返回的失败消息并不能告诉你哪一个密钥被违反:

The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again.

您向表请求的更改未成功,因为它们将在索引,主键或关系中创建重复值。更改包含重复数据的字段或字段中的数据,删除索引或重新定义索引以允许重复条目,然后重试。

Happily, there is more than one way to implement a key and using a CHECK constraint has the advantage of returning the name of the constraint in the failure message. For example, this CHECK constraint will duplicates for the column 'MyField':

令人高兴的是,实现密钥的方法不止一种,使用CHECK约束的优点是在失败消息中返回约束的名称。例如,此CHECK约束将与“MyField”列重复:

ALTER TABLE MyTable ADD
   CONSTRAINT MyTable__MyField__no_dups_allowed 
      CHECK (NOT EXISTS (
                         SELECT T1.MyField 
                           FROM MyTable AS T1
                          GROUP
                             BY T1.MyField 
                         HAVING COUNT(*) > 1
                        ));

For data that fail the 'MyField' but would satisfy the other keys in the table, the failure message would be:

对于“MyField”失败但满足表中其他键的数据,失败消息将是:

One or more values are prohibited by the validation rule 'MyTable__MyField__no_dups_allowed' set for 'MyTable'. Enter a value that the expression for this field can accept.

为“MyTable”设置的验证规则“MyTable__MyField__no_dups_allowed”禁止一个或多个值。输入此字段的表达式可以接受的值。

You can then catch the error in your C# front end and parse the error message for the known constraint names to provide a more meaningful message.

然后,您可以捕获C#前端中的错误,并解析已知约束名称的错误消息,以提供更有意义的消息。

#5

If [field name] = Me. field name in the form.Column(0) And Not Me.NewRecord Then

如果[字段名称] =我。表单中的字段名称.Column(0)And Not Me.NewRecord然后

Me.field name in the form.Undo Me.Undo Exit Sub End If If [field name] = Me. field name in the form.Column(0) And Me.NewRecord Then

表单中的Me.field名称.Undo Me.Undo Exit Sub End If If [field name] = Me。表单中的字段名称.Column(0)和Me.NewRecord然后

Me.field name in the form.Undo Me.Undo MsgBox " already there", vbInformation, "repeated" Exit Sub End If End Sub

表单中的Me.field名称。撤消Me.Undo MsgBox“已经存在”,vbInformation,“重复”退出子端如果结束子

#1