Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations bkrike on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Order search - not working

Status
Not open for further replies.
loveday

loveday

Programmer
Sep 19, 2004
106
US
I found this to be very upsetting.

If a record matches customer's search code, display the record
otherwise, display a message indicating that record is not on our database.
That shouldn't be that hard, yet anytime I open the page to test it, first thing I see is the message that says "This record doesn't exist in our database.

here is a snip
Code: 
dim productCode
ProductCode = rs("ccode")
'If request.form("orderNumber") = ProductCode then
searchstr = "SELECT products.ccode, orders.orderid, orders.odate, orders.deliverFlag, Sum((oitems.numitems)*(orders.orderamount)) AS Amount, customers.cfirstname , customers.clastname, orders.ostatus, orders.oprocessed" & _
          " FROM orders, customers, oitems,products " & _
          " WHERE customers.custid = orders.ocustomerid and orders.orderid = oitems.orderid and oitems.catalogid = products.catalogid and products.ccode='"&request.form("orderNumber")&"'" & _
          " GROUP BY orders.orderid, orders.odate,customers.cfirstname,customers.clastname,orders.ostatus,orders.oprocessed, products.ccode,orders.deliverFlag" & _
          " ORDER BY orders.odate desc "
objConn.Execute(searchstr)

'else
'  response.write "<div align='left'><tr><td>No such record exists in our database</td></tr></table>"
'  response.end
'end if
 
Sort by date Sort by votes
you're not giving us nearly enough to help.

we need to see the code that is relavent to the conditioning. for instance the portion that creates rs("ccode").

and then some code that isn't commented out. run the code that you are trying to get working not commented out and let us know the error(s) and the lines they are occuring on

where does "This record doesn't exist in our database." come from also. your code states the message, "No such record exists in our database"

___________________________________________________________________

onpnt.com
SELECT * FROM programmers WHERE clue > 0
(0 row(s) affected) -->faq333-3811

 
Upvote 0 Downvote
Sorry, I left after posting this message:

Yes, the error message, which I defined, should have read, "This record doesn't exist in out database"

Here is entire code:
Code: 
<%
set rs = Server.CreateObject("ADODB.RECORDSET")
 Set objConn=Server.CreateObject("ADODB.Connection")
 objConn.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _
 "DATA SOURCE=" & server.mappath("admin\scart.mdb")

IF session("custid") = "" Then 'We have no value in session

  response.redirect "error.asp?msg=" & server.urlencode("You must log in to view your order status")

End IF

sqlstmt = "SELECT products.ccode, orders.orderid, orders.odate, orders.deliverFlag, Sum((oitems.numitems)*(orders.orderamount)) AS Amount, customers.cfirstname , customers.clastname, orders.ostatus, orders.oprocessed" & _
          " FROM orders, customers, oitems,products " & _
          " WHERE customers.custid = orders.ocustomerid and orders.orderid = oitems.orderid and oitems.catalogid = products.catalogid and customers.custid="&SESSION("custid") & _
          " GROUP BY orders.orderid, orders.odate,customers.cfirstname,customers.clastname,orders.ostatus,orders.oprocessed, products.ccode,orders.deliverFlag" & _
          " ORDER BY orders.odate desc "

'response.write sqlstmt
'response.end
Set rs = objConn.Execute(sqlstmt)
dim productCode
ProductCode = rs("ccode")
If request.form("orderNumber") = ProductCode then
searchstr = "SELECT products.ccode, orders.orderid, orders.odate, orders.deliverFlag, Sum((oitems.numitems)*(orders.orderamount)) AS Amount, customers.cfirstname , customers.clastname, orders.ostatus, orders.oprocessed" & _
          " FROM orders, customers, oitems,products " & _
          " WHERE customers.custid = orders.ocustomerid and orders.orderid = oitems.orderid and oitems.catalogid = products.catalogid and products.ccode='"&request.form("orderNumber")&"'" & _
          " GROUP BY orders.orderid, orders.odate,customers.cfirstname,customers.clastname,orders.ostatus,orders.oprocessed, products.ccode,orders.deliverFlag" & _
          " ORDER BY orders.odate desc "
objConn.Execute(searchstr)

else
  response.write "<div align='left'><tr><td>No such record exists in our database</td></tr></table>"
  response.end
end if

%>
<div align="center">
<table border="0" width="800" cellspacing="0" cellpadding="0">
  <tr>
    <td width="50%">
      <table border="0" width="77%" cellspacing="0" cellpadding="0">
      <tr>

       <tr bgcolor="#D6E7EF"><td>

     </td>
      </tr>
    </table>
 <left>
   </font></p><font size=2 face=verdana color='#000066'><b>Orders are listed by latest date first<br>
   </font></b><br>
   <form name=form1 method=Post action="orderstatus.asp">
    <table cellpadding=2 cellspacing=2>
     <tr bgcolor=#B1D4F0>
      <td><font face='Tahoma, Verdana' size=2 color='#000000'>Product Code</td>
      <td><input size=25 name=OrderNumber><input type=image border='0' name=Action src="vpnav_search.gif"></td>
     </tr>
     </table>
    <table cellpadding=2 cellspacing=2>
     <tr><td>&nbsp;</td>
     </tr>
     <tr>
       <td><input type=submit name="Received" value="Order Received"><font face='Tahoma, Verdana' size=2 color='red'>**</font>&nbsp;<--&nbsp;Click here if order is received.</td>
       <tr>
    </table>
   </form>
    <table border=0 align=left cellpadding=2 cellspacing=1>
     <tr bgcolor='#194B8A'>
      <td align=center><font face='Tahoma, Verdana' size=2 color='#FFFFFF'><b>Order #</b></font></td>
      <td align=center><font face='Tahoma, Verdana' size=2 color='#FFFFFF'><b>Amount</b></font></td>
      <td align=center><font face='Tahoma, Verdana' size=2 color='#FFFFFF'><b>Date of Order</b></font></td>
      <td align=center><font face='Tahoma, Verdana' size=2 color='#FFFFFF'><b>First Name </b></font></td>
      <td align=center><font face='Tahoma, Verdana' size=2 color='#FFFFFF'><b>Last Name </b></font></td>
      <td align=center><font face='Tahoma, Verdana' size=2 color='#FFFFFF'><b>Status</b></font></td>
      <td align=center><font face='Tahoma, Verdana' size=2 color='#FFFFFF'><b>Processed</b></font></td>
      <td align=center><font face='Tahoma, Verdana' size=2 color='#FFFFFF'><b>Deliver Status</b></font></td>
     </tr>
     <tr>
      <td valign='top' align=center><font face='Tahoma, Verdana' size=2><%=rs("ccode")%></font></td>
      <td valign='top' align=center><font face='Tahoma, Verdana' size=2><%=formatcurrency(rs("amount"),2)%></font></td>
      <td valign='top' align=center><font face='Tahoma, Verdana' size=2><%=rs("odate")%></font></td>
      <td valign='top' align=center><font face='Tahoma, Verdana' size=2><%=rs("cfirstname")%></font></td>
      <td valign='top' align=center><font face='Tahoma, Verdana' size=2><%=rs("clastname")%></font></td>
      <td valign='top' align=center><font face='Tahoma, Verdana' size=2><%=rs("ostatus")%></font></td>
      <td valign='top' align=center><font face='Tahoma, Verdana' size=2><%=rs("oprocessed")%> </font></td>
      <td valign='top' align=center><font face='Tahoma, Verdana' size=2><%=rs("deliverFlag")%></font></td>
     </tr>
     <tr>
      <td>&nbsp;</td>
     </tr>
     </table><table border=0 width='50%'>
    </table><!-- end Main Display-->

       <table border="0" width="100%" cellspacing="0" cellpadding="0">
            <tr>
              <td width="100%"><img src="vpnav_spacer.gif" width="1" height="5"></td>
            </tr>
            <tr>
              <td width="100%"><img src="vpnav_spacer.gif" width="1" height="5"></td>
            </tr>
       </table>

</left>
</div>
<%
If len(request("Received"))>0 then
      '' now, grab the order number and update everything needs to be updated
      sqlupdate = "update orders "  & _
                  "set oprocessed = 'Processed', deliverFlag='Delivered', ostatus='Closed' where orders.ocustomerid = "&SESSION("custid")
      ''' ---> execute the sqlupdate command that you've put together...
      objConn.execute sqlupdate
          'response.write sqlupdate
          'response.end

      '' then, redirect to the page again, showing the customer order has indeed been updated and marked it as "received"
      response.redirect "[URL unfurl="true"]http://localhost/millenniumhealthproducts/orderstatus.asp"[/URL]
    end if
%>
 
Upvote 0 Downvote
The portion I really see that can cause you problems is in your conditioning. actually in any conditions that you compare values you should always first convert to matching data types and then make certain the case (if a factor) is matched along with empty spaces.

something like

If cInt(request.form("orderNumber")) = cInt(ProductCode) then
or

If Trim(request.form("orderNumber")) = Trim(ProductCode) then

___________________________________________________________________

onpnt.com
SELECT * FROM programmers WHERE clue > 0
(0 row(s) affected) -->faq333-3811

 
Upvote 0 Downvote
It's more like Trim(request.form("orderNumber")) (string) but even after trying that, I still get my custom error message.

 
Upvote 0 Downvote
hey loveday

I was more so referencing the comparing of values. it seems that the values must not be the same in some way to not meet the condition when you know it does. try other methods to force types and comparisons.

otherwise I would tear it down and get the conditional working in a small test script and then add the html output for trouble shooting the problem
or I could be missing a small part in the script causing the effects. that's happen more then a few times ;)

___________________________________________________________________

onpnt.com
SELECT * FROM programmers WHERE clue > 0
(0 row(s) affected) -->faq333-3811

 
Upvote 0 Downvote
hi onpnt,
I think you misunderstood or I was unclear.

You gave two options,one string, the other a number.

I was just basically saying productCode is a string.

I *wasn't* trying to imply you did something wrong.
 
Upvote 0 Downvote
The first thing youre doing is an sqlquery on the database. What does it return?? One or more records?

After executing the sqlstatement the record pointer is at the first record of the recordset!!!!!
Are You sure that the "ccode" of the first record is the ordernumber of your form.

Change the lines you commented out:
'response.write sqlstmt
'response.end
in

Code: 
Do until rs.eof
Response.write(rs("ccode")
rs.movenext()
loop
response.end

 
Upvote 0 Downvote
hi,
thanks for the response.

Where on this code:
Code: 
dim productCode
ProductCode = rs("ccode")
If request.form("orderNumber") = ProductCode then
searchstr = "SELECT products.ccode, orders.orderid, orders.odate, orders.deliverFlag, Sum((oitems.numitems)*(orders.orderamount)) AS Amount, customers.cfirstname , customers.clastname, orders.ostatus, orders.oprocessed" & _
          " FROM orders, customers, oitems,products " & _
          " WHERE customers.custid = orders.ocustomerid and orders.orderid = oitems.orderid and oitems.catalogid = products.catalogid and products.ccode='"&request.form("orderNumber")&"'" & _
          " GROUP BY orders.orderid, orders.odate,customers.cfirstname,customers.clastname,orders.ostatus,orders.oprocessed, products.ccode,orders.deliverFlag" & _
          " ORDER BY orders.odate desc "
objConn.Execute(searchstr)

else
  response.write "<div align='left'><tr><td>No such record exists in our database</td></tr></table>"
  response.end
end if

should your suggested code go?

Because I tried your suggestion and still got same problem.
 
Upvote 0 Downvote
Before the "DIM ProductCode.

For debugging i always use an response.write action, to see what results there are after an action.
My suggested code is only for debugging, so you can see what the result of your query is on THE SCREEN before you do the "If request.form("orderNumber... blahblahblah")
In the second part you compare the output from a query to an input from an Form. Don't you want to know what result you get from the first query before thinking something is wrong.

if blah = bluh then
blah = "hello"
else
blah = "Bye"
end if

The computer makes no mistakes if blah has the same value as bluh than blah becomes "hello" and nothing else.
So output your results to the screen before letting the computer comparing and thinking something is wrong in the second part ( I mean the part from DIM productcode )

You also give no answer on the question :
The first thing youre doing is an sqlquery on the database. What does it return?? One or more records?
Click to expand...
 
Upvote 0 Downvote
I got it all working now.

thanks for all the help
 
Upvote 0 Downvote
sorry, didn't mean to "disappear"

Here is what I did to solve it, after many trials.

If len(request.form("orderNumber"))>0 then
' then query the database

Dim rs
set rs = server.createobject("adodb.recordset")
set rs = objConn.Execute(searchstr)
end if
If rs.eof then
response.redirect "searchagain.asp" 'write "<div align='left'><tr><td>No such record exists in our database</td></tr></table>"
' response.end
else
''put your code here to write the product search results...
end if
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

leifoet
  • Locked
  • Question
Code does not run in Microsoft Edge - it does in Google Chrome
Replies
3
Views
953
leifoet
leifoet
penguinspeaks
  • Locked
  • Question
iis woos (not displaying pages correctly)
Replies
1
Views
544
penguinspeaks
penguinspeaks
leifoet
  • Locked
  • Question
Multi-selection dropdown list does not work
Replies
0
Views
304
leifoet
leifoet
leifoet
  • Locked
  • Question
ASP window.open not working without activating pop up blokker
Replies
5
Views
384
ChrisHirst
ChrisHirst
Ambientguitar
  • Locked
  • Question
Issue with this not calling the Sub in Classic ASP page
Replies
5
Views
581
MarkSweetland
MarkSweetland

Part and Inventory Search

Sponsor

Back
Top