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 & "' )"
Microsoft AccessASPSQL

Avatar of undefined
Last Comment
bschwarting

8/22/2022 - Mon
Paul MacDonald

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.
russellC

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
bschwarting

ASKER
paulmacd,

Can you provide an example?
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
bschwarting

ASKER
russellC,

What happens if it does exist?  
russellC

As far as what is returned I am not 100% surebut Ithink it is a null value and nothing is added to the DB.
Paul MacDonald

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.

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
bschwarting

ASKER
getting a syntax error on that example.
russellC

Did u use = RadioNumber or = radionumber
rabarlow

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 & ");"
Your help has saved me hundreds of hours of internet surfing.
fblack61
godeschalk

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
godeschalk

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
bschwarting

ASKER
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?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
bschwarting

ASKER
FYI, none of the above are working.
Paul MacDonald

"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.
godeschalk

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
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
godeschalk

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
bschwarting

ASKER
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

godeschalk

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

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
bschwarting

ASKER
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
godeschalk

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
bschwarting

ASKER
worked awesome!  thanks!