Making ORDER BY parameterized

[TSmooth]

I'm using an Access database for a backend to an ASP.NET site and was wondering how to go about setting up and storing a parameterized query that uses a parameter to specify which field to ORDER BY. I would like the parameter to be a field name. I can't seem to get it to work properly and would appreciate an example. Below is one of the queries for which I would like to be able to do this by specifiying either Members.MemberName, DKPs, or LifetimeEarned in the ORDER BY clause.

On a similar note, if I have the following query:

SELECT Members.MemberName, Sum(DKPAudits.DKPAuditAdjPts) AS DKPs, Sum(IIf(DKPAudits.DKPAuditType=2,-DKPAudits.DKPAuditAdjPts,0)) AS PtsSpent, Sum(IIf(DKPAudits.DKPAuditType<>2,DKPAudits.DKPAuditAdjPts,0)) AS [Lifetime Earned], Count(IIf(DKPAudits.DKPAuditType=2,1,Null)) AS Epics
FROM Members INNER JOIN DKPAudits ON Members.MemberID=DKPAudits.DKPAuditMemberID
GROUP BY Members.MemberName
ORDER BY Sum(DKPAudits.DKPAuditAdjPts) DESC;

Why will it not accept "DKPs" in the ORDER BY clause when i already specify that Sum() AS DKPs above? Do I really need the same sum in the SELECT and ORDER BY clauses?

[Igor Soukhov]

Why will it not accept "DKPs" in the ORDER BY clause when i already specify that Sum() AS DKPs above? Do I really need the same sum in the SELECT and ORDER BY clauses?

I'm not sure why it doesn't work for you - but it should be. Actually, the ORDER BY clause is the only part of the query where you CAN use column aliases.

[columbus2003]

so surprise!!!
it might be the nature or the weakness of Access...
Insteads of repeating expression, just use column number such as "order by 2"

[Igor Soukhov]

so surprise!!!
it might be the nature or the weakness of Access...
Insteads of repeating expression, just use column number such as "order by 2"

"ORDER BY Column" number is a very bad practise. Person responsible for maintaining your code will curse you.

[columbus2003]

anyway, I hope you will agree it is better than repeating the expression.

[Igor Soukhov]

anyway, I hope you will agree it is better than repeating the expression.

I totally disagree with you. It's better to repeat the already known expression
than to use less clear syntax (BTW I've heard this syntax is deprecated).

[trends]

Hi,

For running the parameterized query you have to put the query in the query section of the MS Access.on the left side u can find the tables toolbox.below tables there is queries.go there and write the query as


SELECT Members.MemberName, Sum(DKPAudits.DKPAuditAdjPts) AS DKPs, Sum(IIf(DKPAudits.DKPAuditType=2,-DKPAudits.DKPAuditAdjPts,0)) AS PtsSpent, Sum(IIf(DKPAudits.DKPAuditType<>2,DKPAudits.DKPAuditAdjPts,0)) AS [Lifetime Earned], Count(IIf(DKPAudits.DKPAuditType=2,1,Null)) AS Epics
FROM Members INNER JOIN DKPAudits ON Members.MemberID=DKPAudits.DKPAuditMemberID
GROUP BY Members.MemberName
ORDER BY @variable;


save this query there.You have to call this query from asp.net with the input values for that.the input must be the necessary field name u want.such as members or sum(audit) something.

Try.All the best