insert duplicate data into database.

[NinjaTurtle]

dear,

example i have 10 records, between the 10 records there are few duplicate data, b4 insert to database i want it smart enough insert only the distinct records without promt users there are duplicate records....

example:

Userlist= request.Form("Userlist")
arrSelectedUsers = split(Userlist,",")

dim SplitID,w
for w = 0 to ubound(arrSelectedUsers)
SplitID = split(arrSelectedUsers(w),",")
if ubound(SplitID)>=1 then
set rsSave = server.createObject("ADODB.Recordset")
rsSave.Open "Select * From User", objSaveconn, adOpenKeyset, adLockPessimistic, adCmdText
rsSave.Addnew
rsSave(UserID") =trim(SplitID(0))
rsSave(UserAge") =trim(SplitID(1)) rsSave.Update
rsSave.Close
end if
Next

Exapmle records i get:
Userlist:
=======
Name | Age
----------------
1. John,19
2. Alex,26
3. Tom,50
4. John,19
5. John,19
6. Mary,21
7. Max,34
8. John,19
9. Alex,26
10. Mary,21

from this databse, u can c there are few user that appear few times in the list: John, Alex & Mary. User name is primary key.

but what i want is i just want to insert the single user for duplicate user in the list so the final insert to data base should be
:
1. John,19
2. Alex,26
3. Tom,50
4. Mary,21
5. Max,34

Possible without Javascript or vbscript to do checking on that??
if using loop to check, how about if my users are >100 users???

[raf]

Never came across a situation like that.

My advice would be to prevent duplications as soon as possible. They should not get into the array and shouldn't be in the userlist. I'd check for duplicates, before adding to the userlist.

[Leeus]

Why not add a Primary Key?

[whammy]

Just make sure that you check for anything that you don't want to duplicate before you insert any records (i.e. a primary key or whatever you're unique field may be, for instance email address).

If NOT EOF then you have a duplicate, and don't insert a new record.

Or like raf said, as soon as is possible, prevent duplication... if you're already comparing your variable to a database.

[whammy]

Also, as Leeus said... what is this for?

If this is for some specific purpose (as most programs are), you should have some primary key. If, as you said, the username IS the primary key, then it can't be duplicated, and I have no idea what you're trying to do...

If you see that the "user" is already present you might want to use an "UPDATE" statement on their record, instead of an insert... is that what you're looking for?

[NinjaTurtle]

tq, but the USERNAME can be duplicate, but they are not having same USERNAME and AGE. what i meant is User can alocated to different Age group. like John,19 he can appear in John,22. what i do is not UPDATE, im delete all the record then only i add the record back to database.

[Leeus]

I remember studying about composite pri keys where both fields together make up the key.

When you do you update statemnet you just have to make sure that both the fields are updated in the WHERE bit of it.

[aCcodeMonkey]

NinjaTurtle
you can add a check tot he SQL Insert statement

SQLStr = "INSERT INTO myTABLE
(UserName,UserID,Password) VALUES
('& sUName & "," & sUID & "','" & sPWD & "')
Where

[aCcodeMonkey]

NinjaTurtle

You can add a check to the SQL Insert statement as shown below:

SQLStr = "INSERT INTO myTABLE " & _
"(UserName,UserID,Password) " & _
"VALUES ('& sUName & "," & sUID & "','" & sPWD & "') " & _
"Where '" & sUID & "' NOT IN (Select UserID FROM mytable)"

The key is the NOT IN ( ) function. The Query will compare the UserID value and if no match is found, will update the Database.

Hope this helps :cool: