在将数据添加到访问数据库之前检查重复的条目 [英] Check for duplicate entries before adding data to access database

问题描述

我正在尝试使用vbscript添加数据以访问数据库.我能够成功将数据添加到数据库，但是我想在将数据添加到数据库之前检查重复性.

Using vbscript i am trying to add data to access database. I am successfully able to add data to database but i want to check duplicity before adding data to database.

如果在这三列中发现任何重复，则应在名称，电话和ID"列中输入重复"，然后显示发现重复的消息.

Duplicity should check in column Name, Phone and Id if found duplicity in any of these three column then show message found duplicity.

下面是帮助我向数据库添加数据的代码.

Below is the code which help me to add data to database.

sub addUser
    SQL_query = "INSERT INTO dvd (timestammp,Name,Phone,Id,Cat,StartDate,agent,amount,source,conver) VALUES ('"& txtteNow.value &"','"& txtName.value &"','"& txtPhone.value &"','"& txtId.value &"','"& txtCat.value &"','"& txtStartDate.value &"','"& txtsrch.value &"','"& txtamount.value &"','"& txtsource.value &"','"& txtconver.value &"')"
    conn.Execute(SQL_query
end sub 

我也尝试下面的代码，但是不起作用.

I try below code also but not working.

SQL_query = "INSERT INTO dvd (timestammp,Name,Phone,Id,Cat,StartDate,agent,amount,source,conver) VALUES('"& txtteNow.value &"','"& txtName.value &"','"& txtPhone.value &"','"& txtId.value &"','"& txtCat.value &"','"& txtStartDate.value &"','"& txtsrch.value &"','"& txtamount.value &"','"& txtsource.value &"','"& txtconver.value &"') WHERE NOT EXISTS (Select '2' from dvd  where Name = + " & txtName.value & + "
conn.Execute(SQL_query)

推荐答案

我的解决方案非常复杂:使用包含一个记录的虚拟表中的INSERT INTO ... SELECT查询，然后在其中使用NOT EXISTS.

My solution is quite elaborate: use an INSERT INTO ... SELECT query from a dummy table containing one record, then use a NOT EXISTS in there.

我也将使用参数，您确实应该这样做，以避免SQL注入问题.

I'm going to use parameters too, you really should've done that yourself to avoid problems with SQL injection.

CurrentDb是DAO数据库对象.

CurrentDb in this scenario is a DAO database object.

我将仅使用3个字段来简化解决方案.

I'm going to only use 3 fields to keep the solution concise.

With CurrentDb.CreateQueryDef("", "INSERT INTO dvd (timestammp,Name,Phone) " & _ 
    " SELECT p1, p2, p3" & _
    " FROM (SELECT First(ID) FROM MSysObjects) As Dummy" & _
    " WHERE NOT EXISTS(SELECT 1 FROM dvd WHERE Name = p1)"
    .Parameters("p1") = txtteNow.value
    .Parameters("p2") = txtName.value
    .Parameters("p3") = txtPhone.value
    .Execute
End With

这篇关于在将数据添加到访问数据库之前检查重复的条目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！