locked
ORDER BY CASE WHEN RRS feed

  • Question

  • User-1630378091 posted

    I am trying to use a ORDER BY CASE to order the output.

    I have used  the followinng staement and it works fine;

    "SELECT tblStock.* FROM tblStock WHERE ID_No <> " & lngRecordNo & " AND tblstock.price <> 'SOLD' ORDER BY tblStock.DOR DESC"

    But when I add the ORDER BY CASE like so;

    "SELECT tblStock.* FROM tblStock WHERE ID_No <> " & lngRecordNo & " ORDER BY CASE WHEN tblstock.price = 'SOLD' THEN 1 ELSE 0 END, tblStock.DOR DESC"

    it throws an IErrorInfo error, what am I doing wrong?

    Saturday, June 11, 2011 3:01 PM

Answers

All replies

  • User-125547262 posted

    The case statement is the issue

    "SELECT tblStock.*, CASE WHEN tblstock.price = 'SOLD' THEN 1 ELSE 0 END FROM tblStock WHERE ID_No <> " & lngRecordNo & "

    ORDER BY  tblStock.DOR DESC

    Saturday, June 11, 2011 3:09 PM
  • User-1630378091 posted

    I tried thhat but got the same error. Here is my full connection code;


        Dim lngRecordNo As String
        lngRecordNo = Request.QueryString("ID")
        dim dbconn,sql,dbcomm,dbread
        dbconn=New OleDbConnection("PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA Source=path/file.mdb")
        dbconn.Open()
        sql="SELECT tblStock.*, CASE WHEN tblstock.price = 'SOLD' THEN 1 ELSE 0 END FROM tblStock WHERE ID_No <> " & lngRecordNo & " ORDER BY  tblStock.DOR DESC"
        dbcomm=New OleDbCommand(sql,dbconn)
        dbread=dbcomm.ExecuteReader()
       

    Saturday, June 11, 2011 3:22 PM
  • User-1199946673 posted

    Assuming you're using Access (since this is posted in the Access Databases and AccessDataSource Control forum) , the problem is the CASS WHEN statement. In Access, you should use the IIF function:

    IIF(tblstock.price = 'Sold', 1, 0)

    Also, you better start using parameters:

    http://www.mikesdotnetting.com/Article/26/Parameter-Queries-in-ASP.NET-with-MS-Access

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, June 12, 2011 8:20 AM
  • User-1630378091 posted

    Thank you hans, works fine now I replaced the CASE with an IIF.

    Sunday, June 12, 2011 8:40 AM