month & year only as current date for default (Full Version)

All Forums >> [Web Development] >> ASP and Database



Message

designzo -> 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=&amp;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 -> 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



designzo -> 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 -> 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)...



designzo -> 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 -> 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.



designzo -> 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 -> 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.




rdouglass -> 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?



designzo -> 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 -> 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).




designzo -> 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 -> 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 & "#"




designzo -> 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 -> 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 -> 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()) )



designzo -> RE: month & year only as current date for default (10/27/2007 23:00:06)

quote:

WHERE (PN = '::PN::' AND Month(Period) > "" & Month(Date()) & "" AND Year(Period)= "" & Year(Date()) )


Hi William

Perfect! Thanks very much for all of your help, could not have done this without your help, looking forward to a good nights sleep tonight!!! [:)]

If your in New Zealand say hi



designzo -> 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 -> 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()







designzo -> 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 -> 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 -> RE: month & year only as current date for default (11/4/2007 9:34:36)

Any updates? Pls post how you have solved your problem, thanks[:)]



Page: [1]

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
0.0625