Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 931
  • Last Modified:

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.Connection")
     strConn = "Provider=SQLOLEDB; Data Source=Monica; " & "Initial Catalog=SecureSign;"     objConnection.open strConn
     
     set rs=Server.CreateObject("ADODB.recordset")
     
     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,Email)"
          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>Welcome " & request.Form ("NickName") & "</h3>")
          end if
     end if
     objConnection.close
%>
0
juanfigs
Asked:
juanfigs
  • 5
  • 4
  • 3
  • +1
1 Solution
 
apolloisCommented:
Hi juanfigs,

>>> 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<
0
 
apolloisCommented:
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<
0
 
gladxmlCommented:
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









0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
gladxmlCommented:
juanfigs,

Try to check out the revise code...


<%
    Dim strConn
    Dim objConnection
    Dim DontAdd

    set objConnection = Server.CreateObject("ADODB.Connection")
    strConn = "Provider=SQLOLEDB; Data Source=Monica; " & "Initial Catalog=SecureSign;"     objConnection.open strConn
   
    set rs=Server.CreateObject("ADODB.recordset")
   
    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")) then

         sql="INSERT INTO SignIn (NickName,FName,LName,Email)"
         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...
0
 
gladxmlCommented:
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.Connection")
    strConn = "Provider=SQLOLEDB; Data Source=Monica; " & "Initial Catalog=SecureSign;"     objConnection.open strConn
   
    set rs=Server.CreateObject("ADODB.recordset")
   
    sql = "SELECT NickName FROM SignIn WHERE NickName = '" & Request.Form("NickName") & "';"
    rs.Open sql, strConn
     
     
     if rs.eof then
     
     set rs1=Server.CreateObject("ADODB.recordset")
    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,Email)"
         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...
0
 
gladxmlCommented:
juanfigs,

You will notice on my last post that I open two recordset... because basing on your requirement it will be much faster rather iterating on the whole db using do while...

On the first recordset we check for the username

if nickname is not found then we will search again for the email with another recordset

if email is not found the dat will be inserted on the db...

else

it will dispay your error message


I use two recordset object so that the code still had the same functionality that you have on your original code...

but it is much faster... Right now you dont see it cause the data you have is only few but what if your data is around 10000 and then you will use do while and loop through 10000 records...

HTH...

HAppy programming..
0
 
wuranmaCommented:
hello, i wont mind if two users enter the same email address, coz, some people might share their addresses, get my idea??
0
 
apolloisCommented:
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<
0
 
apolloisCommented:
wuranma,

Are you and juanfigs the same person?

Best Regards,
>apollois<
0
 
wuranmaCommented:
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..
0
 
apolloisCommented:
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<
0
 
juanfigsAuthor Commented:
Thanks you help me a lot to undestand some stuff..

Thanks
0
 
wuranmaCommented:
well, u still need to ensure that ur codes cater to both the majority, and the minority, dun u?
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 5
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now