By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,659 Members | 1,611 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,659 IT Pros & Developers. It's quick & easy.

I need help in troubleshooting duplicate records

tsubasa
P: 64
I have an native ASP page that is used to login and uses the email address to sort records in another page. On the page where the sorting is done, the email is compared with two columns where it will match records that are based on the login email address. When a user who has logon compares his email to the first column, the records are displayed correctly, but if the email is compared to the column next to is, the data displays in duplicates. Would someone please review my page and tell me what am I doing wrong? Thanks in advance!

Expand|Select|Wrap|Line Numbers
  1. <!--#include file="../ao_includes/no-cache.asp"-->
  2. <!--#include file="../ao_includes/setup.asp"-->
  3. <!--#include file="../ao_includes/functions.asp"-->
  4. <%
  5. If Session("PassCheck") <> True Then
  6.   Response.Redirect("logon.asp")
  7. End If
  8. %>
  9. <html>
  10. <%
  11. session("adminlevel")="1"
  12. email= Request.Cookies("email")
  13. customerid= Request.Cookies("customerid")
  14. %>
  15.  
  16. <body>
  17. <form name="admin" action="logout.asp" method="post">
  18. <table border="0" width="100%" id="table2" height="95">
  19.     <tr>
  20.         <td width="370" rowspan="3">
  21.         <img border="0" src="http://bytes.com/submit/img/UA.jpg" width="174" height="96"></td>
  22.         <td width="364" align="center" height="30"><font color="#000080" size="4">Ls</font></td>
  23.         <td align="center" valign="bottom" rowspan="3">&nbsp;</td>
  24.     </tr>
  25.     <tr>
  26.         <td width="364" align="center"><%=aounit%></td>
  27.     </tr>
  28.     <tr>
  29.         <td width="364" align="center" height="21">Click on an order below.</td>
  30.     </tr>
  31. </table>
  32. <hr>
  33.  
  34. <table border="0" width="100%" id="table5">
  35.     <tr>
  36.         <td width="128"><a href="chgpsswrd.asp">Change Password</a></td>
  37.         <td bgcolor="#000080"><font color="#FFFFFF">Order No</font></td>
  38.         <td width="206" bgcolor="#000080"><font color="#FFFFFF">Name</font></td>
  39.         <td width="221" bgcolor="#000080"><font color="#FFFFFF">Date</font></td>
  40.         <td width="131" bgcolor="#000080"><font color="#FFFFFF">Status</font></td>
  41.         <td width="198">&nbsp;</td>
  42.     </tr>
  43. </table>
  44.  
  45. <%
  46.   Set CNObj=Server.CreateObject("ADODB.Connection")
  47.   Set rs=Server.CreateObject("ADODB.Recordset")
  48.   SQL="SELECT * FROM Orders WHERE Status='Pending Approval' AND aoemail ='" & email & "' Or aoemail1 ='" & email & "'"
  49.   rs.Open SQL,dbconn
  50.  
  51. While Not rs.EOF 
  52.     orderid=rs("id")
  53.     orderno=rs("orderno")
  54.     shipgrade=rs("shipgrade")
  55.     shipfname=rs("shipfname")
  56.     shiplname=rs("shiplname")
  57.     status=rs("status")
  58.     orderdate=rs("orderdate")
  59.     Session("orderid")=rs("id")
  60. %>
  61.  
  62. <table border="0" width="100%" id="table4">
  63.         <tr>
  64.             <td width="130">&nbsp;</td>
  65.             <td width="144"><a href="orderview.asp?orderid=<%=orderid%>">
  66.             <font size="1" face="Verdana" color="#000000"><%=orderno%></font></a></td>
  67.             <td width="264"><a href="orderview.asp?orderid=<%=orderid%>">
  68.             <font size="1" face="Verdana" color="#000000"><%=shipgrade%>&nbsp; <%=shipfname%>&nbsp;<%=shiplname%></font></a></td>
  69.             <td width="182"><a href="orderview.asp?orderid=<%=orderid%>">
  70.             <font size="1" face="Verdana" color="#000000"><%=orderdate%></font></a></td>
  71.             <td width="36"><a href="orderview.asp?orderid=<%=orderid%>">
  72.             <font size="1" face="Verdana" color="#000000"><%=z%></font></a></td>
  73.             <td width="130"><font size="1" face="Verdana" color="#000000"><%=status%></font></a></td>
  74.             <td width="199"></td>
  75.         </tr>
  76. <%  
  77. rs.MoveNext
  78. Wend
  79. rs.Close
  80. %>
  81. </table>
  82.  
  83.  
  84. <%
  85.   Set CNObj=Server.CreateObject("ADODB.Connection")
  86.   Set rs=Server.CreateObject("ADODB.Recordset")
  87.   SQL="SELECT * FROM Orders WHERE Status='Walk-in' AND aoemail ='" & email & "' Or aoemail1 ='" & email & "' "
  88.   rs.Open SQL,dbconn
  89.  
  90. While Not rs.EOF 
  91.     orderid=rs("id")
  92.     orderno=rs("orderno")
  93.     shipgrade=rs("shipgrade")
  94.     shipfname=rs("shipfname")
  95.     shiplname=rs("shiplname")
  96.     status=rs("status")
  97.     orderdate=rs("orderdate")
  98.     Session("orderid")=rs("id")
  99. %>
  100.  
  101. <table border="0" width="100%" id="table4">
  102.         <tr>
  103.             <td width="130">&nbsp;</td>
  104.             <td width="144"><a href="orderviewwalkin.asp?orderid=<%=orderid%>">
  105.             <font size="1" face="Verdana" color="#000000"><%=orderno%></font></a></td>
  106.             <td width="264"><a href="orderviewwalkin.asp?orderid=<%=orderid%>">
  107.             <font size="1" face="Verdana" color="#000000"><%=shipgrade%>&nbsp; <%=shipfname%>&nbsp;<%=shiplname%></font></a></td>
  108.             <td width="182"><a href="orderviewwalkin.asp?orderid=<%=orderid%>">
  109.             <font size="1" face="Verdana" color="#000000"><%=orderdate%></font></a></td>
  110.             <td width="36"><a href="orderviewwalkin.asp?orderid=<%=orderid%>">
  111.             <font size="1" face="Verdana" color="#000000"><%=z%></font></a></td>
  112.             <td width="130"><font size="1" face="Verdana" color="#000000"><%=status%></font></a></td>
  113.             <td width="199"></td>
  114.         </tr>
  115. <%  
  116. rs.MoveNext
  117. Wend
  118. rs.Close
  119. %>
  120. </table>
  121.  
  122. <p>
  123. <a href="login.asp">
  124. <input type="submit" name="Submit" value="Logout"></a></p>
  125.  
  126. <table border="0" width="100%" id="table6">
  127.     <tr>
  128.         <td>&nbsp;</td>
  129.         <td>&nbsp;</td>
  130.         <td width="137"><a href="completedorders.asp">Completed Orders</a></td>
  131.         <td width="114">&nbsp;</td>
  132.         <td width="151"><a href="cancelledorders.asp">Cancelled Orders</a></td>
  133.         <td width="111">&nbsp;</td>
  134.         <td>&nbsp;</td>
  135.     </tr>
  136. </table>
  137. </form>
  138. </body>
  139. </html>
  140.  
Oct 15 '10 #1
Share this Question
Share on Google+
4 Replies


danp129
Expert 100+
P: 321
Put Response.write(SQL & "<BR>") before each rs.Open.

Run each query on your db to determine if the problem is the query or your code.

My guess is comparing the e-mail to a null value is considered a match or the data is duplicated in the db.
Oct 15 '10 #2

tsubasa
P: 64
Thank you for assisting me. I enter the code as you recommended. Below is what I got:

SELECT * FROM Orders WHERE Status='Pending Approval' AND aoemail ='john.morgan@gmail.com' Or aoemail1 ='john.morgan@gmail.com'
4755 Sgt John Doe 10/15/2010 New
4756 Sgt John Doe 10/15/2010 Completed
4757 Civ Tom Slate 10/15/2010 Completed
SELECT * FROM Orders WHERE Status='Walk-in' AND aoemail ='john.morgan@gmail.com' Or aoemail1 ='john.morgan@gmail.com'
4755 Sgt John Doe 10/15/2010 New
4756 Sgt John Doe 10/15/2010 Completed
4757 Civ Tom Slate 10/15/2010 Completed


As you can see the query is working, but duplicating the efforts because the email is on the 2nd colum of the table. If I use the email address in the 1st colum of the table. It will display the information correctly.
I have provide an attachment of the table.

-Tsu

danp129,
Oct 16 '10 #3

danp129
Expert 100+
P: 321
Put parenthesis around your OR statements. Right now it is parsing it as follows:
(Pending='xxx' AND aoemail='xxx') OR aoemail1='xxx'

You want to put the parenthesis in so it parses as:
Pending='xxx' AND (aoemail='xxx' OR aoemail1='xxx')
Oct 18 '10 #4

tsubasa
P: 64
I did as you directed and then I also tried it with parenthesis on both (Pending='xxx'AND aoemail='xxx') OR (Pending='xxx' AND aoemail1='xxx) and it worked like a charm. Thanks alot Danp129, your technical advise was perfect.

R,

-Tsu
Oct 20 '10 #5

Post your reply

Sign in to post your reply or Sign up for a free account.