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

x
?
Solved

ASP Search Page

Posted on 2011-10-26
13
Medium Priority
?
271 Views
Last Modified: 2012-05-12
Hello,

I need help with an ASP page with 3 fields for possible search.

Fields are:

OrderID, ShipStartDate, ShipEndDate

OrderID is a required field to do a basic search.

Start and End Ship date are not required to do a simple search.

User can enter orderId and hit search

not sure if this one makes sense:
User can enter orderid + ship start date
User can enter orderid + ship end date

User can shearch by daterange start + end but orderid is required.

So far those are the combinations I came up with, they might not be all the possible ones, I would like to ask for experts input, suggestions and help to come up with this page, is possible.

thank you a lot.
0
Comment
Question by:metropia
  • 7
  • 4
12 Comments
 
LVL 101

Expert Comment

by:mlmcc
ID: 37034648
What does this have to do with Crystal Reports?

mlmcc
0
 

Author Comment

by:metropia
ID: 37035007
nothing. that was a nistake of selection and do not know how to fix it.
0
 

Author Comment

by:metropia
ID: 37035077
thank you for that mlmcc
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!

 
LVL 3

Expert Comment

by:LouSch7
ID: 37037367
I guess my question would be, how is the OrderID being used.  You mention that the OrderID is always required but 'ID' implies that this only references a singular row in an Orders table, if that is true; searching on any other field wouldn't make much sense.
0
 

Author Comment

by:metropia
ID: 37037404
an order can be related to multiple rows/ items.

some of the itmes of an order can be shipped one day, the rest on a different day.
0
 
LVL 3

Expert Comment

by:LouSch7
ID: 37037428
In that case I would personally have the user provide the OrderID and then display the results.  Include an option to then filter those results by date.  You can make this fairly clean with AJAX.
0
 

Author Comment

by:metropia
ID: 37037989
Thank you for the recommendation, but i have never used AJAX. My experience with ASP is also limited. If you have an example I could try giving it a try.

The results would return a field with a link to a Crystal Report passing the OrderId and the date values.

I was thinking to place like radio buttons to let the user select the type of search they want to do.
0
 

Author Comment

by:metropia
ID: 37039202
will it be possible that a kind expert help me with the code of this page?

Thank you very much.

0
 
LVL 3

Expert Comment

by:LouSch7
ID: 37039453
Can you post what you have so far, we can then make the required edits for your environment.
0
 

Author Comment

by:metropia
ID: 37040162
0
 
LVL 3

Accepted Solution

by:
LouSch7 earned 2000 total points
ID: 37040851
Not able to test it but, you can try the attached.
<%
'
'
'Purpose:
'

%>
<!--#include file="ConnectionString.inc"-->

<%

'Load form variables
Order_ID = request.form("Order_ID")
Line_No = request.form("LineNo")
ShipStartDate = request.form("BeforeDT")
ShipEndDate = request.form("AfterDT")
ShipDateRange = request.form("ShipDateRange")
ErrMsg = ""

IF request.form("func") = "Search" THEN
	IF request.form("Order_ID") = "" THEN
		ErrMsg = "ORDER ID IS REQUIRED TO CONTINUE!"
    END IF
	
	'Check to see if the user wishes to search by a date range
	If ShipDateRange = "1" Then
		'If they do, check to make sure they provided both a start and end ship date
		If ShipStartDate = "" OR ShipEndDate = "" Then
			ErrMsg =  "IF YOU CHOOSE TO SEARCH BY A DATE RANGE, YOU MUST PROVIDE BOTH START AND END DATES!"
		End If
	End If
	
END IF

SET cn = Server.Createobject("ADODB.Connection")
cn.Open ConnectionStringRath

search_sql = 	"SELECT co.id as order_id, co.customer_id, co.customer_po_ref, c.name, c.city, count(col.line_no) as linecount " & _
                "FROM customer_order co " & _
                "INNER JOIN cust_order_line col ON col.cust_order_id = co.id " & _
                "INNER JOIN customer c ON c.id = co.customer_id " & _
                "WHERE co.status <> 'X' " & _
                "AND co.id='" & Order_ID & "' "

'Start our case selection for search variables
Dim myCase
myCase = 0

'Ensure no errors have been detected thus far
If ErrMsg = "" Then
	If ShipStartDate <> "" Then myCase = myCase + 1 End If
	If ShipEndDate <> "" Then myCase = myCase + 2 End If
	If ShipDateRange = 1 Then myCase = 4 End If
	
	Select Case myCase
		Case 1 'ShipStartDate was provided but checkbox was not selected - search by ShipStartDate
			search_sql = search_sql & "AND ShipStartDate = '" & ShipStartDate & "' "
		Case 2 'ShipEndDate was provided but checkbox was not selected - search by ShipEndDate
			search_sql = search_sql & "AND ShipEndDate = '" & ShipEndDate & "' "
		Case 4 'Checkbox was selected, use ShipStartDate and ShipEndDate as a range
			search_sql = search_sql & "AND ShipStartDate >= '" & ShipStartDate & "' AND ShipEndDate <= '" & ShipEndDate & "' "
		Case Else
			search_sql = search_sql
	End Select
End If

'Finalize original query
search_sql = search_sql & "GROUP BY co.id, co.customer_id, co.customer_po_ref, c.name, c.city "

set rsData = cn.execute(search_sql)


%>

<html>
<head>
<title>Packing List Search Page</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<link href="StyleSheet.css" rel="stylesheet" type="text/css" />
<script type="text/javascript" language="JavaScript" src="security.js"></script>
<style type="text/css">
body
{
	font-family: 'Lucida Grande', 'Lucida Sans Unicode', Verdana, sans-serif;
	font-size: 13px;
}
td
{
	font-family:  'Lucida Grande', 'Lucida Sans Unicode', Verdana, sans-serif;
	font-size: 13px;
}
td.title-results
{
    text-align: left;
    padding-top: 10px; 
    padding-bottom: 3px; 
    padding-left: 2px;
    font-size: 14px;
}
.yellow-header
{
    background-color: #FFFF99;
	font-weight:bold;
	text-align: center;
	font-size: 12px;   
}
.row-style
{
    background-color: #FFFFFF;
    text-align: center;
    font-size: 12px;
}
td.search-labels
{
   font-size: 13px;
   
}
.nav-top
{
   font-size: 15px;
}	  
</style>
</head>
<body bgcolor="#FFFFFF" onload="document.form1.Order_ID.focus();">
<form name="form1" method="post" action="PackListSummarySearch.asp">
<input type="hidden" name="func" value="Search" />
    <table border="0" width="600">
        <tr>
            <td colspan="4" class="nav-top"><a href="Index.asp">Start</a> - <b>Search Packing List</b></td>
        </tr>
        <tr>
            <td colspan="4"><hr size="1" align="center" /></td>
        </tr>
    </table>
    <table border="0" align="left" width="600"  cellpadding="3" cellspacing="1">
<%
IF ErrMsg <> "" THEN
%>
    <tr>
      <td colspan="4">
        <div align="left"><font face="'Lucida Grande', 'Lucida Sans Unicode', Verdana, sans-serif" size="2" color="#FF0000"><%=ErrMsg%>
          </font></div>
      </td>
    </tr>
<%
END IF
%>
    <tr>
		<td class="search-labels" nowrap="nowrap" width="70">Order ID</td>
        <td><input type="text" name="Order_ID" size="10" maxlength="7" style="text-transform:uppercase;" onKeyUp="javascript:this.value=this.value.toUpperCase();" value="<%=Order_ID%>" /></td>
        <td class="search-labels" nowrap="nowrap" width="70">Line No</td>
        <td><input type="text" name="LineNo" size="10" maxlength="2" value="<%=Line_No%>" /></td>
    </tr>
    <tr>
         <td class="search-labels" nowrap="nowrap">Ship Start Date</td>
        <td><input type="text" name="ShipStartDate" size="10" maxlength="10" value="<%=StartDate%>" /></td>
        <td class="search-labels" nowrap="nowrap">Ship End Date</td>
        <td><input type="text" name="ShipEndDate" size="10" maxlength="10" value="<%=EndDate%>" /></td>
    </tr>

    <tr>
        <td colspan="3">
			Use Start and End Date as Range: 
			<input name="ShipDateRange" type="checkbox" value="1">
		</td>
		<td><input type="submit" name="Search" value="Search" style="width: 88px" /></td>
    </tr>
	<tr>
      <td colspan="4"><hr size="1" align="center" /></td>
    </tr>
<%
IF not rsData.eof THEN
	'Standard Search
%>
    <tr>
      <td colspan="4" class="title-results">Results</td>
    </tr>
    <tr valign="bottom">
		<td colspan="6">
		<table border="0" cellpadding="3" cellspacing="1" bgcolor="#CCCCCC" width="100%">
			<tr class="yellow-header">
				<td>Order ID</td>
				<td>Cust ID</td>
				<td>Cust Name</td>
				<td>Cust City</td>
				<td>Line Count</td>
				<td>Action</td>
	    	</tr>
<%
	do while not rsData.eof
%>
			<tr class="row-style">
				<td><%=rsData.fields("order_id")%></td>
				<td><%=rsData.fields("customer_id")%></td>
				<td title="<%=rsData("name")%>"><%If len(rsData("name")) > 20 THEN response.write Left(rsData("name"), 20) & "..." ELSE response.write rsData("name") END IF%></td>
				<td><%=rsData.fields("city")%></td>
				<td><%=rsData.fields("linecount")%></td>
				<td nowrap="nowrap"><a href="/PrintPackSummary.aspx?OrderID=<%=rsData("order_id")%>">Print Packing List Summary</a></td>				
			</tr>
<%
		rsData.MoveNext
	loop
%>
   		</table>
   		</td>
   	</tr>
<%
ELSE
%>
    <tr>
      <td colspan="4"><div class="title-results">No Results</div><hr size="1" align="center" />
      </td>
    </tr>
<%
END IF
%>
  </table>
  </form>
</body>
</html>

<%
cn.close
set cn = nothing
set rsData = nothing
set rsData2 = nothing
%>

Open in new window

0
 

Author Closing Comment

by:metropia
ID: 37056839
thanks a lot!
0

Featured Post

Industry Leaders: 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!

Question has a verified solution.

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

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

578 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