Date query ASP page fails in IIS6

David Berry · Oct 27, 2006

Before the <HTML> tag put:

<%
meetingdate = trim(Request("meetingdate"))
%>

PaulR said:
Hi David,
Have replaced the code you suggested and the problem is still there.

Paul

David Berry said:

Ok. I just stripped out all the database wizard code and checked the
value
being returned by the calendar. Your script is returning DD/MM/YYYY.
You
need to change the Sub Calendar1_Click() to get rid of the lines that are
converting it to a UK date so it appears as MM/DD/YYYY. Replace what you
have with this:

<SCRIPT LANGUAGE=vbscript>

</SCRIPT>

Now, when you click a calendar value, you'll see that the QueryString is
returning the date in the correct format.

PaulR said:

David,
many thanks for your interest.
Your clarifications are correct.

The query is from the embedded Access file that the asp page accesses.
(in
an Access database called masonic)
If the issue is one of passing data to the sql, it is a fault that also
occurs when you use FP only code. (I have tried removing all my code
and
just
using the FP wizard, same problem)
Remember this code does work on earlier versions of IIS.

Paul

The page is here:
http://www.derbyshiremason.org/newpglroot/masonicConcepts/calendar.asp

and this is the entire HTML as FP sees it.

<html>

<head>
<% ' FP_ASP -- ASP Automatically generated by a FrontPage Component. Do
not
Edit.
FP_LCID = 2057 %>
<meta http-equiv="Content-Language" content="en-gb">
<% ' FP_ASP -- ASP Automatically generated by a FrontPage Component. Do
not
Edit.
FP_CharSet = "windows-1252"
FP_CodePage = 1252 %>
<meta http-equiv="Content-Type" content="text/html;
charset=windows-1252">
<title>Calendar</title>
<meta name="GENERATOR" content="Microsoft FrontPage 6.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
<meta name="Microsoft Theme" content="pgl 0011, default">
<meta name="Microsoft Border" content="tlrb, default">
</head>

<body>

<SCRIPT LANGUAGE=vbscript>

</SCRIPT>

<div align="center">

<table border="0" cellspacing="1" style="border-collapse: collapse;
text-align: center" width="74%">
<tr>
<td width="115%"><i><b><font color="#800000"
size="2">Help:</font></b></i><font color="#000080" size="2"><br>
Choose the month, then the year, then
the date to find Derbyshire lodges meeting at that time.</font></td>
<td width="109%">
<object classid="clsid:8E27C92B-1264-101C-8A2F-040224009C02"
id="Calendar1"
width="361" height="161">
<param name="_Version" value="524288">
<param name="_ExtentX" value="7641">
<param name="_ExtentY" value="3408">
<param name="_StockProps" value="1">
<param name="BackColor" value="-2147483633">
<param name="Year" value=year(now())>
<param name="Month" value=month(now())>
<param name="Day" value=day(now())>
<param name="DayLength" value="1">
<param name="MonthLength" value="1">
<param name="DayFontColor" value="0">
<param name="FirstDay" value="7">
<param name="GridCellEffect" value="1">
<param name="GridFontColor" value="10485760">
<param name="GridLinesColor" value="-2147483632">
<param name="ShowDateSelectors" value="-1">
<param name="ShowDays" value="-1">
<param name="ShowHorizontalGrid" value="-1">
<param name="ShowTitle" value="-1">
<param name="ShowVerticalGrid" value="-1">
<param name="TitleFontColor" value="10485760">
<param name="ValueIsNull" value="0">
</object>
</td>
</tr>
</table><table width="100%" border="1">
<thead>
<tr>
<th ALIGN="LEFT"><b>LodgeName</b></th>
<th ALIGN="LEFT"><b>LNo</b></th>
<th ALIGN="LEFT"><b>Meetingdate</b></th>
<th ALIGN="LEFT"><b>meetingtype</b></th>
<th ALIGN="LEFT"><b>lodgetype</b></th>
</tr>
</thead>
<tbody>

<% 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 * FROM meetings WHERE (Meetingdate =
'::Meetingdate::')
ORDER BY LNo ASC"
fp_sDefault="Meetingdate=10/10/07"
fp_sNoRecords="<tr><td colspan=5 align=""LEFT"" width=""100%"">Choose
meeting date from calendar</td></tr>"
fp_sDataConn="masonic"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_sColTypes="&LodgeName=202&LNo=3&lodgetype=202&meetingtype=202&Expr1=135&Meetingdate=135&month=3&Expr2=2&Expr3=2&LodgeID=3&hallid=3&Expr4=135&Expr5=135&Expr6=135&"
fp_iDisplayCols=5
fp_fCustomQuery=False
BOTID=0
fp_iRegion=BOTID
%>

<tr>
<td>
<%=FP_FieldVal(fp_rs,"LodgeName")%></td>
<td>
<%=FP_FieldVal(fp_rs,"LNo")%></td>
<td>
<%=FP_FieldVal(fp_rs,"Meetingdate")%></td>
<td>
<%=FP_FieldVal(fp_rs,"meetingtype")%></td>
<td>
<%=FP_FieldVal(fp_rs,"lodgetype")%></td>
</tr>

</tbody>
</table>
</div>

<br>

</body>

</html>

:

Paul, just to clarify, when you're trying to return the results on
your
page
you said the SQL Statement was:

SELECT * FROM meetings WHERE (Meetingdate = #::Meetingdate::#) ORDER
BY
LNo
ASC

Is Meetingdate the field that holds the date information? What is the
Access query below used for (how are you using it on your page)?

When a user picks a date on the calendar, how is that being done
(what's
the
code). For example, they pick a date, it has a value and when clicked
that
value is being stored in a variable called "Meetingdate" that is then
used
in the SQL Statement above right?

Can you show us all the code for the page? I'm thinking there's an
issue
between what's getting picked from the calendar and what's passed to
the
SQL
Statement.

David,
I have tried with and without the setlocal statement and have set to
US
and
GB. All give the same result.
If I use the FP generated search form, I get the same problem. i.e.
I
only
use FP generated code, none of my own.

Meetingdate is a field in the database. You can also see this field
displayed on the form.

The date is derived from this expression within an access query:
Expr1:
DateSerial(Year(Now()),[month],meetingdate!offset+(Lodges!weekinmonth)*7+Lodges!dayofweek-Weekday(DateSerial(Year(Now()),[month],1)-1)-IIf(Weekday(DateSerial(Year(Now()),[month],1)-1)>[dayofweek],0,7))

Paul

:

In your code you have:

'set the locale to UK English
Dim meetingdate

Click to expand...

Click to expand...

Kathleen Anderson [MVP - FrontPage] · Oct 27, 2006

I was referring to the CaSe of the two places where the fieldname was
referenced.

--

~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
web: http://www.spiderwebwoman.com/resources/

David Berry said:
It shouldn't be but you're right, you should try to name variables
differently. Like dtmeetingdate as the variable name.

Kathleen Anderson said:

I'm wondering if naming it Meetingdate in your database table, but
referring to it as meetingdate in your script might be a problem?

--

~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
web: http://www.spiderwebwoman.com/resources/

PaulR said:

David,
many thanks for your interest.
Your clarifications are correct.

The query is from the embedded Access file that the asp page accesses.
(in
an Access database called masonic)
If the issue is one of passing data to the sql, it is a fault that also
occurs when you use FP only code. (I have tried removing all my code and
just
using the FP wizard, same problem)
Remember this code does work on earlier versions of IIS.

Paul

The page is here:
http://www.derbyshiremason.org/newpglroot/masonicConcepts/calendar.asp

and this is the entire HTML as FP sees it.

<html>

<head>
<% ' FP_ASP -- ASP Automatically generated by a FrontPage Component. Do
not
Edit.
FP_LCID = 2057 %>
<meta http-equiv="Content-Language" content="en-gb">
<% ' FP_ASP -- ASP Automatically generated by a FrontPage Component. Do
not
Edit.
FP_CharSet = "windows-1252"
FP_CodePage = 1252 %>
<meta http-equiv="Content-Type" content="text/html;
charset=windows-1252">
<title>Calendar</title>
<meta name="GENERATOR" content="Microsoft FrontPage 6.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
<meta name="Microsoft Theme" content="pgl 0011, default">
<meta name="Microsoft Border" content="tlrb, default">
</head>

<body>

<SCRIPT LANGUAGE=vbscript>

</SCRIPT>

<div align="center">

<table border="0" cellspacing="1" style="border-collapse: collapse;
text-align: center" width="74%">
<tr>
<td width="115%"><i><b><font color="#800000"
size="2">Help:</font></b></i><font color="#000080" size="2"><br>
Choose the month, then the year, then
the date to find Derbyshire lodges meeting at that time.</font></td>
<td width="109%">
<object classid="clsid:8E27C92B-1264-101C-8A2F-040224009C02"
id="Calendar1"
width="361" height="161">
<param name="_Version" value="524288">
<param name="_ExtentX" value="7641">
<param name="_ExtentY" value="3408">
<param name="_StockProps" value="1">
<param name="BackColor" value="-2147483633">
<param name="Year" value=year(now())>
<param name="Month" value=month(now())>
<param name="Day" value=day(now())>
<param name="DayLength" value="1">
<param name="MonthLength" value="1">
<param name="DayFontColor" value="0">
<param name="FirstDay" value="7">
<param name="GridCellEffect" value="1">
<param name="GridFontColor" value="10485760">
<param name="GridLinesColor" value="-2147483632">
<param name="ShowDateSelectors" value="-1">
<param name="ShowDays" value="-1">
<param name="ShowHorizontalGrid" value="-1">
<param name="ShowTitle" value="-1">
<param name="ShowVerticalGrid" value="-1">
<param name="TitleFontColor" value="10485760">
<param name="ValueIsNull" value="0">
</object>
</td>
</tr>
</table><table width="100%" border="1">
<thead>
<tr>
<th ALIGN="LEFT"><b>LodgeName</b></th>
<th ALIGN="LEFT"><b>LNo</b></th>
<th ALIGN="LEFT"><b>Meetingdate</b></th>
<th ALIGN="LEFT"><b>meetingtype</b></th>
<th ALIGN="LEFT"><b>lodgetype</b></th>
</tr>
</thead>
<tbody>

<% 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 * FROM meetings WHERE (Meetingdate = '::Meetingdate::')
ORDER BY LNo ASC"
fp_sDefault="Meetingdate=10/10/07"
fp_sNoRecords="<tr><td colspan=5 align=""LEFT"" width=""100%"">Choose
meeting date from calendar</td></tr>"
fp_sDataConn="masonic"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_sColTypes="&LodgeName=202&LNo=3&lodgetype=202&meetingtype=202&Expr1=135&Meetingdate=135&month=3&Expr2=2&Expr3=2&LodgeID=3&hallid=3&Expr4=135&Expr5=135&Expr6=135&"
fp_iDisplayCols=5
fp_fCustomQuery=False
BOTID=0
fp_iRegion=BOTID
%>

<tr>
<td>
<%=FP_FieldVal(fp_rs,"LodgeName")%></td>
<td>
<%=FP_FieldVal(fp_rs,"LNo")%></td>
<td>
<%=FP_FieldVal(fp_rs,"Meetingdate")%></td>
<td>
<%=FP_FieldVal(fp_rs,"meetingtype")%></td>
<td>
<%=FP_FieldVal(fp_rs,"lodgetype")%></td>
</tr>

</tbody>
</table>
</div>

<br>

</body>

</html>

:

Paul, just to clarify, when you're trying to return the results on your
page
you said the SQL Statement was:

SELECT * FROM meetings WHERE (Meetingdate = #::Meetingdate::#) ORDER BY
LNo
ASC

Is Meetingdate the field that holds the date information? What is the
Access query below used for (how are you using it on your page)?

When a user picks a date on the calendar, how is that being done
(what's the
code). For example, they pick a date, it has a value and when clicked
that
value is being stored in a variable called "Meetingdate" that is then
used
in the SQL Statement above right?

Can you show us all the code for the page? I'm thinking there's an
issue
between what's getting picked from the calendar and what's passed to
the SQL
Statement.

David,
I have tried with and without the setlocal statement and have set to
US
and
GB. All give the same result.
If I use the FP generated search form, I get the same problem. i.e. I
only
use FP generated code, none of my own.

Meetingdate is a field in the database. You can also see this field
displayed on the form.

The date is derived from this expression within an access query:
Expr1:
DateSerial(Year(Now()),[month],meetingdate!offset+(Lodges!weekinmonth)*7+Lodges!dayofweek-Weekday(DateSerial(Year(Now()),[month],1)-1)-IIf(Weekday(DateSerial(Year(Now()),[month],1)-1)>[dayofweek],0,7))

Paul

:

In your code you have:

'set the locale to UK English
Dim meetingdate
Dim original
original = setlocale("en-gb")

Are you trying to use UK date formats? if not remove this

Is meetingdate a date/time field in your database?

Hi Kathleen,
The problem is still present. i.e. the query does not return most
dates.
Debug says there is not a problem.
The site did work on an earlier version of IIS.
It looks like an issue with IIS6, but I do not know a work around.
Can you help?

Regards
Paul

:

So if you get no error, does that means that it's working?

--

~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
web: http://www.spiderwebwoman.com/resources/

With debug and # in place of' i get
Database Results Wizard Error
Unable to find operator in query string. Query string currently
is
SELECT
*
FROM meetings WHERE (Meetingdate = #::Meetingdate::#) ORDER BY
LNo
ASC

with the ' I get no error.
Paul

:

One thing I see is that you have a field in your table called
'month',
which
is a Microsoft Jet 4.0 reserved word
http://support.microsoft.com/?id=321266 Even though you're
not
using
it
in
your SQL, that may still be a problem.

Here's something else you can try: open the hidden folder
/_fpclass/
and
edit the fpdbrgn1.inc file

At about line 19, change :

fp_DEBUG = False

to :

fp_DEBUG = True

and then preview the page in your browser.

--

~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
web: http://www.spiderwebwoman.com/resources/

Kathleen,
I get an error if I do this.
both in developer time:
"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. The custom query
contains
errors."
and in run time
"Database Results Wizard Error
The operation failed. If this continues, please contact your
server
administrator."
Paul

:

In Code View, make the change to the gray code and save the
change
while
still in code view. The change will propagate to the maroon
code.

--

~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
web: http://www.spiderwebwoman.com/resources/

Kathleen, thanks for the suggestion.
FrontPage just changes the code back again!!
Is there an update to the webbot that cures the issue?

Regards
Paul

:

Try WHERE (Meetingdate = #::Meetingdate::#)

see:
http://support.microsoft.com/default.aspx?scid=kb;en-us;284843&Product=fp2002

--

~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
web: http://www.spiderwebwoman.com/resources/

message
I thought it was a locale issue at first, but if you
translate
American
dates
they do not always work.
for example 1/9/07 and 9/1/07 should return info.

In answer to Davids Question, the scripts are below.
I should point out that the fault is there even is you
use
just
the
FP
webbots. (i.e. I have eliminated my VBscript code)

Paul

The VBscript is

FP then uses this webbot


With thes parameters.
<%
fp_sQry="SELECT * FROM meetings WHERE (Meetingdate =
'::Meetingdate::')
ORDER BY LNo ASC"
fp_sDefault="Meetingdate=10/10/07"
fp_sNoRecords="<tr><td colspan=5 align=""LEFT""
width=""100%"">Choose
meeting date from calendar</td></tr>"
fp_sDataConn="masonic"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_sColTypes="&LodgeName=202&LNo=3&lodgetype=202&meetingtype=202&Expr1=135&Meetingdate=135&month=3&Expr2=2&Expr3=2&LodgeID=3&hallid=3&Expr4=135&Expr5=135&Expr6=135&"
fp_iDisplayCols=5
fp_fCustomQuery=False
BOTID=0
fp_iRegion=BOTID
%>

</SCRIPT>

"David Berry" wrote:

Click to expand...

Click to expand...

PaulR · Oct 27, 2006

David,

Have tried your suggestion but the problems is still there.
Paul

the first few lines are now:
<%
meetingdate = trim(Request("meetingdate"))
%>
<html>

<head>
<% ' FP_ASP -- ASP Automatically generated by a Frontpage Component. Do not
Edit.
FP_LCID = 2057 %>

and the locale statement is removed.

BTW, did you mean to send the same info twice? of is there code missing?

David Berry said:
PS - I was doing a little more investigating and even though the QueryString
is bring back the date it doesn't look like your code ever picks up the
value of the QueryString so it's always putting a blank in the Select
statement. Try this:

At the very top of your page (before the <HTML> tag, put:

<%
meetingdate = trim(Request("meetingdate"))
%>

Now try it. You'll need to make the other change I mentioned as well

David Berry said:

Ok. I just stripped out all the database wizard code and checked the
value being returned by the calendar. Your script is returning
DD/MM/YYYY. You need to change the Sub Calendar1_Click() to get rid of
the lines that are converting it to a UK date so it appears as MM/DD/YYYY.
Replace what you have with this:

<SCRIPT LANGUAGE=vbscript>

</SCRIPT>

Now, when you click a calendar value, you'll see that the QueryString is
returning the date in the correct format.

PaulR said:

David,
many thanks for your interest.
Your clarifications are correct.

The query is from the embedded Access file that the asp page accesses.
(in
an Access database called masonic)
If the issue is one of passing data to the sql, it is a fault that also
occurs when you use FP only code. (I have tried removing all my code and
just
using the FP wizard, same problem)
Remember this code does work on earlier versions of IIS.

Paul

The page is here:
http://www.derbyshiremason.org/newpglroot/masonicConcepts/calendar.asp

and this is the entire HTML as FP sees it.

<html>

<head>
<% ' FP_ASP -- ASP Automatically generated by a FrontPage Component. Do
not
Edit.
FP_LCID = 2057 %>
<meta http-equiv="Content-Language" content="en-gb">
<% ' FP_ASP -- ASP Automatically generated by a FrontPage Component. Do
not
Edit.
FP_CharSet = "windows-1252"
FP_CodePage = 1252 %>
<meta http-equiv="Content-Type" content="text/html;
charset=windows-1252">
<title>Calendar</title>
<meta name="GENERATOR" content="Microsoft FrontPage 6.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
<meta name="Microsoft Theme" content="pgl 0011, default">
<meta name="Microsoft Border" content="tlrb, default">
</head>

<body>

<SCRIPT LANGUAGE=vbscript>

</SCRIPT>

<div align="center">

<table border="0" cellspacing="1" style="border-collapse: collapse;
text-align: center" width="74%">
<tr>
<td width="115%"><i><b><font color="#800000"
size="2">Help:</font></b></i><font color="#000080" size="2"><br>
Choose the month, then the year, then
the date to find Derbyshire lodges meeting at that time.</font></td>
<td width="109%">
<object classid="clsid:8E27C92B-1264-101C-8A2F-040224009C02"
id="Calendar1"
width="361" height="161">
<param name="_Version" value="524288">
<param name="_ExtentX" value="7641">
<param name="_ExtentY" value="3408">
<param name="_StockProps" value="1">
<param name="BackColor" value="-2147483633">
<param name="Year" value=year(now())>
<param name="Month" value=month(now())>
<param name="Day" value=day(now())>
<param name="DayLength" value="1">
<param name="MonthLength" value="1">
<param name="DayFontColor" value="0">
<param name="FirstDay" value="7">
<param name="GridCellEffect" value="1">
<param name="GridFontColor" value="10485760">
<param name="GridLinesColor" value="-2147483632">
<param name="ShowDateSelectors" value="-1">
<param name="ShowDays" value="-1">
<param name="ShowHorizontalGrid" value="-1">
<param name="ShowTitle" value="-1">
<param name="ShowVerticalGrid" value="-1">
<param name="TitleFontColor" value="10485760">
<param name="ValueIsNull" value="0">
</object>
</td>
</tr>
</table><table width="100%" border="1">
<thead>
<tr>
<th ALIGN="LEFT"><b>LodgeName</b></th>
<th ALIGN="LEFT"><b>LNo</b></th>
<th ALIGN="LEFT"><b>Meetingdate</b></th>
<th ALIGN="LEFT"><b>meetingtype</b></th>
<th ALIGN="LEFT"><b>lodgetype</b></th>
</tr>
</thead>
<tbody>

<% 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 * FROM meetings WHERE (Meetingdate = '::Meetingdate::')
ORDER BY LNo ASC"
fp_sDefault="Meetingdate=10/10/07"
fp_sNoRecords="<tr><td colspan=5 align=""LEFT"" width=""100%"">Choose
meeting date from calendar</td></tr>"
fp_sDataConn="masonic"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_sColTypes="&LodgeName=202&LNo=3&lodgetype=202&meetingtype=202&Expr1=135&Meetingdate=135&month=3&Expr2=2&Expr3=2&LodgeID=3&hallid=3&Expr4=135&Expr5=135&Expr6=135&"
fp_iDisplayCols=5
fp_fCustomQuery=False
BOTID=0
fp_iRegion=BOTID
%>

<tr>
<td>
<%=FP_FieldVal(fp_rs,"LodgeName")%></td>
<td>
<%=FP_FieldVal(fp_rs,"LNo")%></td>
<td>
<%=FP_FieldVal(fp_rs,"Meetingdate")%></td>
<td>
<%=FP_FieldVal(fp_rs,"meetingtype")%></td>
<td>
<%=FP_FieldVal(fp_rs,"lodgetype")%></td>
</tr>

</tbody>
</table>
</div>

<br>

</body>

</html>

:

Paul, just to clarify, when you're trying to return the results on your
page
you said the SQL Statement was:

SELECT * FROM meetings WHERE (Meetingdate = #::Meetingdate::#) ORDER BY
LNo
ASC

Is Meetingdate the field that holds the date information? What is the
Access query below used for (how are you using it on your page)?

When a user picks a date on the calendar, how is that being done (what's
the
code). For example, they pick a date, it has a value and when clicked
that
value is being stored in a variable called "Meetingdate" that is then
used
in the SQL Statement above right?

Can you show us all the code for the page? I'm thinking there's an
issue
between what's getting picked from the calendar and what's passed to the
SQL
Statement.

David,
I have tried with and without the setlocal statement and have set to
US

Click to expand...

Click to expand...

David Berry · Oct 27, 2006

I know. That's what I meant.

Kathleen Anderson said:
I was referring to the CaSe of the two places where the fieldname was
referenced.

--

~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
web: http://www.spiderwebwoman.com/resources/

David Berry said:

It shouldn't be but you're right, you should try to name variables
differently. Like dtmeetingdate as the variable name.

Kathleen Anderson said:

I'm wondering if naming it Meetingdate in your database table, but
referring to it as meetingdate in your script might be a problem?

--

~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
web: http://www.spiderwebwoman.com/resources/

David,
many thanks for your interest.
Your clarifications are correct.

The query is from the embedded Access file that the asp page accesses.
(in
an Access database called masonic)
If the issue is one of passing data to the sql, it is a fault that also
occurs when you use FP only code. (I have tried removing all my code
and just
using the FP wizard, same problem)
Remember this code does work on earlier versions of IIS.

Paul

The page is here:
http://www.derbyshiremason.org/newpglroot/masonicConcepts/calendar.asp

and this is the entire HTML as FP sees it.

<html>

<head>
<% ' FP_ASP -- ASP Automatically generated by a FrontPage Component. Do
not
Edit.
FP_LCID = 2057 %>
<meta http-equiv="Content-Language" content="en-gb">
<% ' FP_ASP -- ASP Automatically generated by a FrontPage Component. Do
not
Edit.
FP_CharSet = "windows-1252"
FP_CodePage = 1252 %>
<meta http-equiv="Content-Type" content="text/html;
charset=windows-1252">
<title>Calendar</title>
<meta name="GENERATOR" content="Microsoft FrontPage 6.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
<meta name="Microsoft Theme" content="pgl 0011, default">
<meta name="Microsoft Border" content="tlrb, default">
</head>

<body>

<SCRIPT LANGUAGE=vbscript>

</SCRIPT>

<div align="center">

<table border="0" cellspacing="1" style="border-collapse: collapse;
text-align: center" width="74%">
<tr>
<td width="115%"><i><b><font color="#800000"
size="2">Help:</font></b></i><font color="#000080" size="2"><br>
Choose the month, then the year, then
the date to find Derbyshire lodges meeting at that time.</font></td>
<td width="109%">
<object classid="clsid:8E27C92B-1264-101C-8A2F-040224009C02"
id="Calendar1"
width="361" height="161">
<param name="_Version" value="524288">
<param name="_ExtentX" value="7641">
<param name="_ExtentY" value="3408">
<param name="_StockProps" value="1">
<param name="BackColor" value="-2147483633">
<param name="Year" value=year(now())>
<param name="Month" value=month(now())>
<param name="Day" value=day(now())>
<param name="DayLength" value="1">
<param name="MonthLength" value="1">
<param name="DayFontColor" value="0">
<param name="FirstDay" value="7">
<param name="GridCellEffect" value="1">
<param name="GridFontColor" value="10485760">
<param name="GridLinesColor" value="-2147483632">
<param name="ShowDateSelectors" value="-1">
<param name="ShowDays" value="-1">
<param name="ShowHorizontalGrid" value="-1">
<param name="ShowTitle" value="-1">
<param name="ShowVerticalGrid" value="-1">
<param name="TitleFontColor" value="10485760">
<param name="ValueIsNull" value="0">
</object>
</td>
</tr>
</table><table width="100%" border="1">
<thead>
<tr>
<th ALIGN="LEFT"><b>LodgeName</b></th>
<th ALIGN="LEFT"><b>LNo</b></th>
<th ALIGN="LEFT"><b>Meetingdate</b></th>
<th ALIGN="LEFT"><b>meetingtype</b></th>
<th ALIGN="LEFT"><b>lodgetype</b></th>
</tr>
</thead>
<tbody>

<% 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 * FROM meetings WHERE (Meetingdate =
'::Meetingdate::')
ORDER BY LNo ASC"
fp_sDefault="Meetingdate=10/10/07"
fp_sNoRecords="<tr><td colspan=5 align=""LEFT"" width=""100%"">Choose
meeting date from calendar</td></tr>"
fp_sDataConn="masonic"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_sColTypes="&LodgeName=202&LNo=3&lodgetype=202&meetingtype=202&Expr1=135&Meetingdate=135&month=3&Expr2=2&Expr3=2&LodgeID=3&hallid=3&Expr4=135&Expr5=135&Expr6=135&"
fp_iDisplayCols=5
fp_fCustomQuery=False
BOTID=0
fp_iRegion=BOTID
%>

<tr>
<td>
<%=FP_FieldVal(fp_rs,"LodgeName")%></td>
<td>
<%=FP_FieldVal(fp_rs,"LNo")%></td>
<td>
<%=FP_FieldVal(fp_rs,"Meetingdate")%></td>
<td>
<%=FP_FieldVal(fp_rs,"meetingtype")%></td>
<td>
<%=FP_FieldVal(fp_rs,"lodgetype")%></td>
</tr>

</tbody>
</table>
</div>

<br>

</body>

</html>

:

Paul, just to clarify, when you're trying to return the results on
your page
you said the SQL Statement was:

SELECT * FROM meetings WHERE (Meetingdate = #::Meetingdate::#) ORDER
BY LNo
ASC

Is Meetingdate the field that holds the date information? What is the
Access query below used for (how are you using it on your page)?

When a user picks a date on the calendar, how is that being done
(what's the
code). For example, they pick a date, it has a value and when clicked
that
value is being stored in a variable called "Meetingdate" that is then
used
in the SQL Statement above right?

Can you show us all the code for the page? I'm thinking there's an
issue
between what's getting picked from the calendar and what's passed to
the SQL
Statement.

David,
I have tried with and without the setlocal statement and have set to
US
and
GB. All give the same result.
If I use the FP generated search form, I get the same problem. i.e.
I only
use FP generated code, none of my own.

Meetingdate is a field in the database. You can also see this field
displayed on the form.

The date is derived from this expression within an access query:
Expr1:
DateSerial(Year(Now()),[month],meetingdate!offset+(Lodges!weekinmonth)*7+Lodges!dayofweek-Weekday(DateSerial(Year(Now()),[month],1)-1)-IIf(Weekday(DateSerial(Year(Now()),[month],1)-1)>[dayofweek],0,7))

Paul

:

In your code you have:

'set the locale to UK English
Dim meetingdate
Dim original
original = setlocale("en-gb")

Are you trying to use UK date formats? if not remove this

Is meetingdate a date/time field in your database?

Hi Kathleen,
The problem is still present. i.e. the query does not return most
dates.
Debug says there is not a problem.
The site did work on an earlier version of IIS.
It looks like an issue with IIS6, but I do not know a work
around.
Can you help?

Regards
Paul

:

So if you get no error, does that means that it's working?

--

~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
web: http://www.spiderwebwoman.com/resources/

With debug and # in place of' i get
Database Results Wizard Error
Unable to find operator in query string. Query string
currently is
SELECT
*
FROM meetings WHERE (Meetingdate = #::Meetingdate::#) ORDER BY
LNo
ASC

with the ' I get no error.
Paul

:

One thing I see is that you have a field in your table called
'month',
which
is a Microsoft Jet 4.0 reserved word
http://support.microsoft.com/?id=321266 Even though you're
not
using
it
in
your SQL, that may still be a problem.

Here's something else you can try: open the hidden folder
/_fpclass/
and
edit the fpdbrgn1.inc file

At about line 19, change :

fp_DEBUG = False

to :

fp_DEBUG = True

and then preview the page in your browser.

--

~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
web: http://www.spiderwebwoman.com/resources/

Kathleen,
I get an error if I do this.
both in developer time:
"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. The custom query
contains
errors."
and in run time
"Database Results Wizard Error
The operation failed. If this continues, please contact
your
server
administrator."
Paul

:

In Code View, make the change to the gray code and save
the
change
while
still in code view. The change will propagate to the
maroon
code.

--

~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
web: http://www.spiderwebwoman.com/resources/

Kathleen, thanks for the suggestion.
FrontPage just changes the code back again!!
Is there an update to the webbot that cures the issue?

Regards
Paul

:

Try WHERE (Meetingdate = #::Meetingdate::#)

see:
http://support.microsoft.com/default.aspx?scid=kb;en-us;284843&Product=fp2002

--

~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
web: http://www.spiderwebwoman.com/resources/

message
I thought it was a locale issue at first, but if you
translate
American
dates
they do not always work.
for example 1/9/07 and 9/1/07 should return info.

In answer to Davids Question, the scripts are below.
I should point out that the fault is there even is
you use
just
the
FP
webbots. (i.e. I have eliminated my VBscript code)

Paul

The VBscript is

FP then uses this webbot


With thes parameters.
<%
fp_sQry="SELECT * FROM meetings WHERE (Meetingdate =
'::Meetingdate::')
ORDER BY LNo ASC"
fp_sDefault="Meetingdate=10/10/07"
fp_sNoRecords="<tr><td colspan=5 align=""LEFT""
width=""100%"">Choose
meeting date from calendar</td></tr>"
fp_sDataConn="masonic"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_sColTypes="&LodgeName=202&LNo=3&lodgetype=202&meetingtype=202&Expr1=135&Meetingdate=135&month=3&Expr2=2&Expr3=2&LodgeID=3&hallid=3&Expr4=135&Expr5=135&Expr6=135&"
fp_iDisplayCols=5
fp_fCustomQuery=False
BOTID=0
fp_iRegion=BOTID
%>

</SCRIPT>

:

Click to expand...

Click to expand...

David Berry · Oct 27, 2006

Ok. Let's try something different. I want to see if the problem is with
the SELECT statement or just something with the Database Wizard code so
let's see what happens with straight ASP code.

Create a new page called "calendar2.asp", switch to code view and paste in
all the code below. Then try running it and see if it works. (If your DSN
connection isn't called "masonic" then change that line to what it should
be)

------------------


<%@ Language=VBScript %>

<%
if isnull(trim(Request("meetingdate"))) or trim(Request("meetingdate")) = ""
then
dtMeetingDate = "10/27/2007"
else
dtMeetingDate = trim(Request("meetingdate"))
end if

Set adoCN = server.CreateObject("ADODB.connection")
Set adoRS = server.CreateObject("ADODB.recordset")
adoCN.Open "DSN=masonic;"
adoRS.ActiveConnection = adoCN
adoRS.CursorLocation = adUseClient

strSQL = "SELECT * FROM meetings WHERE Meetingdate = #" & dtMeetingDate & "#
"
strSQL = strSQL & "ORDER BY LNo ASC "
adors.Open strSQL

if adors.bof = false and adors.EOF = false then
strLodgeName = adors("LodgeName") & vbnullstring
strLNo = adors("LNo") & vbnullstring
strMeetingdate = adors("Meetingdate") & vbnullstring
strmeetingtype = adors("meetingtype") & vbnullstring
strlodgetype = adors("lodgetype") & vbnullstring
else
strLodgeName = vbnullstring
strLNo = vbnullstring
strMeetingdate = vbnullstring
strmeetingtype = vbnullstring
strlodgetype = vbnullstring
end if

adors.close

%>
<html>

<head>
<meta http-equiv="Content-Language" content="en-gb">

<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>Calendar</title>
<meta name="GENERATOR" content="Microsoft FrontPage 6.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
<meta name="Microsoft Theme" content="pgl 0011, default">
<meta name="Microsoft Border" content="tlrb, default">
</head>

<body>

<SCRIPT LANGUAGE=vbscript>

</SCRIPT>

<div align="center">

<table border="0" cellspacing="1" style="border-collapse: collapse;
text-align: center" width="74%">
<tr>
<td width="115%"><i><b><font color="#800000"
size="2">Help:</font></b></i><font color="#000080" size="2"><br>
Choose the month, then the year, then
the date to find Derbyshire lodges meeting at that time.</font></td>
<td width="109%">
<object classid="clsid:8E27C92B-1264-101C-8A2F-040224009C02" id="Calendar1"
width="361" height="161">
<param name="_Version" value="524288">
<param name="_ExtentX" value="9551">
<param name="_ExtentY" value="4260">
<param name="_StockProps" value="1">
<param name="BackColor" value="-2147483633">
<param name="Year" value=2006>
<param name="Month" value=10>
<param name="Day" value=27>
<param name="DayLength" value="1">
<param name="MonthLength" value="1">
<param name="DayFontColor" value="0">
<param name="FirstDay" value="7">
<param name="GridCellEffect" value="1">
<param name="GridFontColor" value="10485760">
<param name="GridLinesColor" value="-2147483632">
<param name="ShowDateSelectors" value="-1">
<param name="ShowDays" value="-1">
<param name="ShowHorizontalGrid" value="-1">
<param name="ShowTitle" value="-1">
<param name="ShowVerticalGrid" value="-1">
<param name="TitleFontColor" value="10485760">
<param name="ValueIsNull" value="0">
</object>
</td>
</tr>
</table><table width="100%" border="1">
<thead>
<tr>
<th ALIGN="LEFT"><b>LodgeName</b></th>
<th ALIGN="LEFT"><b>LNo</b></th>
<th ALIGN="LEFT"><b>Meetingdate</b></th>
<th ALIGN="LEFT"><b>meetingtype</b></th>
<th ALIGN="LEFT"><b>lodgetype</b></th>
</tr>
</thead>
<tbody>
<tr>
<td><%=strLodgeName%></td>
<td><%=strLNo%></td>
<td><%=strMeetingdate%></td>
<td><%=strmeetingtype%></td>
<td><%=strlodgetype%></td>
</tr>
</tbody>
</table>
</div>

<br>

</body>

</html>

PaulR · Oct 27, 2006

David,
set up as you suggested in.
http://www.derbyshiremason.org/newpglroot/masonicConcepts/calendar2.asp

It now returns a server error.
Paul

David Berry · Oct 27, 2006

Sorry Paul, hazard of posting code into a news group post. The lines
wrapped. Just change the line that says:

if isnull(trim(Request("meetingdate"))) or trim(Request("meetingdate")) = ""
then
dtMeetingDate = "10/27/2007"
else
dtMeetingDate = trim(Request("meetingdate"))
end if

to move the "then" up a line. It should say

if isnull(trim(Request("meetingdate"))) or trim(Request("meetingdate")) = ""
then
dtMeetingDate = "10/27/2007"
else
dtMeetingDate = trim(Request("meetingdate"))
end if

David Berry · Oct 27, 2006

SIGH ... it wrapped again. The "then" should be on the same line as the IF
statement. IF ........ Then

I also noticed that the last " in the SQL statement dropped down a line.
You'll need to hit backspace and move that up too.

Dave

David Berry · Oct 28, 2006

Looks like you're still getting an error on the datasource name. try:

strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Server.MapPath("/fpdb/web.mdb")

I know you'd rather use the database wizard but if you can get this code
working it will help us figure out if the problem is with the SELECT
statement or the database wizard code. If straight ASP code works then the
problem is with the Database Wizard code and then you can start debugging
that.

Date Query SASP page fails in IIS6, continued	0	Oct 27, 2006
Security Privileges Moving Visio ASP App to Win2K3 Running IIS6	0	Feb 26, 2007
Date Query help required	8	Nov 2, 2007
Date/Time won't show items on first day in query	1	Oct 1, 2008
Page displaying in design view, but layout view shows blank screen	4	May 5, 2010
Browser-based access to my Access DB	8	Jan 15, 2008
Sum.if date is in an interval...	6	Nov 11, 2007
Access query for same parameter in multiple columns	2	Aug 18, 2004

Date query ASP page fails in IIS6

David Berry

Kathleen Anderson [MVP - FrontPage]

PaulR

David Berry

David Berry

PaulR

David Berry

David Berry

David Berry

Ask a Question

Similar Threads