|
| |
|
|
designzo
Posts: 16
Joined: 10/10/2007
Status: offline
 |
month & year only as current date for default - 10/24/2007 2:53:02
We have built a database and have used frontpage 2002 database wizard plus custom SQL on our .asp pages, the below html results
Question - instead of having to update the defalt period s-defaultfields="PN=&Period=October 2007" in the many pages that access the database results, is it possible instead to use a computors current date BUT using month and year only? Any help would be greatly appreciated thank you
PART ONE of html - editable to create PART TWO
<!--webbot bot="DatabaseRegionStart" s-columnnames="Ident,PN,Prop,Book,Label,Period" s-columntypes="3,3,202,202,202,135" s-dataconnection="Database1" b-tableformat="TRUE" b-menuformat="FALSE" s-menuchoice="Ident" s-menuvalue="Ident" b-tableborder="TRUE" b-tableexpand="FALSE" b-tableheader="TRUE" b-listlabels="TRUE" b-listseparator="TRUE" i-listformat="0" b-makeform="FALSE" s-recordsource s-displaycolumns="Ident,PN,Prop,Book,Label,Period" s-criteria s-order s-sql="SELECT Host.Ident, Host.PN, Host.Prop, Host.Book, Availability.Label, Availability.Period<br>FROM Availability INNER JOIN Host ON Availability.Source = Host.Ident<br>WHERE (PN = '::PN::' AND Period = '::Period::')" b-procedure="FALSE" clientside suggestedext="asp" s-defaultfields="PN=&Period=October 2007" s-norecordsfound="No records returned." i-maxrecords="1" i-groupsize="0" botid="0" u-dblib="../_fpclass/fpdblib.inc" u-dbrgn1="../_fpclass/fpdbrgn1.inc" u-dbrgn2="../_fpclass/fpdbrgn2.inc" tag="TBODY" preview="<tr><td colspan=64 bgcolor="#FFFF00" align="left" width="100%"><font color="#000000">This is the start of a Database Results region. The page must be fetched from a web server with a web browser to display correctly; the current web is stored on your local disk or network.</font></td></tr>" startspan b-WasTableFormat="TRUE" --><!--#include file="../_fpclass/fpdblib.inc"-->
PART TWO
<% if 0 then %>
<SCRIPT Language="JavaScript">
document.write("<div style='background: yellow; color: black;'>The Database Results component on this page is unable to display database content. The page must have a filename ending in '.asp', and the web must be hosted on a server that supports Active Server Pages.</div>");
</SCRIPT>
<% end if %>
<%
fp_sQry="SELECT Host.Ident, Host.PN, Host.Prop, Host.Book, Availability.Label, Availability.Period FROM Availability INNER JOIN Host ON Availability.Source = Host.Ident WHERE (PN = '::PN::' AND Period = '::Period::')"
fp_sDefault="PN=&Period=October 2007"
fp_sNoRecords="<tr><td colspan=6 align=left width=""100%"">No records returned.</td></tr>"
fp_sDataConn="Database1"
fp_iMaxRecords=1
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice="Ident"
fp_sMenuValue="Ident"
fp_sColTypes="&Ident=3&PN=3&Prop=202&Book=202&Label=202&Period=135&"
fp_iDisplayCols=6
fp_fCustomQuery=True
BOTID=0
fp_iRegion=BOTID
%>
|
|
|
|
William Lee
Posts: 1060
Joined: 1/25/2002
From: Singapore
Status: offline
|
RE: month & year only as current date for default - 10/24/2007 3:22:19
Edited: Use the MonthName and Year function.
Try:
fp_sQry="SELECT Host.Ident, Host.PN, Host.Prop, Host.Book, Availability.Label, Availability.Period FROM Availability INNER JOIN Host ON Availability.Source = Host.Ident WHERE (PN = '::PN::' AND Period = '" & MonthName(Month(Date)) & " " & Year(Date) & "')"
Month(Date) gives 10
MonthName(10) gives October
Year(Date) gives 2007
< Message edited by William Lee -- 10/24/2007 4:17:10 >
_____________________________
William Lee
| |
|
|
|
designzo
Posts: 16
Joined: 10/10/2007
Status: offline
|
RE: month & year only as current date for default - 10/25/2007 14:04:39
Hi William, thank you for your advice, have tried to do this and it really looks as if it should work and no problems with verifying the SQL but when we publish and try and get a result we do not get one. Don't know enough to take an educated guess why but possibly their is a conflict in the format of date? Our database table we are seeking a result from hase the date as 10/1/2007 (for October) and we have used October 2007 as the default in the Frontpage results wizard
any advice on whether we should be changing our table would be appreciated although the server is returning a result for this, OR is it possible to change the way the advice you have given us is presented to the server in the SQL?
Would greatly appreciate your advice, this is the last building block in what has been a steep and rewarding learning curve!
| |
|
|
|
rdouglass
Posts: 9202
From: Biddeford, ME USA
Status: offline
|
RE: month & year only as current date for default - 10/25/2007 14:55:45
quote:
...MonthName(Month(Date)) & " " & Year(Date)...
Try it this way:
...Month(Date) & "/01/" & Year(Date)...
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
designzo
Posts: 16
Joined: 10/10/2007
Status: offline
|
RE: month & year only as current date for default - 10/25/2007 15:24:49
quote:
...Month(Date) & "/01/" & Year(Date)...
Thank you, have tried this as you suggested to get
WHERE (PN = '::PN::' AND Period = '" & MonthName(Month(Date) & "/01/" & Year(Date) & "')
Again no problem in verifying but still no result returned, did think that maybe my computer date setting (we are New zealand so day/month/year used here) may have been the problem so tried your solution as
WHERE (PN = '::PN::' AND Period = '" & "/01/" & MonthName(Month(Date) & Year(Date) & "')
Again verified but no result
Again thank you - we will succeed!
| |
|
|
|
rdouglass
Posts: 9202
From: Biddeford, ME USA
Status: offline
|
RE: month & year only as current date for default - 10/25/2007 16:42:15
quote:
WHERE (PN = '::PN::' AND Period = '" & MonthName(Month(Date) & "/01/" & Year(Date) & "')
Were you sure *not* to use MonthName? Also try using more parens like this:
...WHERE (PN = '::PN::') AND (Period = '" & Month(Date) & "/01/" & Year(Date) & "')
If this is SQL Server and PN is text and Period is a date/time field, I would use that query.
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
designzo
Posts: 16
Joined: 10/10/2007
Status: offline
|
RE: month & year only as current date for default - 10/25/2007 17:02:53
Many thanks, yes did not use the Monthname
Still no luck and need to talk to our service provider, have a suspicion our wenb hosting package only includes basic database functionality through .asp but may not support SQL functionality for calculating
| |
|
|
|
William Lee
Posts: 1060
Joined: 1/25/2002
From: Singapore
Status: offline
|
RE: month & year only as current date for default - 10/25/2007 22:58:59
quote:
ORIGINAL: designzo
quote:
...Month(Date) & "/01/" & Year(Date)...
Thank you, have tried this as you suggested to get
WHERE (PN = '::PN::' AND Period = '" & MonthName(Month(Date) & "/01/" & Year(Date) & "')
Again no problem in verifying but still no result returned, did think that maybe my computer date setting (we are New zealand so day/month/year used here) may have been the problem so tried your solution as
WHERE (PN = '::PN::' AND Period = '" & "/01/" & MonthName(Month(Date) & Year(Date) & "')
Again verified but no result
Again thank you - we will succeed!
Try the more than(>) or less than(<) operator instead of eqaul to (=). This will surely return some results.
If no results are returned, check the value of Period that is passed. Use <%=Request("Period")%> at the top of the asp page.
There might be some trailing hr:min:s as is expected of Time stamped. You will need to strip that away, perhaps using DatePart function.
< Message edited by William Lee -- 10/26/2007 2:33:42 >
_____________________________
William Lee
| |
|
|
|
rdouglass
Posts: 9202
From: Biddeford, ME USA
Status: offline
|
RE: month & year only as current date for default - 10/26/2007 0:03:30
quote:
basic database functionality through .asp but may not support SQL functionality for calculating
I don't understand that. Either you have SQL Server or you dont.
What is your database platform?
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
designzo
Posts: 16
Joined: 10/10/2007
Status: offline
|
RE: month & year only as current date for default - 10/26/2007 19:17:16
That is the problem, we don't know enough to know that we don't know
We are using access 2003 for building the database and then using frontpage 2002 for the Database interface using the wizard but putting an inner join query in.
Everything works just fine
Our original server is Windows 2000 and we now have MS SQL 2000 (W2K) Database Support
We spoke to our hosting service who advised us that the hosting package we have does not support SQL so we changed yesterday to a hosting package temporarily that they said did
to cut a long story short we can still not get a result from eith server (we have everything loaded on two of websites now for testing)
When we put <%=Request("Period")%> on the top of the page we get nothing
When we put <%Response.write Date()%> in the same place the server returns the date as 10/26/2007
All the best
| |
|
|
|
William Lee
Posts: 1060
Joined: 1/25/2002
From: Singapore
Status: offline
|
RE: month & year only as current date for default - 10/27/2007 0:01:45
quote:
ORIGINAL: designzo
When we put <%=Request("Period")%> on the top of the page we get nothing
If you do not have a preceding form that passes "Period" to this page that processes the SQL statement, then you will get nothing from <%=Request("Period")%>. This is expected result.
If there is a form posting to this page and you're not getting the "Period" passed, then you should check the form.
If you loading this page without a preceding form, then I would think your default values for PN and Period needs to be investigated. Currently your default for PN has no value, right? Check your table for the values and test the asp page with these values, ie hardcoding the values into the fp_sQry for PN and Period. Exampe " ...WHERE (PN=1) AND (Period='10/1/2007')"
Try testing with this sql statement which applies the Month function to Period as below:
"SELECT Host.Ident, Host.PN, Host.Prop, Host.Book, Availability.Label, Availability.Period
FROM
Availability INNER JOIN Host ON Availability.Source = Host.Ident
WHERE Month(Availability.Period)=10"
When using DRW you must observe the syntax like if PN field is numeric, you should use PN=::PN:: (without the single quotes).
< Message edited by William Lee -- 10/27/2007 0:10:32 >
_____________________________
William Lee
| |
|
|
|
designzo
Posts: 16
Joined: 10/10/2007
Status: offline
|
RE: month & year only as current date for default - 10/27/2007 1:33:03
Thank you for this, the following do return a result
SELECT Host.Ident, Host.PN, Host.Prop, Host.Book, Availability.Label, Availability.Period
FROM Availability INNER JOIN Host ON Availability.Source = Host.Ident
WHERE Month(Availability.Period)=10
SELECT Host.Ident, Host.PN, Host.Prop, Host.Book, Availability.Label, Availability.Period
FROM
Availability INNER JOIN Host ON Availability.Source = Host.Ident
WHERE (Period=#10/1/2007#)
I think that maybe what I was trying to do is not possible, I was working on the misunderstanding that if I put a formula in the SQL query the server would put a value from current date and thaen process this to give me a result.
It seems from your comment that all I can do is to provide something in my SQL to then receive information from a form then take this to the server to get the result
Have I got this right?
All the best and thank you for sticking with this
| |
|
|
|
William Lee
Posts: 1060
Joined: 1/25/2002
From: Singapore
Status: offline
|
RE: month & year only as current date for default - 10/27/2007 5:48:33
quote:
ORIGINAL: designzo
Thank you for this, the following do return a result
SELECT Host.Ident, Host.PN, Host.Prop, Host.Book, Availability.Label, Availability.Period
FROM Availability INNER JOIN Host ON Availability.Source = Host.Ident
WHERE Month(Availability.Period)=10
SELECT Host.Ident, Host.PN, Host.Prop, Host.Book, Availability.Label, Availability.Period
FROM
Availability INNER JOIN Host ON Availability.Source = Host.Ident
WHERE (Period=#10/1/2007#)
Go ahead and try with the current date:
"SELECT Host.Ident, Host.PN, Host.Prop, Host.Book, Availability.Label, Availability.Period
FROM Availability INNER JOIN Host ON Availability.Source = Host.Ident
WHERE Month(Availability.Period)=" & Month(Date)
or
"SELECT Host.Ident, Host.PN, Host.Prop, Host.Book, Availability.Label, Availability.Period
FROM
Availability INNER JOIN Host ON Availability.Source = Host.Ident
WHERE Period=#" & Date & "#"
_____________________________
William Lee
| |
|
|
|
designzo
Posts: 16
Joined: 10/10/2007
Status: offline
|
RE: month & year only as current date for default - 10/27/2007 15:00:06
Thank you William
Tried both of these suggestions thanks, following resulted
1. WHERE Month(Availability.Period)=" & Month(Date)
Syntax error in string in query expression 'Month(Availability.Period)=" & Month(Date)'
2. WHERE Period=#" & Date & "#"
Syntax error in string in query expression 'Period=#[ & Date & ]#"'
Changed to WHERE Month(Availability.Period)="" & Month(Date)
Verified but no results returned on either server
Will try some other combinations but pure guesswork here
To recap our database with each record representing 1 month, the period field in the record contains a short date ie 10/1/2007
So the 10/1/2007 is what we are using to get equal to vales and greater than values in
http://www.designzontravel.co.nz/Database1_interface/restempS.asp?PN=1240
So possible there is no result as we are not providing inputs for Month Day Year ?
Thank you
Russell
| |
|
|
|
designzo
Posts: 16
Joined: 10/10/2007
Status: offline
|
RE: month & year only as current date for default - 10/27/2007 22:20:13
Hi William
Now have a result for current date using
WHERE (PN = '::PN::' AND Month(Period)="" & Month(Date()) & "" AND Year(Period)= "" & Year(Date()) )
Now trying to work out how to get greater than current month
Many thanks for all your help
| |
|
|
|
William Lee
Posts: 1060
Joined: 1/25/2002
From: Singapore
Status: offline
|
RE: month & year only as current date for default - 10/27/2007 22:23:37
quote:
ORIGINAL: designzo
Hi William
Now have a result for current date using
WHERE (PN = '::PN::' AND Month(Period)="" & Month(Date()) & "" AND Year(Period)= "" & Year(Date()) )
Now trying to work out how to get greater than current month
Use the > operator
WHERE (PN = '::PN::' AND Month(Period) > "" & Month(Date()) & "" AND Year(Period)= "" & Year(Date()) )
_____________________________
William Lee
| |
|
|
|
designzo
Posts: 16
Joined: 10/10/2007
Status: offline
|
RE: month & year only as current date for default - 10/29/2007 13:19:29
With a great deal of help using current server date as query date has now been achieved and we can get results. However we really need to use the current date as our defalt date instead of inserting a current date into the Frontpage DBRW reason is we are setting up lots of pages rather than one page for editing by individual clients
Following are our field values and queries, our objective is to access results or editor and have the default (current month) show up as the default result then be able to search for different months
Fields are
PN - a numeric value that is put into the query by either hyperlink or default in Frontpage Database Wizard
Period is a month represented by for example 10/1/2007 being October 1 2007
Current date SQL is either (equal) Month(Period)="" & Month(Date()) & "" AND Year(Period)= "" & Year(Date()) (or greater than) Month(Period) > "" & Month(Date()) & "" AND Year(Period)= "" & Year(Date())
Queries are
(PN = ::PN:: AND Period = '::Period::')
(PN = ::PN:: AND (Period = '::Period::' OR Period > '::Period::'))
(Period > '::Period::' AND PN = '::PN::')
(Period = '::Period::' AND PN = '::PN::')
Any help in showing us how to include the current month as a default in these queries would be greatly appreciated thank you, we have tried several ways but get either weird or no result.
| |
|
|
|
William Lee
Posts: 1060
Joined: 1/25/2002
From: Singapore
Status: offline
|
RE: month & year only as current date for default - 10/29/2007 21:13:06
quote:
ORIGINAL: designzo
Queries are
(PN = ::PN:: AND Period = '::Period::')
(PN = ::PN:: AND (Period = '::Period::' OR Period > '::Period::'))
(Period > '::Period::' AND PN = '::PN::')
(Period = '::Period::' AND PN = '::PN::')
Any help in showing us how to include the current month as a default in these queries would be greatly appreciated thank you, we have tried several ways but get either weird or no result.
I understand you to mean you do not want to modify the numerous custom query in all your DRW results region in the manner that was shown to you and which works.
In this case, set your Defaults for Period with this :
Date()
< Message edited by William Lee -- 10/29/2007 21:19:09 >
_____________________________
William Lee
| |
|
|
|
designzo
Posts: 16
Joined: 10/10/2007
Status: offline
|
RE: month & year only as current date for default - 10/30/2007 0:00:58
I think I need to use the month year current date that worked because my search by record is based on each record month "Period" having month start date value ie 10/1/2007, my thinking (and tell me if I am wrong) is that if I tried to use Date() my field value does not work because even if I get a result for the current month period I can not get a greater than month ie the next month as if the date is say 10/15/2007 both current month and next month would match. Hope this makes sense
So what I need to try and do, I think, is to load the - Month(Period)="" & Month(Date()) & "" AND Year(Period)= "" & Year(Date()) - that worked, into my query as a default value to stop no results be returned when the hyperlinked .asp?PN= XXXX goes to the database results page
Question is how do you put a Default value into a query, tried to help myself but no luck!
I really hope this makes sense
| |
|
|
|
William Lee
Posts: 1060
Joined: 1/25/2002
From: Singapore
Status: offline
|
RE: month & year only as current date for default - 10/30/2007 5:46:22
Try variations of this:
(PN=::PN:: AND Month(Period)='::Month(Period)::' AND Year(Period)='::Year(Period)::')
Again, if you do not want to return empty results when the page is first called, eg through the hyperlinked.asp?PN=XXXX, set your default for Period to Date()
So, if DRW executes the query and there is no Period passed as parameter, it will look in the webbot code for values inside fp_sDefault, which is Date().
Hope this helps.
_____________________________
William Lee
| |
|
 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 
