Link to home
Start Free TrialLog in
Avatar of bschwarting
bschwarting

asked on

ASP - Check For Duplicates

What is the EASIEST way to check for duplicates before I do an insert?

The radionumber field is where I need to do the check.

Should I do this in ASP or in the SQL statement?

This is an access database.

mySQL = "INSERT INTO radioGPS ( lastname, streetaddress, radionumber, latitude, longitude, signalstrength, altitude, antennatype, gpsaccuracy, latitude1, latitude2, longitude1, longitude2 ) VALUES ( '" & lastname & "','" & streetaddress & "','" & radionumber & "','" & latitude & "','" & longitude & "','" & signalstrength & "','" & altitude & "','" & antennatype & "','" & gpsaccuracy & "','" & latitude1 & "','" & latitude2 & "','" & longitude1 & "','" & longitude2 & "' )"
Avatar of Paul MacDonald
Paul MacDonald
Flag of United States of America image

Do a SELECT for the unique criteria in your table, based on what was entered into the page.  If you get no results, then do the INSERT.
INSERT INTO clients
(client_id, client_name, client_type)
SELECT supplier_id, supplier_name, 'advertising'
FROM suppliers
WHERE not exists (select * from clients
where clients.client_id = suppliers.supplier_id);


from : http://www.techonthenet.com/sql/insert.php
Avatar of bschwarting
bschwarting

ASKER

paulmacd,

Can you provide an example?
russellC,

What happens if it does exist?  
As far as what is returned I am not 100% surebut Ithink it is a null value and nothing is added to the DB.
If RadioNumber is the only unique criteria, do something like what [russellC] suggests:

INSERT INTO radioGPS ( lastname, streetaddress, radionumber, latitude, longitude, signalstrength, altitude, antennatype, gpsaccuracy, latitude1, latitude2, longitude1, longitude2 ) VALUES ( '" & lastname & "','" & streetaddress & "','" & radionumber & "','" & latitude & "','" & longitude & "','" & signalstrength & "','" & altitude & "','" & antennatype & "','" & gpsaccuracy & "','" & latitude1 & "','" & latitude2 & "','" & longitude1 & "','" & longitude2 & "' )"
WHERE not exists (SELECT RadioNumber from RadioGPS
where RadioGPS.RadioNumber  = RadioNumber);

...or something to that effect.

getting a syntax error on that example.
Did u use = RadioNumber or = radionumber
Try
mySQL = "INSERT INTO radioGPS ( lastname, streetaddress, radionumber, latitude, longitude, signalstrength, altitude, antennatype, gpsaccuracy, latitude1, latitude2, longitude1, longitude2 ) VALUES ( '" & lastname & "','" & streetaddress & "','" & radionumber & "','" & latitude & "','" & longitude & "','" & signalstrength & "','" & altitude & "','" & antennatype & "','" & gpsaccuracy & "','" & latitude1 & "','" & latitude2 & "','" & longitude1 & "','" & longitude2 & "' ) WHERE not exists (SELECT RadioNumber from RadioGPS where RadioGPS.RadioNumber  = " & RadioNumber & ");"
if it is a duplicate do you want it to be updated or just skip it.
than you can use

INSERT INTO table (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE c=c+1;

and make the radionumber as key
and if nothing must be updated

mySQL = "INSERT INTO radioGPS ( lastname, streetaddress, radionumber, latitude, longitude, signalstrength, altitude, antennatype, gpsaccuracy, latitude1, latitude2, longitude1, longitude2 ) VALUES ( '" & lastname & "','" & streetaddress & "','" & radionumber & "','" & latitude & "','" & longitude & "','" & signalstrength & "','" & altitude & "','" & antennatype & "','" & gpsaccuracy & "','" & latitude1 & "','" & latitude2 & "','" & longitude1 & "','" & longitude2 & "' ) ON DUPLICATE KEY UPDATE radionumber=radionumber;"

just a dummy update
I need it to check if duplicates exists, if so, provide the user with an error and to try again.

Maybe I should just do a count, and if it's > than zero I know it already exists.  If it is <1 then I could do the insert.

Thoughts?  If this is a good idea, what is the best method to do that?
FYI, none of the above are working.
"Maybe I should just do a count..."

That's essentially what I initially suggested.  Do a SELECT for any records matching your criteria and, if none exist, then do the UPDATE.
set on error resume next is on and set radionumber as primary key so you get an error if you insert a duplicate key
like

on error resume next
insert into db
if err.number <> 0 then
   error message
Else
  inserted
End If
reset on error resume next

On Error GoTo 0

and err.Source and err.description provides you the error information

but be carefull it will on error resume next never forget to reset when not needed anymore
you don't have to da a count;

it can also be done like

if conn.execute("select radionumber from table weher radionumber='"& request("radionumber") &"'").eof then
  INSERT
else
 error
end if
godeschalk,

I did the below and it always inserts, no matter what.  I never get the error.
if Con.execute("select radionumber from radioGPS where radionumber = '"& request("radionumber") &"' ").eof then

mySQL = "INSERT INTO radioGPS ( lastname, streetaddress, radionumber, latitude, longitude, signalstrength, altitude, antennatype, gpsaccuracy, latitude1, latitude2, longitude1, longitude2 ) VALUES ( '" & lastname & "','" & streetaddress & "','" & radionumber & "','" & latitude & "','" & longitude & "','" & signalstrength & "','" & altitude & "','" & antennatype & "','" & gpsaccuracy & "','" & latitude1 & "','" & latitude2 & "','" & longitude1 & "','" & longitude2 & "' )"
Con.Execute mySQL

else

response.write "Duplicate Radio #'s"

end if

Open in new window

i see you have evrything in variables so change request("radionumber") to radionumber

but, if I change it to this, they will always match, and always look like there are duplicates in the DB

if Con.execute("select radionumber from radioGPS where radionumber = radionumber ").eof then
ASKER CERTIFIED SOLUTION
Avatar of godeschalk
godeschalk
Flag of Netherlands image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
worked awesome!  thanks!