Checking for duplicate records before inserting

[stlWebWiz]

Hello,

I'm trying to determine if a record exist before I insert a record. Can anyone point me in the right direction here??

Heres how my code looks right now with nothing checking for duplicate records. An example or code snippet would really help and be appreciated.

<%
Dim name, email, country, sql_insert

name= request.form("name")
email = request.form("email")
country = request.form("country")

'Connection String is here

sql_insert="insert into users (name, email, country) values ('"& name &"','"& email &"', '"& country &"')"

con.execute sql_insert

con.close
Set con = Nothing
%>

 

[a6m1n0]

Depending on the unique identifier that you are trying to run the check on depends on the solution, but here's an example using your existing code:

<%
Dim name, email, country, sql_insert

name = request.form("name")
email = request.form("email")
country = request.form("country")

'Connection String is here

'need to create a recordset to check against
dim check_sql, exists

if len(name)>0 then
  exists=true
  check_sql="select * from users where name='" & name & "'"
  set rs = con.execute(check_sql)
  if rs.eof then
    exists=false
  else
    exists=true
  end if
  set rs = nothing
end if

if exist=true then
  sql_insert="insert into users (name, email, country) values ('"& name &"','"& email &"', '"& country &"')"
  con.execute sql_insert
end if
con.close
Set con = Nothing
%>

 

[SecondToNone]

Try something like this:

<%
Dim name, email, country, sql_insert

Dim rs = Server.CreateObject(ADODB.RecordSet)

name= request.form("name")
email = request.form("email")
country = request.form("country")

'Connection String is here

strSQL = "Select * from yourtable where name='"&name&"' AND email='"&email&"' AND country='"&country&"' "

rs=con.execute(strSQL)

if rs.EOF AND rs.BOF then

sql_insert="insert into users (name, email, country) values ('"& name &"','"& email &"', '"& country &"')"

con.execute sql_insert
Else

Response.Write "User already exists"

End if

con.close
Set con = Nothing
%>


I did not test the code...just giving you an idea...

 

[a6m1n0]

Can you insert a record with an existing recordset open? I didn't know you could do that.

 

[bslintx]

stlWebWiz,

What exactly are you trying to accomplish here? Besides the Unique ID per record the only thing I see you can possibly use that would be unique is the email. Because name and country can't be unique So, why not do a simple query on email and if it exists then they can't submit the data.

name= request.form("name")
email = request.form("email")
country = request.form("country")

'Connection String is here
sql = "SELECT email From users WHERE email='" & email & "';"

objRS.Open sql, con

If objRS.EOF Then ' No match, insert into database

sql="insert into users (name, email, country) values ('"& name &"','"& email &"', '"& country &"')"

objRS.Close

Else ' Match

Response.Write "Sorry, user exists!"

objRS.Close
con.close
Set objRS= Nothing
Set con = Nothing
End If
%>

 

[stlWebWiz]

Thanks to all of you for your input, They give me three ways of accomplishing what I set out to do. I'll play around with these and see what I which one I like the best, they all pretty good

STLWW