Passing string to asp page to be placed as ORDER BY column in SELECT

[Varmit]

I am trying to do what I know is a very simple thing. Unfamiliar with ASP / Select statement variable syntax, I can't figure out how to:

call the page view.asp?sortcol=company_name (string)

and put the proper place holder into the web bot comment seciton declaring the sql=SELECT statement to take the string variable

Tried:
s-sql="SELECT * FROM company_table ORDER BY ::sortcol::"
s-sql="SELECT * FROM company_table ORDER BY sortcol
s-sql="SELECT * FROM company_table ORDER BY 'sortcol' "
s-sql="SELECT * FROM company_table ORDER BY &sortcol& "
s-sql="SELECT * FROM company_table ORDER BY ????????"

What is the syntax that will place this passed variable into the query statement as the order by value ???????

 

[motte]

dim sortbyvar
sortbyvar=request.querystring("sortcol&quot

sql="select * form company_table order by '" & =sortbyvar & "'"

...ok, not sure if you need the = sign at =sortbyvar...in fact, i think you don't.

that should give you the sql statement

mike

 

[Varmit]

Thank you . It looks like the insert area is
' " & val & " '

where there are single quotes surrounding double quotes.

When using your syntax as follows:
s-criteria= s-order s-sql="SELECT * FROM co_table ORDER BY '" & sortval & "' "

The following error ocurred:
Syntax error in string in query expression '''

When pasting your example into the <!--webbot databaseregionstart... --> section that contains the s-sql= declaration, the & was replaced with &amp;

Tried the following syntax
ORDER BY '&quot; & sortval & &quot;' &quot;
ORDER BY '&quot; & =sortval & &quot;' &quot;
ORDER BY '&quot; &amp; sortval &amp; &quot;' &quot;
ORDER BY '&quot; &amp; =sortval &amp; &quot;' &quot;
**With varying errors... Any clues ?

 

[motte]

yeah, i had single double & var & double single double...i guess it is hard to see. i'm not sure what error you are getting...since i am just looking at it and not playing around, i can't give you a perfect answer...but i bet its your variables...can't have a hypen in them. s-criteria should be s_criteria...change all variables hyphens to underscores.

mike

 

[Varmit]

I have given up for now. Just a note: the s-criteria and s-sql=&quot; are Front page generated and appear to be &quot;declarations&quot; for the asp code. If you try to modify the code itself, FrontPage will overwrite your changes based on the information in the comment

<!--webbot bot=&quot;DatabaseRegionStart&quot; s-columnnames=&quot;cand_ID ...&quot; s-criteria s-order s-sql=&quot;SELECT * FROM cand_table ORDER BY cand_curempl_title&quot; b-procedure=&quot;FALSE&quot; clientside suggestedext=&quot;asp&quot; s-defaultfields s-norecordsfound=&quot;No records returned.&quot; i-maxrecords=&quot;0&quot; i-groupsize=&quot;0&quot; botid=&quot;0&quot; ... -->

Thank you for your efforts. When I figure it out I'll post the solution...

 

[pgferro]

The column name shouldn't be within quotes..

Correct syntax :
sql=&quot;select * form company_table order by &quot; & sortbyvar

Bye

 

[motte]

That's right, my bad. It was an order by...sure, you don't need to put the value in single quotes. Sorry, I was getting ahead of myself, thinking of the 'where' clause. Sorry for any confusion varmit. I guess that happens when I rarely write sql statements, opting for use of the ADO model.

Mike

 

[Varmit]

Thanks for the input. Unfortunately this is a FrontPage generated view.asp. &quot;Request&quot; is not defined.

When passing an ID to a &quot;detial.asp?co_ID=4&quot; page the parameter is referenced as ::co_ID:: in the

<!--webot.. s-criteria=&quot;{co_ID} EQ {co_ID} +&quot; s-order
s-sql=&quot;SELECT * FROM co_table WHERE co_ID = ::co_ID::&quot; --> area as ::co_ID::

WHICH results in the code itself being written identically

<%
fp_sQry=&quot;SELECT * FROM co_table WHERE co_ID = ::co_ID::&quot;
... %>

This works fine for a WHERE clause & passed value is value of data in a field but not as the text that is supposed to be the &quot;ORDER BY colname&quot;.

with no variable set up and &quot;Request&quot;. What am I missing ?

P.S Thanks for the time and effort

 

[lahatte]

Try this, and do away with the frontpage stuff:

co_ID = request.querystring(&quot;co_ID&quot

s-sql = &quot;SELECT * FROM co_table ORDER BY &quot; & co_ID

You could also append the sort order to the end if you wish (DESC or ASC):

s-sql = &quot;SELECT * FROM co_table ORDER BY &quot; & co_ID & &quot;DESC&quot;

Clay.

 

[lahatte]

A mistake, you will need a space in the last part...

s-sql = &quot;SELECT * FROM co_table ORDER BY &quot; & co_ID & &quot; DESC&quot;

Clay.