I need help in troubleshooting duplicate records

Collapse
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • tsubasa
    New Member
    • Aug 2008
    • 64
    #1

    I need help in troubleshooting duplicate records

    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!

    Code:
    <!--#include file="../ao_includes/no-cache.asp"-->
<!--#include file="../ao_includes/setup.asp"-->
<!--#include file="../ao_includes/functions.asp"-->
<%
If Session("PassCheck") <> True Then
  Response.Redirect("logon.asp")
End If
%>
<html>
<%
session("adminlevel")="1"
email= Request.Cookies("email")
customerid= Request.Cookies("customerid")
%>

<body>
<form name="admin" action="logout.asp" method="post">
<table border="0" width="100%" id="table2" height="95">
	<tr>
		<td width="370" rowspan="3">
		<img border="0" src="http://bytes.com/submit/img/UA.jpg" width="174" height="96"></td>
		<td width="364" align="center" height="30"><font color="#000080" size="4">Ls</font></td>
		<td align="center" valign="bottom" rowspan="3">&nbsp;</td>
	</tr>
	<tr>
		<td width="364" align="center"><%=aounit%></td>
	</tr>
	<tr>
		<td width="364" align="center" height="21">Click on an order below.</td>
	</tr>
</table>
<hr>

<table border="0" width="100%" id="table5">
	<tr>
		<td width="128"><a href="chgpsswrd.asp">Change Password</a></td>
		<td bgcolor="#000080"><font color="#FFFFFF">Order No</font></td>
		<td width="206" bgcolor="#000080"><font color="#FFFFFF">Name</font></td>
		<td width="221" bgcolor="#000080"><font color="#FFFFFF">Date</font></td>
		<td width="131" bgcolor="#000080"><font color="#FFFFFF">Status</font></td>
		<td width="198">&nbsp;</td>
	</tr>
</table>

<%
  Set CNObj=Server.CreateObject("ADODB.Connection")
  Set rs=Server.CreateObject("ADODB.Recordset")
  SQL="SELECT * FROM Orders WHERE Status='Pending Approval' AND aoemail ='" & email & "' Or aoemail1 ='" & email & "'"
  rs.Open SQL,dbconn

While Not rs.EOF 
    orderid=rs("id")
    orderno=rs("orderno")
    shipgrade=rs("shipgrade")
    shipfname=rs("shipfname")
    shiplname=rs("shiplname")
    status=rs("status")
    orderdate=rs("orderdate")
    Session("orderid")=rs("id")
%>
 
<table border="0" width="100%" id="table4">
		<tr>
			<td width="130">&nbsp;</td>
			<td width="144"><a href="orderview.asp?orderid=<%=orderid%>">
            <font size="1" face="Verdana" color="#000000"><%=orderno%></font></a></td>
            <td width="264"><a href="orderview.asp?orderid=<%=orderid%>">
            <font size="1" face="Verdana" color="#000000"><%=shipgrade%>&nbsp; <%=shipfname%>&nbsp;<%=shiplname%></font></a></td>
            <td width="182"><a href="orderview.asp?orderid=<%=orderid%>">
            <font size="1" face="Verdana" color="#000000"><%=orderdate%></font></a></td>
            <td width="36"><a href="orderview.asp?orderid=<%=orderid%>">
            <font size="1" face="Verdana" color="#000000"><%=z%></font></a></td>
            <td width="130"><font size="1" face="Verdana" color="#000000"><%=status%></font></a></td>
            <td width="199"></td>
		</tr>
<%  
rs.MoveNext
Wend
rs.Close
%>
</table>


<%
  Set CNObj=Server.CreateObject("ADODB.Connection")
  Set rs=Server.CreateObject("ADODB.Recordset")
  SQL="SELECT * FROM Orders WHERE Status='Walk-in' AND aoemail ='" & email & "' Or aoemail1 ='" & email & "' "
  rs.Open SQL,dbconn

While Not rs.EOF 
    orderid=rs("id")
    orderno=rs("orderno")
    shipgrade=rs("shipgrade")
    shipfname=rs("shipfname")
    shiplname=rs("shiplname")
    status=rs("status")
    orderdate=rs("orderdate")
    Session("orderid")=rs("id")
%>
 
<table border="0" width="100%" id="table4">
		<tr>
			<td width="130">&nbsp;</td>
			<td width="144"><a href="orderviewwalkin.asp?orderid=<%=orderid%>">
            <font size="1" face="Verdana" color="#000000"><%=orderno%></font></a></td>
            <td width="264"><a href="orderviewwalkin.asp?orderid=<%=orderid%>">
            <font size="1" face="Verdana" color="#000000"><%=shipgrade%>&nbsp; <%=shipfname%>&nbsp;<%=shiplname%></font></a></td>
            <td width="182"><a href="orderviewwalkin.asp?orderid=<%=orderid%>">
            <font size="1" face="Verdana" color="#000000"><%=orderdate%></font></a></td>
            <td width="36"><a href="orderviewwalkin.asp?orderid=<%=orderid%>">
            <font size="1" face="Verdana" color="#000000"><%=z%></font></a></td>
            <td width="130"><font size="1" face="Verdana" color="#000000"><%=status%></font></a></td>
            <td width="199"></td>
		</tr>
<%  
rs.MoveNext
Wend
rs.Close
%>
</table>

<p>
<a href="login.asp">
<input type="submit" name="Submit" value="Logout"></a></p>

<table border="0" width="100%" id="table6">
	<tr>
		<td>&nbsp;</td>
		<td>&nbsp;</td>
		<td width="137"><a href="completedorders.asp">Completed Orders</a></td>
		<td width="114">&nbsp;</td>
		<td width="151"><a href="cancelledorders.asp">Cancelled Orders</a></td>
		<td width="111">&nbsp;</td>
		<td>&nbsp;</td>
	</tr>
</table>
</form>
</body>
</html>
    Tags: None
    • danp129
      Recognized Expert Contributor
      • Jul 2006
      • 323
      #2
      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.

        Comment

        • tsubasa
          New Member
          • Aug 2008
          • 64
          #3
          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@g mail.com' Or aoemail1 ='john.morgan@g mail.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@g mail.com' Or aoemail1 ='john.morgan@g mail.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,

            Comment

            • danp129
              Recognized Expert Contributor
              • Jul 2006
              • 323
              #4
              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')

                Comment

                • tsubasa
                  New Member
                  • Aug 2008
                  • 64
                  #5
                  I did as you directed and then I also tried it with parenthesis on both (Pending='xxx'A ND aoemail='xxx') OR (Pending='xxx' AND aoemail1='xxx) and it worked like a charm. Thanks alot Danp129, your technical advise was perfect.

                  R,

                  -Tsu

                    Comment

                    Previous template Next
                    Working...