juanfigs
asked on
ASP ADO
Hi I'm just learning database with asp and I have a Question. I'm using SQL Server for database and I'm doing a form where the user register before he can use some resources and form with NickName, FName, LName, Email and I set the NickName as a Primary Key but I want to do something just in case that 2 users use the same email.
<%
Dim strConn
Dim objConnection
Dim DontAdd
set objConnection = Server.CreateObject("ADODB
strConn = "Provider=SQLOLEDB; Data Source=Monica; " & "Initial Catalog=SecureSign;" objConnection.open strConn
set rs=Server.CreateObject("AD
sql = "SELECT NickName, Email FROM SignIn"
rs.Open sql, strConn
DontAdd = False
DO until rs.EOF
for each x in rs.fields
If (x.value = (request.form("Email"))) then
DontAdd = true
response.write "Emails are equal" & "<br>"
else
Response.write "else" & "<br>"
end if
next
rs.MoveNext
LOOP
rs.Close
If DontAdd = true then
Response.write "User Already Exists"
else
sql="INSERT INTO SignIn (NickName,FName,LName,Emai
sql=sql & " VALUES "
sql=sql & "('" & Request.Form("NickName") & "',"
sql=sql & "'" & Request.Form("FName") & "',"
sql=sql & "'" & Request.Form("LName") & "',"
sql=sql & "'" & Request.Form("Email") & "')"
on error resume next
objConnection.Execute sql,recaffected
if err<>0 then
Response.Write("No update permissions!")
else
Response.Write("<h3>Welcom
end if
end if
objConnection.close
%>
<%
Dim strConn
Dim objConnection
Dim DontAdd
set objConnection = Server.CreateObject("ADODB
strConn = "Provider=SQLOLEDB; Data Source=Monica; " & "Initial Catalog=SecureSign;" objConnection.open strConn
set rs=Server.CreateObject("AD
sql = "SELECT NickName, Email FROM SignIn"
rs.Open sql, strConn
DontAdd = False
DO until rs.EOF
for each x in rs.fields
If (x.value = (request.form("Email"))) then
DontAdd = true
response.write "Emails are equal" & "<br>"
else
Response.write "else" & "<br>"
end if
next
rs.MoveNext
LOOP
rs.Close
If DontAdd = true then
Response.write "User Already Exists"
else
sql="INSERT INTO SignIn (NickName,FName,LName,Emai
sql=sql & " VALUES "
sql=sql & "('" & Request.Form("NickName") & "',"
sql=sql & "'" & Request.Form("FName") & "',"
sql=sql & "'" & Request.Form("LName") & "',"
sql=sql & "'" & Request.Form("Email") & "')"
on error resume next
objConnection.Execute sql,recaffected
if err<>0 then
Response.Write("No update permissions!")
else
Response.Write("<h3>Welcom
end if
end if
objConnection.close
%>
juanfigs,
Just check the DB directly for the EMail. It's much faster than looping through all the records.
==========================
strEMail = Request.form("EMail")
strSQL = "SELECT NickName, EMail, etc " _
& " FROM SignIn " _
& " WHERE Email = '" & strEMail & "'"
rs.Open strSQL, objConnection
IF rs.EOF THEN
'--- EMail does NOT exist ---
ELSE
'---User already exists ---
END IF
==========================
Best Regards,
>apollois<
Just check the DB directly for the EMail. It's much faster than looping through all the records.
==========================
strEMail = Request.form("EMail")
strSQL = "SELECT NickName, EMail, etc " _
& " FROM SignIn " _
& " WHERE Email = '" & strEMail & "'"
rs.Open strSQL, objConnection
IF rs.EOF THEN
'--- EMail does NOT exist ---
ELSE
'---User already exists ---
END IF
==========================
Best Regards,
>apollois<
juanfigs,
You can try to use the where clause rather than the do while...
YOu can try this approach
first you will check for the nickname since it is uniqu and it is PK...
using if statement to check if it is in the db like this..
if rs.eof then
you will compare the email data coming from the db with the inputted data like this
if rs("email") <> request.form("Email") then
do the insert thing
else
your error message for email already
end if
else
your error for nickname already use
end if
HTH...
HAppy programming...
BTW this is much faster cause you will iterate the whole db just to find the nickname and emails
You can try to use the where clause rather than the do while...
YOu can try this approach
first you will check for the nickname since it is uniqu and it is PK...
using if statement to check if it is in the db like this..
if rs.eof then
you will compare the email data coming from the db with the inputted data like this
if rs("email") <> request.form("Email") then
do the insert thing
else
your error message for email already
end if
else
your error for nickname already use
end if
HTH...
HAppy programming...
BTW this is much faster cause you will iterate the whole db just to find the nickname and emails
juanfigs,
Try to check out the revise code...
<%
Dim strConn
Dim objConnection
Dim DontAdd
set objConnection = Server.CreateObject("ADODB
strConn = "Provider=SQLOLEDB; Data Source=Monica; " & "Initial Catalog=SecureSign;" objConnection.open strConn
set rs=Server.CreateObject("AD
sql = "SELECT NickName FROM SignIn WHERE NickName = '" & Request.Form("NickName") & "';"
rs.Open sql, strConn
if rs.eof then
if rs("Email") <> trim(Request.Form("Email")
sql="INSERT INTO SignIn (NickName,FName,LName,Emai
sql=sql & " VALUES "
sql=sql & "('" & Request.Form("NickName") & "',"
sql=sql & "'" & Request.Form("FName") & "',"
sql=sql & "'" & Request.Form("LName") & "',"
sql=sql & "'" & Request.Form("Email") & "')"
on error resume next
objConnection.Execute sql,recaffected
if err<>0 then
Response.Write("No update permissions!")
end if
else
Response.write "Email already exist."
end if
else
Response.write "Nickname already exist."
end if
rs.Close
objConnection.close
%>
HTH...
HAppy programming...
Try to check out the revise code...
<%
Dim strConn
Dim objConnection
Dim DontAdd
set objConnection = Server.CreateObject("ADODB
strConn = "Provider=SQLOLEDB; Data Source=Monica; " & "Initial Catalog=SecureSign;" objConnection.open strConn
set rs=Server.CreateObject("AD
sql = "SELECT NickName FROM SignIn WHERE NickName = '" & Request.Form("NickName") & "';"
rs.Open sql, strConn
if rs.eof then
if rs("Email") <> trim(Request.Form("Email")
sql="INSERT INTO SignIn (NickName,FName,LName,Emai
sql=sql & " VALUES "
sql=sql & "('" & Request.Form("NickName") & "',"
sql=sql & "'" & Request.Form("FName") & "',"
sql=sql & "'" & Request.Form("LName") & "',"
sql=sql & "'" & Request.Form("Email") & "')"
on error resume next
objConnection.Execute sql,recaffected
if err<>0 then
Response.Write("No update permissions!")
end if
else
Response.write "Email already exist."
end if
else
Response.write "Nickname already exist."
end if
rs.Close
objConnection.close
%>
HTH...
HAppy programming...
juanfigs,
disregards my last post there are some errors... I forgot to open another recordset for the email...
===try to use this instead...
<%
Dim strConn
Dim objConnection
Dim DontAdd
set objConnection = Server.CreateObject("ADODB
strConn = "Provider=SQLOLEDB; Data Source=Monica; " & "Initial Catalog=SecureSign;" objConnection.open strConn
set rs=Server.CreateObject("AD
sql = "SELECT NickName FROM SignIn WHERE NickName = '" & Request.Form("NickName") & "';"
rs.Open sql, strConn
if rs.eof then
set rs1=Server.CreateObject("A
sql1 = "SELECT Email FROM SignIn WHERE Email = '" & Request.Form("Email") & "';"
rs1.Open sql1, strConn
if rs1.eof then
sql="INSERT INTO SignIn (NickName,FName,LName,Emai
sql=sql & " VALUES "
sql=sql & "('" & Request.Form("NickName") & "',"
sql=sql & "'" & Request.Form("FName") & "',"
sql=sql & "'" & Request.Form("LName") & "',"
sql=sql & "'" & Request.Form("Email") & "')"
on error resume next
objConnection.Execute sql,recaffected
if err<>0 then
Response.Write("No update permissions!")
end if
else
Response.write "Email already exist."
end if
else
Response.write "Nickname already exist."
end if
rs1.Close
rs.Close
objConnection.close
%>
HTH...
HAppy programming...
disregards my last post there are some errors... I forgot to open another recordset for the email...
===try to use this instead...
<%
Dim strConn
Dim objConnection
Dim DontAdd
set objConnection = Server.CreateObject("ADODB
strConn = "Provider=SQLOLEDB; Data Source=Monica; " & "Initial Catalog=SecureSign;" objConnection.open strConn
set rs=Server.CreateObject("AD
sql = "SELECT NickName FROM SignIn WHERE NickName = '" & Request.Form("NickName") & "';"
rs.Open sql, strConn
if rs.eof then
set rs1=Server.CreateObject("A
sql1 = "SELECT Email FROM SignIn WHERE Email = '" & Request.Form("Email") & "';"
rs1.Open sql1, strConn
if rs1.eof then
sql="INSERT INTO SignIn (NickName,FName,LName,Emai
sql=sql & " VALUES "
sql=sql & "('" & Request.Form("NickName") & "',"
sql=sql & "'" & Request.Form("FName") & "',"
sql=sql & "'" & Request.Form("LName") & "',"
sql=sql & "'" & Request.Form("Email") & "')"
on error resume next
objConnection.Execute sql,recaffected
if err<>0 then
Response.Write("No update permissions!")
end if
else
Response.write "Email already exist."
end if
else
Response.write "Nickname already exist."
end if
rs1.Close
rs.Close
objConnection.close
%>
HTH...
HAppy programming...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
hello, i wont mind if two users enter the same email address, coz, some people might share their addresses, get my idea??
juanfigs,
If you need to ensure unique Nickname and unique EMail, then you can do this most efficiently in one SQL:
strEMail = Request.form("EMail")
strNickname = Request.form("NickName")
'--- CHECK FOR UNIQUE EMAIL AND UNIQUE NICKNAME ---
' NOTE: If only the conbination of the two must be unique,
' then change the "OR" to "AND"
strSQL = "SELECT NickName, EMail, etc " _
& " FROM SignIn " _
& " WHERE Email = '" & strEMail & "', " _
& " OR Nickname = '" & strNickname & "'"
rs.Open strSQL, objConnection
IF rs.EOF THEN
'--- Neither EMail NOR Nickname Exists ---
'your processing code here
ELSE
'---One or the other exists ---
'your code here
END IF
This is the fastest approach.
Best Regards,
>apollois<
If you need to ensure unique Nickname and unique EMail, then you can do this most efficiently in one SQL:
strEMail = Request.form("EMail")
strNickname = Request.form("NickName")
'--- CHECK FOR UNIQUE EMAIL AND UNIQUE NICKNAME ---
' NOTE: If only the conbination of the two must be unique,
' then change the "OR" to "AND"
strSQL = "SELECT NickName, EMail, etc " _
& " FROM SignIn " _
& " WHERE Email = '" & strEMail & "', " _
& " OR Nickname = '" & strNickname & "'"
rs.Open strSQL, objConnection
IF rs.EOF THEN
'--- Neither EMail NOR Nickname Exists ---
'your processing code here
ELSE
'---One or the other exists ---
'your code here
END IF
This is the fastest approach.
Best Regards,
>apollois<
wuranma,
Are you and juanfigs the same person?
Best Regards,
>apollois<
Are you and juanfigs the same person?
Best Regards,
>apollois<
huhz... no no..
juz tellin him that some ppl might share the same email adds, so if u make it unique as well, it wun be tat user friendly..
juz tellin him that some ppl might share the same email adds, so if u make it unique as well, it wun be tat user friendly..
wuranma,
>>>some ppl might share the same email adds
That is usually frowned upon, and most, if not all, membership agreements prohibit this.
With todays EMail technology, there is no need to share an EMail address.
Best Regards,
>apollois<
>>>some ppl might share the same email adds
That is usually frowned upon, and most, if not all, membership agreements prohibit this.
With todays EMail technology, there is no need to share an EMail address.
Best Regards,
>apollois<
ASKER
Thanks you help me a lot to undestand some stuff..
Thanks
Thanks
well, u still need to ensure that ur codes cater to both the majority, and the minority, dun u?
>>> NickName as a Primary Key
Using IDENTITY integer is a much better PK. NickNames are way too common, impossible to ensure uniqueness.
Best Regards,
>apollois<