Check duplicate records for more than one data field before inserting 1

[Beng79]

Hi all,

I was looking through the past thread and found thread333-1019874 regarding checking for duplicate records.

I tried checking for duplicate record for one data field using the solutions given and it works.

My question is if I have 2 or more data fields to check for duplicate records, how can I go about doing it?

Here is my code that I have written and it doesn't work, the records are still being inserted.

Any help will be appreciated

<%
Dim connectAdd
Dim recstAdd
Dim SQL
Dim strProjID1, strFeature1
%>

<%
Set connectAdd = Server.CreateObject("ADODB.Connection")
connectAdd.Open("DRIVER={Microsoft Access Driver (*.mdb)};" _
& "DBQ=" & Server.MapPath("Estimation.mdb"))

strProjID1 = request.form("ProjID1")
strFeature1 = request.form("Feature1")

SQL ="SELECT FeatName from FuncFeat where FeatName = '"& strFeature1 &"'"
Set recstAdd = connectAdd.Execute(SQL)

If recstAdd.EOF Then
SQL="INSERT INTO FuncFeat(FeatName) VALUES ('"& strFeature1 & "')"
Set recstAdd = connectAdd.Execute(SQL)
Response.Redirect("New Features Added.htm")

SQL ="SELECT ProjID from FuncFeat where ProjID = '"& ProjID1 &"'"
Set recstAdd = connectAdd.Execute(SQL)

If recstAdd.EOF Then
SQL="INSERT INTO FuncFeat(ProjID) VALUES ('"& strProjID1 & "')"
Set recstAdd = connectAdd.Execute(SQL)
Response.Redirect("New Features Added.htm")

Else
Response.Write "Sorry, Feature1 Exist!"

End If

Else
Response.Write "Sorry, Feature1 Exist!"

 

[Tarwn]

You can place multiple filters/requirements in your WHERE statement using And and Or. So in your case to check if a duplicate record existed with both values you would want a SQL statement like:

SQL ="SELECT FeatName from FuncFeat where FeatName = '"& strFeature1 &"'[highlight] AND ProjID = '"& ProjID1 &"'[/highlight]"
Set recstAdd = connectAdd.Execute(SQL)

With the way you currently have your code you will be adding one new record for the feature and a seperate new record for the project (if they aren't duplicates). With the SQL statement above you can check for a duplicate record based on both fields, then go back to using a single insert statement to insert both values into a single new record.

-T

 

[Beng79]

Thank you, it works.

By the way, if I have more than a pair of FeatName and ProjID textboxes to check for duplications. What can I do to identify the pair that is duplicated?

 

[DotNetGnat]

then you need to do something like this:

SQL ="SELECT FeatName from FuncFeat where 
(FeatName = '"& strFeature1 &"' AND ProjID = '"& ProjID1 &"'") OR
(FeatName = '"& strFeature2 &"' AND ProjID = '"& ProjID2 &"'") OR
(FeatName = '"& strFeature3 &"' AND ProjID = '"& ProjID3 &"'")

and so on...

-DNG

 

[Beng79]

Currently I have a If else statement to check for the one pair.

If recstAdd.EOF Then
SQL="INSERT INTO FuncFeat(FeatName, ProjID) VALUES ('"& strFeature1 & "', '" & strProjID1 & "')"
Set recstAdd = connectAdd.Execute(SQL)
Response.Wrie

Else
Response.Write "Sorry, Feature1 Exist!"

End If


How can check from different pairs? And the script is able to identify which pair has duplication and return the correct error message eg. if detected the 2nd pair is duplicated -> Response.Write "Sorry, Feature2 Exist!"

 

[DotNetGnat]

i would suggest a complete redesign to your method...

-DNG