|
| |
|
|
baldcat
Posts: 41
Joined: 6/30/2005
Status: offline
 |
Display Date Range - 8/23/2005 2:45:18
Well I'm back with another....
What I'm trying to do is to select date ranges in a form then display the results with the range. I was going great guns then I relised it was nothing like the search form I did , and that I had no idea ;)
ANyway here is my date form .. (index.asp)
<form action="results.asp" method="POST" name="sampleform" id="purchase">
<input type="text" name="firstinput" size=20> <small><a href="javascript:showCal('Calendar1')">Select Date</a></small>
<p><input type="text" name="secondinput" size=20> <small><a href="javascript:showCal('Calendar2')">Select Date</a></small>
<p>
<input name="Submit" type="submit" class="button" value="Submit">
Here is my results page... (results.asp)
<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="../Connections/CFS.asp" -->
<%
Dim Recordset1
Dim Recordset1_numRows
Set Recordset1 = Server.CreateObject("ADODB.Recordset")
Recordset1.ActiveConnection = MM_CFS_STRING
Recordset1.Source = "SELECT po_type, branch, ro_no, stock_no, date_exp_d FROM EMMG.RDH, EMMG.RDL WHERE po_type = 'V' ORDER BY branch"
Recordset1.CursorType = 0
Recordset1.CursorLocation = 2
Recordset1.LockType = 1
Recordset1.Open()
Recordset1_numRows = 0
%>
<%
Dim Repeat1__numRows
Dim Repeat1__index
Repeat1__numRows = 10
Repeat1__index = 0
Recordset1_numRows = Recordset1_numRows + Repeat1__numRows
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>Untitled Document</title>
</head>
<body>
<table width="75%" >
<tr>
<th scope="col">Date Expected </th>
<th scope="col">Stock Number</th>
<th scope="col">Branch</th>
<th scope="col">Job Card Number </th>
</tr>
<tr>
<th scope="col"> </th>
<th scope="col"> </th>
<th scope="col"> </th>
<th scope="col"> </th>
</tr>
<%
While ((Repeat1__numRows <> 0) AND (NOT Recordset1.EOF))
%>
<tr>
<td><%=(Recordset1.Fields.Item("date_exp_d").Value)%></td>
<td><%=(Recordset1.Fields.Item("stock_no").Value)%></td>
<td><%=(Recordset1.Fields.Item("branch").Value)%></td>
<td><%=(Recordset1.Fields.Item("ro_no").Value)%></td>
</tr>
<%
Repeat1__index=Repeat1__index+1
Repeat1__numRows=Repeat1__numRows-1
Recordset1.MoveNext()
Wend
%>
</table>
</body>
</html>
<%
Recordset1.Close()
Set Recordset1 = Nothing
%>
The field "date_exp_d" is the one I want to display in the range selected with the first page...
|
|
|
|
BeTheBall
Posts: 6355
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Display Date Range - 8/23/2005 10:02:22
Does this work? I am assuming you are using Access. If not, change the # to a single quote (')
<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="../Connections/CFS.asp" -->
<%
Dim Recordset1
Dim Recordset1_numRows
startdate = Cdate(Request.Form("firstinput"))
enddate = Cdate(Request.Form("secondinput"))
Set Recordset1 = Server.CreateObject("ADODB.Recordset")
Recordset1.ActiveConnection = MM_CFS_STRING
Recordset1.Source = "SELECT po_type, branch, ro_no, stock_no, date_exp_d FROM EMMG.RDH, EMMG.RDL WHERE po_type = 'V' AND (date_exp_d BETWEEN #"& startdate &"# AND #"& enddate &"#) ORDER BY branch"
Recordset1.CursorType = 0
Recordset1.CursorLocation = 2
Recordset1.LockType = 1
Recordset1.Open()
Recordset1_numRows = 0
%>
<%
Dim Repeat1__numRows
Dim Repeat1__index
Repeat1__numRows = 10
Repeat1__index = 0
Recordset1_numRows = Recordset1_numRows + Repeat1__numRows
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>Untitled Document</title>
</head>
<body>
<table width="75%" >
<tr>
<th scope="col">Date Expected </th>
<th scope="col">Stock Number</th>
<th scope="col">Branch</th>
<th scope="col">Job Card Number </th>
</tr>
<tr>
<th scope="col"> </th>
<th scope="col"> </th>
<th scope="col"> </th>
<th scope="col"> </th>
</tr>
<%
While ((Repeat1__numRows <> 0) AND (NOT Recordset1.EOF))
%>
<tr>
<td><%=(Recordset1.Fields.Item("date_exp_d").Value)%></td>
<td><%=(Recordset1.Fields.Item("stock_no").Value)%></td>
<td><%=(Recordset1.Fields.Item("branch").Value)%></td>
<td><%=(Recordset1.Fields.Item("ro_no").Value)%></td>
</tr>
<%
Repeat1__index=Repeat1__index+1
Repeat1__numRows=Repeat1__numRows-1
Recordset1.MoveNext()
Wend
%>
</table>
</body>
</html>
<%
Recordset1.Close()
Set Recordset1 = Nothing
%>
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
baldcat
Posts: 41
Joined: 6/30/2005
Status: offline
|
RE: Display Date Range - 8/23/2005 17:03:49
Hey Duane,
I'm using mySQL.. I'll give it a whirl when I get to work.. Is there somewhere I have to tell the results page what the form I was using ??
| |
|
|
|
BeTheBall
Posts: 6355
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Display Date Range - 8/23/2005 17:34:07
Nope. Your form will post to the results page making the form field values available to the results page.
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
baldcat
Posts: 41
Joined: 6/30/2005
Status: offline
|
RE: Display Date Range - 8/23/2005 21:05:53
nah that hasn't worked.. it just comes up with page cannot be displayed..
| |
|
|
|
BeTheBall
Posts: 6355
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Display Date Range - 8/23/2005 23:21:00
Make sure you turn off friendly errors so you see the true error message:
IE - Tools - Internet Options - Advanced - Uncheck the box labeled, "Show friendly http error messages"
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
baldcat
Posts: 41
Joined: 6/30/2005
Status: offline
|
RE: Display Date Range - 8/24/2005 1:59:14
yeah I tried that, it didn't bring up anything... So I went back to basics..
<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include virtual="/Connections/CFS.asp" -->
<%
Dim Recordset1
Dim Recordset1_numRows
startdate = Cdate(Request.Form("firstinput"))
enddate = Cdate(Request.Form("secondinput"))
Set Recordset1 = Server.CreateObject("ADODB.Recordset")
Recordset1.ActiveConnection = MM_CFS_STRING
Recordset1.Source = "SELECT date_exp_d, stock_no FROM EMMG.RDL WHERE (date_exp_d BETWEEN '"& startdate &"' AND '"& enddate &"')"
Recordset1.CursorType = 0
Recordset1.CursorLocation = 2
Recordset1.LockType = 1
Recordset1.Open()
Recordset1_numRows = 0
%>
Using this as my recordset worked a treat however I need to get some data out of another table..
po_type, branch, ro_no, stock_no, date_exp_d FROM EMMG.RDH
I try this.. and it doesn't work.. It thinks about it for a little bit, then has an "internal Server error"
<%
Dim Recordset1
Dim Recordset1_numRows
startdate = Cdate(Request.Form("firstinput"))
enddate = Cdate(Request.Form("secondinput"))
Set Recordset1 = Server.CreateObject("ADODB.Recordset")
Recordset1.ActiveConnection = MM_CFS_STRING
Recordset1.Source = "SELECT date_exp_d, stock_no, po_type, branch FROM EMMG.RDL, EMMG.RDH WHERE (date_exp_d BETWEEN '"& startdate &"' AND '"& enddate &"') AND po_type = 'V'"
Recordset1.CursorType = 0
Recordset1.CursorLocation = 2
Recordset1.LockType = 1
Recordset1.Open()
Recordset1_numRows = 0
%>
| |
|
|
|
baldcat
Posts: 41
Joined: 6/30/2005
Status: offline
|
RE: Display Date Range - 8/24/2005 3:15:40
sorry , the setting hadn't taken effect...
here is the error
Response object error 'ASP 0251 : 80004005'
Response Buffer Limit Exceeded
/purchase_orders/results1.asp, line 0
Execution of the ASP page caused the Response Buffer to exceed its configured limit.
| |
|
|
|
baldcat
Posts: 41
Joined: 6/30/2005
Status: offline
|
RE: Display Date Range - 8/24/2005 21:00:26
Yeah I searched the error, on this site, and I found some info.. It has something to do with the ASPBUFFERLIMIT on the server.. I changed it from 4mb to 9mb but it made no difference.. I will give your solution a crack..
| |
|
|
|
baldcat
Posts: 41
Joined: 6/30/2005
Status: offline
|
RE: Display Date Range - 8/24/2005 21:49:43
Ok cool, I'm starting to get some results... Here is my code thus far...
index.asp
<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html><!-- InstanceBegin template="/Templates/cfs_gateway_nm.dwt.asp" codeOutsideHTMLIsLocked="false" -->
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<!-- InstanceBeginEditable name="doctitle" -->
<title>Untitled Document</title>
<!-- InstanceEndEditable -->
<!-- InstanceBeginEditable name="head" -->
<script language="javascript" src="cal2.js">
/*
Xin's Popup calendar script- Xin Yang (http://www.yxscripts.com/)
Script featured on/available at http://www.dynamicdrive.com/
This notice must stay intact for use
*/
</script>
<script language="javascript" src="cal_conf2.js"></script>
<link href="/styles.css" rel="stylesheet" type="text/css">
<!-- InstanceEndEditable -->
<link href="/styles.css" rel="stylesheet" type="text/css">
</head>
<body>
<table width="100%" >
<tr>
<td colspan="2"><img src="/images/EMMETTS_web.jpg" width="237" height="112"></td>
<td>Emmett Motors CFS Gateway - <!-- InstanceBeginEditable name="headertext" -->Purshase Order Date Search <!-- InstanceEndEditable --></td>
<td> </td>
</tr>
<tr bgcolor="#00629F" class="capmain">
<td width="7%"><a href="/index.asp" class="button">Home</a></td>
<td width="14%"> </td>
<td width="39%"> </td>
<td width="33%"> </td>
</tr>
</table>
<table width="90%" >
<tr>
<td> </td>
</tr>
</table>
<!-- InstanceBeginEditable name="Main Body" -->
<table width="90%" align="center" >
<tr>
<td>
<form action="results1.asp" method="POST" name="sampleform" id="purchase">
<input type="text" name="firstinput" size=20> <small><a href="javascript:showCal('Calendar1')">Select Date</a></small>
<p><input type="text" name="secondinput" size=20> <small><a href="javascript:showCal('Calendar2')">Select Date</a></small>
<p>
<input name="Submit" type="submit" class="button" value="Submit">
</form></td>
</tr>
</table>
<!-- InstanceEndEditable -->
</body>
<!-- InstanceEnd --></html>
results1.asp
<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include virtual="/Connections/CFS.asp" -->
<%
Dim Recordset1
Dim Recordset1_numRows
startdate = Cdate(Request.Form("firstinput"))
enddate = Cdate(Request.Form("secondinput"))
Set Recordset1 = Server.CreateObject("ADODB.Recordset")
Recordset1.ActiveConnection = MM_CFS_STRING
Recordset1.Source = "SELECT stock_no, date_exp_d, account, po_type, branch, descz FROM EMMG.RDL, EMMG.RDH WHERE (date_exp_d BETWEEN '"& startdate &"' AND '"& enddate &"') AND po_type = 'V' AND doc_status = '6' ORDER BY date_exp_d"
Recordset1.CursorType = 0
Recordset1.CursorLocation = 2
Recordset1.LockType = 1
Recordset1.Open()
Recordset1_numRows = 0
%>
<%
Dim Repeat1__numRows
Dim Repeat1__index
Repeat1__numRows = -1
Repeat1__index = 0
Recordset1_numRows = Recordset1_numRows + Repeat1__numRows
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>Untitled Document</title>
<link href="/styles.css" rel="stylesheet" type="text/css">
</head>
<body>
<table width="75%" >
<tr>
<th width="24%" scope="col">Date Expected </th>
<th width="21%" scope="col">Stock No </th>
<th width="21%" scope="col">Description</th>
<th width="29%" scope="col">Account</th>
<th width="2%" scope="col">Branch</th>
<th width="3%" scope="col"> </th>
</tr>
<%
While ((Repeat1__numRows <> 0) AND (NOT Recordset1.EOF))
%>
<tr>
<td><%=(Recordset1.Fields.Item("date_exp_d").Value)%></td>
<td><%=(Recordset1.Fields.Item("stock_no").Value)%></td>
<td><%=(Recordset1.Fields.Item("descz").Value)%></td>
<td><%=(Recordset1.Fields.Item("account").Value)%></td>
<td><%=(Recordset1.Fields.Item("branch").Value)%></td>
<td> </td>
</tr>
<%
Repeat1__index=Repeat1__index+1
Repeat1__numRows=Repeat1__numRows-1
Recordset1.MoveNext()
Wend
%>
</table>
</body>
</html>
<%
Recordset1.Close()
Set Recordset1 = Nothing
%>
Ok the problem is now, that the dates just don't work... I may put in 05\08\2005 - 25\08\2005 and get a heap of results starting at 22\08\2005 and going till December, then I put in 04\08\2005 and get nothing ?? It isn't the javascript is it ?, as I would have thought that is a quick way to put a date in the box.
Also it is displaying multible entries Not sure why , As I don't use the business system to know what data I'm pulling up.. But I think it is becuase these entries have been edited a number of times, and it is bringing up each version. Is there a way to let only the most current show ? Or let me know what info is needed and I will post it here.
Cheers all..
| |
|
|
|
BeTheBall
Posts: 6355
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Display Date Range - 8/24/2005 23:53:52
See if this thread provides any insight:
http://www.frontpagewebmaster.com/m-169971/key-dates%252Cformat/tm.htm#170353
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
baldcat
Posts: 41
Joined: 6/30/2005
Status: offline
|
RE: Display Date Range - 8/25/2005 0:19:56
Yeah I sort of understand that gear, but I have no idea on how to implement it..
I'm using mySQL, and the displayed date when I just call any results it displays as dd/mm/yyyy... How can I tell how it is being stored.
| |
|
|
|
BeTheBall
Posts: 6355
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Display Date Range - 8/25/2005 9:28:27
One of the points of the thread I referred you to is that it doesn't matter how the dates are being stored. If your query uses a format of say, 8 Aug 2005, it will be understood regardless of how the dates are stored. To test the theory, try hard coding some values into your current page and see if the results are what you expect, for example:
Recordset1.Source = "SELECT stock_no, date_exp_d, account, po_type, branch, descz FROM EMMG.RDL, EMMG.RDH WHERE (date_exp_d BETWEEN '4 Aug 2005' AND '25 Aug 2005') AND po_type = 'V' AND doc_status = '6' ORDER BY date_exp_d"
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
baldcat
Posts: 41
Joined: 6/30/2005
Status: offline
|
RE: Display Date Range - 8/25/2005 23:55:44
I really am at a loss........
I have tried the hard coded bit and it came up with 1080 records with most date selections I tried. And 0 for others. So I thought I'd have a go at Sallys
<% Dim myDay1
Dim myMonth1
Dim myYear1
Dim myDate1
myDay1 = Request.Form ("fromday")
myMonth1 = Request.Form ("frommonth")
myYear1 = Request.Form ("fromyear")
myDate1 = myDay1 & " " & myMonth1 & " " & myYear1 %>
<% Dim myDay2
Dim myMonth2
Dim myYear2
Dim myDate2
myDay2 = Request.Form ("today")
myMonth2 = Request.Form ("tomonth")
myYear2 = Request.Form ("toyear")
myDate2 = myDay2 & " " & myMonth2 & " " & myYear2 %>
<%
Dim Recordset1
Dim Recordset1_numRows
Set Recordset1 = Server.CreateObject("ADODB.Recordset")
Recordset1.ActiveConnection = MM_CFS_STRING
Recordset1.Source = "SELECT date_exp_d, stock_no, doc_status, po_type, branch FROM EMMG.RDL, EMMG.RDH WHERE po_type = 'V' AND doc_status = '6' AND (date_exp_d BETWEEN '"& myDate1 &"' AND '"& myDate2 &"')"
Recordset1.CursorType = 0
Recordset1.CursorLocation = 2
Recordset1.LockType = 1
Recordset1.Open()
Recordset1_numRows = 0
%>
Tis pretty untidy but it gives me the same results as the hard coded dates... So I assume that it is working... I have also noticed that the "date_exp_d" values do not display.. I take out the line (date_exp_d BETWEEN '"& myDate1 &"' AND '"& myDate2 &"') And the date_exp_d value displays..
| |
|
|
|
drewbkilla
Posts: 2
Joined: 9/21/2005
Status: offline
|
RE: Display Date Range - 9/21/2005 12:14:13
quote:
(date_exp_d BETWEEN '"& startdate &"' AND '"& enddate &"')
Hey guys, I stumbled upon this page and it has helped considerably.. BUT
for some reason, if I run a date range query, it seems to ignore the year..
EX: when I input from 1/1/2005 to 2/1/2005 I get results within that month but I get the year 2004 too,
(I dont have data further back but I'm sure it would have came up)
if it helps, I capture the dates when these records were filled out by using the NOW() ASP command, so the months arent 01/01/2005, it's 1/1/2005, but that shouldn't affect the year should it? Any ideas will be greatly appreciated.. : )
| |
|
|
|
BeTheBall
Posts: 6355
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Display Date Range - 9/21/2005 13:35:29
Welcome to OutFront. Can you post the query you are using to retrieve the records. Also, can you verify that the database field is indeed date/time date type?
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
drewbkilla
Posts: 2
Joined: 9/21/2005
Status: offline
|
RE: Display Date Range - 9/21/2005 15:13:03
lol right after I posted this, I went back into my SQL server and noticed that I DID NOT set the field to datetime.. duh.. I'm an ID10T : )
Anyways that SQL string helped alot.. and Thanks!
| |
|
 New Messages |
 No New Messages |
 Hot Topic w/ New Messages |
 Hot Topic w/o New Messages |
 Locked w/ New Messages |
 Locked w/o New Messages |
|
Post New Thread
Reply to Message
Post New Poll
Submit Vote
Delete My Own Post
Delete My Own Thread
Rate Posts
|
|
|
Printable Version 
k¥ 
). 
If you nagged the vendors is there a chance they would send you a copy of the structure at least? 