I need some help with a SQL statement in an Active Server Page that works
fine until I try to order the results with the 'ORDER BY' clause.  The
statement that works is:

strSelect = "SELECT samples.SampleID, samples.title, samples.short_desc,
samples.subject, genre.genres FROM genre INNER JOIN samples ON genre.genreID
= samples.genrecode where genrecode =" & Request.QueryString( "subj" )

This statement produces a list of samples according to a designated genre
that is supplied in the string  '...action.asp?X', with X being a number
assigned to the genre and which corresponds to the 'genrecode' in the above
statement. When I try to order the results with:

strSelect = "SELECT samples.SampleID, samples.title, samples.short_desc,
samples.subject, genre.genres FROM genre INNER JOIN samples ON genre.genreID
= samples.genrecode where genrecode =" & Request.QueryString( "subj" ) &
"ORDER BY samples.title"

I'm informed that: Syntax error (missing operator) in query expression
'genrecode =xORDER BY samples.title'.

Any one who can suggest how I can append the 'ORDER BY' clause to my SQL
statement would be doing me a mondo favor!

Thanks, MH

WHERE genrecode =xORDER BY samples.title

isn't valid SQL. On the other hand:

WHERE genrecord = x ORDER BY samples.title

is valid SQL. Notice how there is a *space* between x and ORDER? Otherwise
the database thinks that ORDER is part of the criteria you want genrecord to
be equal to.

Cheers
Ken

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Michael Hanrahan" <mhanrahan@h...>
Subject: [access_asp] ORDER BY clause question


: I need some help with a SQL statement in an Active Server Page that works
: fine until I try to order the results with the 'ORDER BY' clause.  The
: statement that works is:
:
: strSelect = "SELECT samples.SampleID, samples.title, samples.short_desc,
: samples.subject, genre.genres FROM genre INNER JOIN samples ON
genre.genreID
: = samples.genrecode where genrecode =" & Request.QueryString( "subj" )
:
: This statement produces a list of samples according to a designated genre
: that is supplied in the string  '...action.asp?X', with X being a number
: assigned to the genre and which corresponds to the 'genrecode' in the
above
: statement. When I try to order the results with:
:
: strSelect = "SELECT samples.SampleID, samples.title, samples.short_desc,
: samples.subject, genre.genres FROM genre INNER JOIN samples ON
genre.genreID
: = samples.genrecode where genrecode =" & Request.QueryString( "subj" ) &
: "ORDER BY samples.title"
:
: I'm informed that: Syntax error (missing operator) in query expression
: 'genrecode =xORDER BY samples.title'.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Try add a space in front of ORDER BY.
Good Luck.
Ping Li

-----Original Message-----
From: Michael Hanrahan [mailto:mhanrahan@h...]
Sent: Sunday, April 21, 2002 12:29 PM
To: Access ASP
Subject: [access_asp] ORDER BY clause question


I need some help with a SQL statement in an Active Server Page that works
fine until I try to order the results with the 'ORDER BY' clause.  The
statement that works is:

strSelect = "SELECT samples.SampleID, samples.title, samples.short_desc,
samples.subject, genre.genres FROM genre INNER JOIN samples ON genre.genreID
= samples.genrecode where genrecode =" & Request.QueryString( "subj" )

This statement produces a list of samples according to a designated genre
that is supplied in the string  '...action.asp?X', with X being a number
assigned to the genre and which corresponds to the 'genrecode' in the above
statement. When I try to order the results with:

strSelect = "SELECT samples.SampleID, samples.title, samples.short_desc,
samples.subject, genre.genres FROM genre INNER JOIN samples ON genre.genreID
= samples.genrecode where genrecode =" & Request.QueryString( "subj" ) &
"ORDER BY samples.title"

I'm informed that: Syntax error (missing operator) in query expression
'genrecode =xORDER BY samples.title'.

Any one who can suggest how I can append the 'ORDER BY' clause to my SQL
statement would be doing me a mondo favor!

Thanks, MH