Using ASP to Preventing Duplicate Data from Form Going into Database

[sandrawg]

I am working with a form where the user enters personal information like name and email address that will go into a database. I am a real ASP newbie, and I need some help. I basically need the form to check the information the user has submitted and make sure it's not already in the database. The fields we want to check are:

email address: email_address
first name: first_name
last name: last_name

If the info is already in the database, there should be a response.write that says something like "sorry, you already registered."

The form action's asp file has the following code:

<%
Dim chkContact, random_number

' begin random function
randomize

' random numbers is the varible that will contain a numeriv value
' between one and nine
random_number=int(rnd*90000)+1
random_number = right(CStr(random_number + 1000000),5)

' write the random number out to the browser
'response.write random_number

If Request.Form("signup") = "Yes" Then
chkContact = True
Else
chkContact = False
End If

strSQL = "SELECT * from user_info"

Set r_user_info = Server.CreateObject("ADODB.Recordset")
r_user_info.Open strSQL, strConnect, adOpenStatic, adLockOptimistic, adCmdText
' r_user_info.MoveLast
' iOrderCode = r_user_info.Fields("OrderCode").Value + 1

' response.write "This is a test:"&Trim(Request.Form("pets_name"))

r_user_info.AddNew
r_user_info.Fields("dAdded").Value = Date()
' r_user_info.Fields("OrderCode").Value = iOrderCode
r_user_info.Fields("pet_name").Value = Trim(Request.Form("pets_name"))
r_user_info.Fields("petType").Value = Trim(Request.Form("petType"))
r_user_info.Fields("petTypeOther").Value = Trim(Request.Form("petTypeOther"))
r_user_info.Fields("signup").Value = chkContact
r_user_info.Fields("zip").Value = Trim(Request.Form("zip"))
r_user_info.Fields("state").Value = Trim(Request.Form("state"))
r_user_info.Fields("city").Value = Trim(Request.Form("city"))
r_user_info.Fields("email_address").Value = Trim(Request.Form("email_address"))
r_user_info.Fields("last_name").Value = Trim(Request.Form("last_name"))
r_user_info.Fields("first_name").Value = Trim(Request.Form("first_name"))
r_user_info.Fields("random_number").Value = random_number
r_user_info.Update

r_user_info.Close
set r_user_info = Nothing

Response.Redirect "result.asp?z="&Trim(Request.Form("zip"))&"&fn=" &Trim(Request.Form("first_name"))&"&ln="&Tri m(Request.Form("last_name"))&"&rc="&random_numbe r
%>

Since I am so new at this, I need someone to give me the code and show me where to put it in the above script.

thanks!!

[Freon22]

You can try something like this, some of the other guys here are much better then I on this. I also used OR instead of AND on the email address, because if a user didn't spell their last or first name right you would end up with the same person added to the database again. I found that most people take their time to make sure their email address is right.

PS, I didn't test this so could some of you guys look this over to make sure I didn't mess this up.

<%
Dim chkContact, random_number

' begin random function
randomize

' random numbers is the varible that will contain a numeriv value
' between one and nine
random_number=int(rnd*90000)+1
random_number = right(CStr(random_number + 1000000),5)

' write the random number out to the browser
'response.write random_number
‘***********Check database for user******************
strSQL = "SELECT * from user_info Where last_name = '" & _ 
Trim(Request.Form("last_name")) & "' And first_name = '" & _  
Trim(Request.Form("first_name")) & "' Or email_address = '" & _ 
Trim(Request.Form("email_address")) & "';"
‘Note: you will need to check my sql statement to make sure its right
Set objRS = strConnect.Execute(strSQL) 
'If the recordset is empty then this is a new user 
If objRS.EOF Then
objRS.Close
Set objRS = Nothing
'**Since this is a new user continue with adding the new user to the database**

If Request.Form("signup") = "Yes" Then
chkContact = True
Else
chkContact = False
End If

strSQL = "SELECT * from user_info"

Set r_user_info = Server.CreateObject("ADODB.Recordset")
r_user_info.Open strSQL, strConnect, adOpenStatic, adLockOptimistic, adCmdText
' r_user_info.MoveLast
' iOrderCode = r_user_info.Fields("OrderCode").Value + 1

' response.write "This is a test:"&Trim(Request.Form("pets_name"))

r_user_info.AddNew
r_user_info.Fields("dAdded").Value = Date()
' r_user_info.Fields("OrderCode").Value = iOrderCode
r_user_info.Fields("pet_name").Value = Trim(Request.Form("pets_name"))
r_user_info.Fields("petType").Value = Trim(Request.Form("petType"))
r_user_info.Fields("petTypeOther").Value = Trim(Request.Form("petTypeOther"))
r_user_info.Fields("signup").Value = chkContact
r_user_info.Fields("zip").Value = Trim(Request.Form("zip"))
r_user_info.Fields("state").Value = Trim(Request.Form("state"))
r_user_info.Fields("city").Value = Trim(Request.Form("city"))
r_user_info.Fields("email_address").Value = Trim(Request.Form("email_address"))
r_user_info.Fields("last_name").Value = Trim(Request.Form("last_name"))
r_user_info.Fields("first_name").Value = Trim(Request.Form("first_name"))
r_user_info.Fields("random_number").Value = random_number
r_user_info.Update

r_user_info.Close
set r_user_info = Nothing

Response.Redirect "result.asp?z="&Trim(Request.Form("zip"))&"&fn=" &Trim(Request.Form("first_name"))&"&ln="&Trim(Request.Form("last_name"))&"&rc="&random_numbe r

'******If the recordset has something in it then do something else*******
Else
Response.Write("You are already a member!")
End If
%>