How to check if data exists, it yes, do not save the form

Hello,
I am trying to create a form to insert data in sql server table. I want to check if email address exists or not. If exists, shows errors like "Email exists, try another", if not, insert the form to the db.

Here is my code, but it does nothing.
Is there a better way to check it or what did I do wrong?
thanks.

email_address = trim(fixStr(Request("emailAddress")))
first_name = trim(fixStr(Request("firstName")))

<%
strSQL = "Insert into students(First_name, email_address) values ('" & First_Name & "', '" & Email_Address & "')"

strSQL = "Select * From students Where Email_Address = '" & email_Address & "'"
set pRS = server.CreateObject("ADODB.Recordset")
pRS.open strSQL, Conn,3,1,1
if not(pRS.eof) then
pRS.close
set pRS = nothing
conn.close
set conn=nothing
session("strMsg") = "The Email address existed, try another"
response.redirect "student.asp?FirstName=" & First_Name &" &EmailAddress=" & Email_Address
end if
conn.execute strSQL

%>

what's not working? are the new records not inserting. if that's the case it may be because you are never actually running the insert in the code you showed.

if your search is not finding anything, then i would suggest you check to make sure that you have all of the field names correct.

otherwise it would say it should be working.

This code will do the checking for email if exists:

set pRS = server.CreateObject("ADODB.Recordset")
pRS.open strSQL, Conn,3,1,1
if not(pRS.eof) then
pRS.close
set pRS = nothing
conn.close
set conn=nothing
session("strMsg") = "The Email address existed, try another"
response.redirect "student.asp?FirstName=" & First_Name &" &EmailAddress=" & Email_Address
end if

If I remove the block above, it inserted the data. But when I place it in to check email exists or not, then it does not insert anything, but don' cause errors.

can you help please?

thanks.

You are over writing your strSQL variable.

Just declare the insert sql string after checking for the record. So you want:


strSQL = "Select * From students Where Email_Address = '" & email_Address & "'"
set pRS = server.CreateObject("ADODB.Recordset")
pRS.open strSQL, Conn,3,1,1
if pRS.eof then
pRS.close
set pRS = nothing
conn.close
set conn=nothing
session("strMsg") = "The Email address existed, try another"
response.redirect "student.asp?FirstName=" & First_Name &" &EmailAddress=" & Email_Address
end if
strSQL = "Insert into students(First_name, email_address) values ('" & First_Name & "', '" & Email_Address & "')"
conn.execute strSQL


Basscyst

it worked.
thanks.