Frontpage Custom SQL query

[Blah]

Hi all
I have what I thought would be a simple query, but it's doing my head in.

I am using Frontpage 2002, and an Access 2000 database. This is a custom
query for displaying a certain years Lottery bonus ball winners, the year
being input on a separate page.

Consider this:

SELECT FORMAT(won,'dd mmm yyyy'), winner, amount, id
FROM bonus
WHERE (YEAR(won)) = :input:
ORDER BY won DESC

::input:: is an integer passed from a form on another page, and I'm lead to
believe that YEAR outputs an integer. If I substitute :input: for a
year, it works fine. But, no matter how much tweaking of syntax I do, I
just cannot get it to work with ::input::

Can anyone throw light on this ?
Thanks in advance . . .

Cheers
Alex

 

[Kevin Spencer]

If I understand you correctly, you're getting a variable passed from a
different page instead of from the same page. When FrontPage creates a DRW
page for you that takes input, it writes a whole bunch of code that allows
you to simply put in the name of the form field surrounded with "::"
characters, and does the translating for you. If the page that is sending
the form value wasn't created by the wizard, the coded ::input:: will mean
nothing to the code that FrontPage writes. Instead, you have to create your
SQL string by hand, that is, by inserting the value programmatically with
your own custom ASP code. Now, you didn't post the code, but took the SQL
statement out of the code, and I don't use the DRW, but in ASP, you would
create a string variable like the following:

Dim MyString
MyString = "SELECT FORMAT(won,'dd mmm yyyy'), winner, amount, id FROM bonus
WHERE (YEAR(won)) = " & Request("input") & " ORDER BY won DESC"

So, assuming you understand what my example means, you would want to use the
same basic technique. You are concatenating the string from the posted form
with the SQL string to create a complete SQL statement. This example also
assumes that the name of the form field that is being passed is "input."

--
HTH,

Kevin Spencer
Microsoft MVP
..Net Developer
Ambiguity has a certain quality to it.

 

[Ian]

I've had similar problems in the past where variables would not be passed.
I got around it by generating the SQL string outside of the database
connection using VB, then using a manual process to pull the data (not via
DRB). Only problem with this approach is that you get a list of items that
goes down the page and you will not be able to use the DRW controls.