This is odd. I'm working with MS SQL Server and ASP.NET VB. I'm using a subquery to return ONLY the second record record in this table.

<pre id=code><font face=courier size=2 id=code>
SELECT TOP 1 *
FROM
(
SELECT TOP 2 *
FROM Answer
WHERE Answer_QuestionID = @QID
ORDER BY AnswerSortOrder ASC
)
DERIVEDTBL ORDER BY AnswerSortOrder DESC
</font id=code></pre id=code>

As you can see, the inner query gets the TOP TWO records, then the outer query flips their ORDER (with DESC ) and returns the record remaining (never mind why I'm doing this, just keep in mind that it works). At least the SQL executes correctly in both MS SQL and on the Web page when it runs.

HOWEVER...

Dreamweaver is having a few problems with this. When I test the dataset (while looking at the code in the "Advanced" menu of the dataset designer), it runs perfectly. But, when I try to expand it in the Application/Bindings panel, it returns this error:

<b>Line 2: Incorrect syntax near '1'.</b>

The page still works, but it is definitely slowing me down because I have to hand code all the databindings from that dataset.

BTW: I've used this syntax before in Classic ASP pages, and it worked fine there.

Also, the <b>DERIVEDTBL </b> statement was generated by the MS SQL Query Builder.

Any ideas?

Hi Mike

This is valid SQL and your problems are likely due to the limitations of DW.

The DERIVEDTBL is a name for a virtual table created using the select statement, I just don't think DW can virtual.

You could bung this SQL in an stored procedure and your problems should go away.

Sharing Knowledge Saves Valuable Time!!!
~ ~ ~ ~ ~ ~
<b>Lee Diggins</b> - <i>DMXzone Manager</i>
<font size="1">[ Studio MX/MX2004 | ASP -> VBScript/PerlScript/JavaScript | SQL | CSS ]</font>