Dynamic ORDER BY

Question

User483349636 posted

Hello!

I'm building a website in Visual Studio 2008. I have a MS Access database and want to create a query in the Data Access Layer where the ORDER BY column is given by a parameter. I have tried to use CASE, but that does not work:

SELECT     Id, Rubrik, Rubriktext, Typ, Parenter, Klass, Visas, O1, O2, O3, O4, O5
FROM         Sidor
WHERE     (Parenter LIKE ?)
ORDER BY 
CASE WHEN ? = 1 THEN O1
        WHEN ? = 2 THEN O2
END

What is wrong? Thanks in advance.
        

Answer 1

User-1618234021 posted

Hi

Case When can not be used in Access. You can use IIF in Access. See the following link:

http://msdn.microsoft.com/en-us/library/ms181765.aspx

Answer 2

User483349636 posted

Hi

Case When can not be used in Access. You can use IIF in Access. See the following link:

http://msdn.microsoft.com/en-us/library/ms181765.aspx

Thanks a lot. However I don't really know how to write a query where the ORDER BY column is given by a parameter. I would like to choose to sort by 6 different columns. Is it possible?

Answer 3

User-1199946673 posted

Case When can not be used in Access. You can use IIF in Access

 

Instead of IIF, you can also use the Switch function:

http://www.techonthenet.com/access/functions/advanced/switch.php

I don't really know how to write a query where the ORDER BY column is given by a parameter.

 

You cannot directly do that (parameters cannot be used in the ORDER BY clause), but there's a trick:

SELECT * FROM (SELECT *, SWITCH(@ORDER_BY='field1',[field1],@ORDER_BY='field2',[field2],@ORDER_BY='field3',[field3],@ORDER_BY='field4',[field4],@ORDER_BY='field5',[field5],@ORDER_BY='field6',[field6]) AS order_by FROM [tablename] ) ORDER BY order_by

Answer 4

User483349636 posted

I get the following error message in the Query Editor when I try to execute such a query:

Error in list of function arguments: '@' not recognized.
Unable to parse query text.

The form @parameter_name doesn't work in other cases either, therefore my WHERE-clause with parameter looks as follows:

WHERE     (Parent LIKE ?) 

The form with question mark and no parameter name works, but not with @. I'm new to this type of programming, but I hope to find a solution and learn something at the same time!

Answer 5

User-1199946673 posted

I get the following error message in the Query Editor

 

The Visual Studio / VWD query editor doesn't work well with Access, for instance it doesn't recognize named parameters as you discovered. So you need t create the SQL statement without the editor.

Show the SQL you want to order, and tell which fields you want to use as parameters?

Answer 6

User483349636 posted

Show the SQL you want to order, and tell which fields you want to use as parameters?

OK, I tried to modify the code generated behind instead, I quote a part of the code from the file "/App_code/DataSet.xsd" inside the tag <sources>:

<DbSource ConnectionRef="dataConnectionString (Web.config)" DbObjectName="" DbObjectType="Unknown" GenerateMethods="Get" GenerateShortCommands="true" GeneratorGetMethodName="HämtaSidtabell" GeneratorSourceName="FillBy" GetMethodModifier="Public" GetMethodName="HämtaSidtabell" QueryType="Rowset" ScalarCallRetval="System.Object, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" UseOptimisticConcurrency="true" UserGetMethodName="HämtaSidtabell" UserSourceName="HämtaSidtabell">
                <SelectCommand>
                  <DbCommand CommandType="Text" ModifiedByUser="true">
                    <CommandText>SELECT     *
FROM         (SELECT     *, SWITCH(@ORDER_BY = 1, O1, @ORDER_BY = 2, O2, @ORDER_BY = 3, O3, @ORDER_BY = 4,
                                              O4, @ORDER_BY = 5, O5, @ORDER_BY = 0, Rubrik) AS order_by
                       FROM          Sidor)
                       WHERE Parenter LIKE @PARENT
ORDER BY order_by</CommandText>
                    <Parameters>
                      <Parameter AllowDbNull="false" AutogeneratedName="" DataSourceName="" DbType="String" Direction="Input" ParameterName="PARENT" Precision="0" ProviderType="Integer" Scale="0" Size="0" SourceColumn="Id" SourceColumnNullMapping="false" SourceVersion="Original" />
                      <Parameter AllowDbNull="false" AutogeneratedName="" DataSourceName="" DbType="Int32" Direction="Input" ParameterName="ORDER_BY" Precision="0" ProviderType="Integer" Scale="0" Size="0" SourceColumn="Id" SourceColumnNullMapping="false" SourceVersion="Original" />
                    </Parameters>
                  </DbCommand>
                </SelectCommand>
              </DbSource>

Answer 7

User-1199946673 posted

Remember that OleDb parameters are recognized by Position, not by name, which means that you should add them in the parameter collection in the same order they (first) appear in the query, in this case first @order_by, then @parent, instead of the other way around!

Also, you're using the like operator. When you don't use a Wildcard character (in OleDB %) this has the same effect as the = operator, so maybe you should change the WHERE clause:

.... WHERE Parenter LIKE @PARENT+ '%' ....

or

.... WHERE Parenter LIKE '%' + @PARENT + '%' ....

Answer 8

User483349636 posted

Thank you so much! After these small changes the code now works and I have learnt some new things. I'll be back if I encounter any new problems with this code.

Answer 9

User483349636 posted

A small problem seems to be present. The columns O1 to O5 contains ordering numbers as integers, but they are now ordered as if they were strings, i.e. for example 1, 10, 11, 2, 3, 4, 5, 6, 7, 8, 9 instead of 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11. Could this be a problem in the xsd-file? This haven't been a problem before with the same data base.

              <DbSource ConnectionRef="dataConnectionString (Web.config)" DbObjectName="" DbObjectType="Unknown" FillMethodModifier="Public" FillMethodName="FyllSubsidtabell" GenerateMethods="Both" GenerateShortCommands="true" GeneratorGetMethodName="HämtaSubsidtabell" GeneratorSourceName="FyllSubsidtabell" GetMethodModifier="Public" GetMethodName="HämtaSubsidtabell" QueryType="Rowset" ScalarCallRetval="System.Object, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" UseOptimisticConcurrency="true" UserGetMethodName="HämtaSubsidtabell" UserSourceName="FyllSubsidtabell">
                <SelectCommand>
                  <DbCommand CommandType="Text" ModifiedByUser="true">
                    <CommandText>
                      SELECT     Id, Rubrik, Rubriktext, Parenter, Klass, Visas, O1, O2, O3, O4, O5
                      FROM         (SELECT     *, SWITCH(@ORDER_BY = 1, O1, @ORDER_BY = 2, O2, @ORDER_BY = 3, O3, @ORDER_BY = 4, O4, @ORDER_BY = 5, O5, @ORDER_BY = 0, Rubrik) AS order_by
                      FROM          Sidor)
                      WHERE Parenter LIKE '%' + @PARENT + '%'
                      ORDER BY order_by</CommandText>
                    <Parameters>
                      <Parameter AllowDbNull="false" AutogeneratedName="" DataSourceName="" DbType="Int32" Direction="Input" ParameterName="ORDER_BY" Precision="0" Scale="0" Size="0" SourceColumnNullMapping="false" SourceVersion="Current" />
                      <Parameter AllowDbNull="false" AutogeneratedName="" DataSourceName="" DbType="String" Direction="Input" ParameterName="PARENT" Precision="0" Scale="0" Size="0" SourceColumnNullMapping="false" SourceVersion="Current" />
                    </Parameters>
                  </DbCommand>
                </SelectCommand>
              </DbSource>

Answer 10

User-1199946673 posted

The columns O1 to O5 contains ordering numbers as integers, but they are now ordered as if they were strings, i.e. for example 1, 10, 11, 2, 3, 4, 5, 6, 7, 8, 9 instead of 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11.

 

use the Format function, to include leading zero's:

SWITCH(@ORDER_BY = 1, Format([O1], '000000'), @ORDER_BY = 2, Format([O2], '000000'), @ORDER_BY = 3, Format([O3], '000000'), @ORDER_BY = 4, Format([O4], '000000'), @ORDER_BY = 5, Format([O5], '000000'), @ORDER_BY = 0, Rubrik) AS order_by

off course, make sure to use more zero's when the values can be higher!