?
Solved

ASP ADO

Posted on 2003-03-17
13
Medium Priority
?
929 Views
Last Modified: 2012-06-27
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
Comment
Question by:juanfigs
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 3
  • +1
13 Comments
 
LVL 10

Expert Comment

by:apollois
ID: 8157455
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
 
LVL 10

Expert Comment

by:apollois
ID: 8157474
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
 
LVL 15

Expert Comment

by:gladxml
ID: 8157490
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
Independent Software Vendors: 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!

 
LVL 15

Expert Comment

by:gladxml
ID: 8157522
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
 
LVL 15

Expert Comment

by:gladxml
ID: 8157574
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
 
LVL 15

Accepted Solution

by:
gladxml earned 200 total points
ID: 8157618
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
 

Expert Comment

by:wuranma
ID: 8157644
hello, i wont mind if two users enter the same email address, coz, some people might share their addresses, get my idea??
0
 
LVL 10

Expert Comment

by:apollois
ID: 8157649
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
 
LVL 10

Expert Comment

by:apollois
ID: 8157659
wuranma,

Are you and juanfigs the same person?

Best Regards,
>apollois<
0
 

Expert Comment

by:wuranma
ID: 8157681
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
 
LVL 10

Expert Comment

by:apollois
ID: 8157694
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
 

Author Comment

by:juanfigs
ID: 8157699
Thanks you help me a lot to undestand some stuff..

Thanks
0
 

Expert Comment

by:wuranma
ID: 8157708
well, u still need to ensure that ur codes cater to both the majority, and the minority, dun u?
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:   The Exchange of informatio…
Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question