FP2003 SQL ignoring values passed through hyperlink

Ian · Apr 24, 2005

I wonder if anyone can help with a minor issue I am having with FP2003 and
the DBRW. I have a DBR on an ASP page that takes a value generated in a
previous page and displays records based on the variables passed through a
hyperlink. The DBR uses the following code:

SELECT DISTINCTROW Dial.Date, Sum(Dial.Cost) AS [Sum Of Cost], Count(*) AS
[Count Of Dial] FROM Dial WHERE (((Format$([Dial].[Date],'mmmm
yyyy'))="::MONTHYEAR::")) GROUP BY Dial.Date,
Year([Dial].[Date])*12+DatePart('m',[Dial].[Date])-1;

The value for the variable MONTHYEAR is normally in the format: APRIL 2005.
When I hyperlink to the page the value is passed through, I have confirmed
that the MONTHYEAR field has been populated by displaying the value via a VB
script. The DBR returns the following error:

Database Results Wizard Error
Your page contains a query with user input parameters that could not be
resolved.
This could happen if your DatabaseRegionStart webbot has an empty or missing
s-columnnames or s-columntypes attributes.
You may need to read Microsoft Knowledge Base Article 817029.

I've followed the instructions in the above article but it does not seem to
make any difference and the DatabaseRegionStart looks OK:

<tr>
<td>
<%=FP_FieldVal(fp_rs,"Date")%></td>
<td>
<%=FP_FieldVal(fp_rs,"Sum_Of_Cost")%></td>
<td>
<%=FP_FieldVal(fp_rs,"Count_Of_Dial")%></td>
</tr>

</tbody>

The problem seems to be with the fact that the DBR ignores the
passed-through value and reports a blank value. I have tested the script in
Access and it works fine.....Any ideas?

Thanks

Ian

Ian · Apr 24, 2005

I've done a little bit more testing and changed from the DBRW to
hand-written code using. I am still unable to send a value through
::MONTHYEAR:: but if I replace this with a value, i.e. APRIL 2005 the code
works fine. I can confirm that MONTHYEAR does contain the correct value as
I use the value from MONTHYEAR in another part of the form and it displays
APRIL 2005 OK. Any body have any ideas? Also is there a way to send a VB
value through the SQL statement instead of the hyperlink veriable?

New code is:

<%Response.Buffer = True
Response.Expires = 0
Response.ExpiresAbsolute = now() -1
Response.CacheControl = "no-cache"
DSN = "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" &
("\\server$\Mileage.mdb") & ";"
Set OBJdbConnection = Server.CreateObject("ADODB.Connection")
OBJdbConnection.Open(DSN)
Set SQLStmt = Server.CreateObject("ADODB.Command")
Set RS = Server.CreateObject ("ADODB.Recordset")
SQLStmt.CommandText = " SELECT DISTINCTROW Dial.Date, Dial.Cost,
Dial.Mileage FROM Dial WHERE (((Format$([Dial].[Date],'mmmm
yyyy'))='::MONTHYEAR::')) GROUP BY Dial.Date, Dial.Cost, Dial.Mileage; "
SQLStmt.CommandType = 1
Set SQLStmt.ActiveConnection = OBJdbConnection
RS.Open SQLStmt
do while not RS.EOF %>

<tr>
<td><%=RS("Date")%> </td>
<td><%=RS("Cost")%> </td>
<td><%=RS("Mileage")%> </td>
</tr>
<%
RS.movenext
loop
OBJdbConnection.Close
Set OBJdbConnection = Nothing
%>

Ian said:
I wonder if anyone can help with a minor issue I am having with FP2003 and
the DBRW. I have a DBR on an ASP page that takes a value generated in a
previous page and displays records based on the variables passed through a
hyperlink. The DBR uses the following code:

SELECT DISTINCTROW Dial.Date, Sum(Dial.Cost) AS [Sum Of Cost], Count(*) AS
[Count Of Dial] FROM Dial WHERE (((Format$([Dial].[Date],'mmmm
yyyy'))="::MONTHYEAR::")) GROUP BY Dial.Date,
Year([Dial].[Date])*12+DatePart('m',[Dial].[Date])-1;

The value for the variable MONTHYEAR is normally in the format: APRIL
2005. When I hyperlink to the page the value is passed through, I have
confirmed that the MONTHYEAR field has been populated by displaying the
value via a VB script. The DBR returns the following error:

Database Results Wizard Error
Your page contains a query with user input parameters that could not be
resolved.
This could happen if your DatabaseRegionStart webbot has an empty or
missing s-columnnames or s-columntypes attributes.
You may need to read Microsoft Knowledge Base Article 817029.

I've followed the instructions in the above article but it does not seem
to make any difference and the DatabaseRegionStart looks OK:

<tr>
<td>
<%=FP_FieldVal(fp_rs,"Date")%></td>
<td>
<%=FP_FieldVal(fp_rs,"Sum_Of_Cost")%></td>
<td>
<%=FP_FieldVal(fp_rs,"Count_Of_Dial")%></td>
</tr>

</tbody>

The problem seems to be with the fact that the DBR ignores the
passed-through value and reports a blank value. I have tested the script
in Access and it works fine.....Any ideas?

Thanks

Ian

Ian · Apr 25, 2005

Resolved my issue, built the SQL string outside of the code and it works
great.... If anybody is interested.

Ian said:
I wonder if anyone can help with a minor issue I am having with FP2003 and
the DBRW. I have a DBR on an ASP page that takes a value generated in a
previous page and displays records based on the variables passed through a
hyperlink. The DBR uses the following code:

SELECT DISTINCTROW Dial.Date, Sum(Dial.Cost) AS [Sum Of Cost], Count(*) AS
[Count Of Dial] FROM Dial WHERE (((Format$([Dial].[Date],'mmmm
yyyy'))="::MONTHYEAR::")) GROUP BY Dial.Date,
Year([Dial].[Date])*12+DatePart('m',[Dial].[Date])-1;

The value for the variable MONTHYEAR is normally in the format: APRIL
2005. When I hyperlink to the page the value is passed through, I have
confirmed that the MONTHYEAR field has been populated by displaying the
value via a VB script. The DBR returns the following error:

Database Results Wizard Error
Your page contains a query with user input parameters that could not be
resolved.
This could happen if your DatabaseRegionStart webbot has an empty or
missing s-columnnames or s-columntypes attributes.
You may need to read Microsoft Knowledge Base Article 817029.

I've followed the instructions in the above article but it does not seem
to make any difference and the DatabaseRegionStart looks OK:

<tr>
<td>
<%=FP_FieldVal(fp_rs,"Date")%></td>
<td>
<%=FP_FieldVal(fp_rs,"Sum_Of_Cost")%></td>
<td>
<%=FP_FieldVal(fp_rs,"Count_Of_Dial")%></td>
</tr>

</tbody>

The problem seems to be with the fact that the DBR ignores the
passed-through value and reports a blank value. I have tested the script
in Access and it works fine.....Any ideas?

Thanks

Ian

I am trying to understand this code...	3	Jul 26, 2008
Database Results Wizard Error - FrontPage 2003	5	Mar 3, 2006
bullet list extra spacing	2	Jan 18, 2009
Database Results Error	2	Nov 24, 2004
any ideas why I can update one table in my database but not the other?	2	Sep 22, 2006
Recurring number on asp result page	0	Oct 17, 2003
Rounding off numbers in asp result page.	1	Oct 16, 2003
Clickable Hyperlinks in Database	12	Jun 18, 2007

FP2003 SQL ignoring values passed through hyperlink

Ian

Ian

Ian

Ask a Question

Similar Threads