locked
using case with access database : query is giving error RRS feed

  • Question

  • User-610163401 posted

    I have 2 datefields and i need to count days between these dates. I have a field say kind of category and i need to display these categories as columns and the days are dsplayed as per the category under that particular columns.
    O/p
      x    |   y    |   z
    -----------------------
      1    |         |
            |   3    |
      12   |        |
             |        |   4
    here x, y, z are category in category column and the contents are date 2-date1+1

    I was trying to use a query  such as,

    SELECT CASE WHEN cat='x' THEN ([date2]-[date1]+1) END AS [X_COUNT],CASE WHEN cat='y' THEN ([date2]-[date1]+1) END AS [y_COUNT],CASE WHEN cat='z' THEN ([date2]-[date1]+1( END AS [z_COUNT]

    It's giving me error as,

    IErrorInfo.GetDescription failed with E_FAIL(0x80004005).

    Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

    Exception Details: System.Data.OleDb.OleDbException: IErrorInfo.GetDescription failed with E_FAIL(0x80004005).

    Stack Trace:

    [OleDbException (0x80004005): IErrorInfo.GetDescription failed with E_FAIL(0x80004005).]
       System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) +267
       System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) +192
       System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) +48
       System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) +106
       System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior) +111
       System.Data.OleDb.OleDbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) +4
       System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +141
       System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +137
       System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) +86
       QueryFunctions.processSelect(OleDbCommand& cmd, DataSet& ds) in E:\www.asp.net\WebSites\Corporate\LeaveAppModule\App_Code\QueryFunctions.vb:32
       Report_LeaveRecord.populateGridView() in E:\www.asp.net\WebSites\Corporate\LeaveAppModule\Report\LeaveRecord.aspx.vb:42
       Report_LeaveRecord.Page_Load(Object sender, EventArgs e) in E:\www.asp.net\WebSites\Corporate\LeaveAppModule\Report\LeaveRecord.aspx.vb:13
       System.Web.UI.Control.OnLoad(EventArgs e) +99
       System.Web.UI.Control.LoadRecursive() +47
       System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1436
    

    Monday, January 19, 2009 12:06 PM

Answers

  • User-821857111 posted

    Mike, maybe a new topic for your website?
     

    LOL.  I think Aaron has done a nice enough job on his site covering this topic.

    Well, it's not actually "his" site any more.  He sold it a couple of years back...

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, January 20, 2009 2:31 AM

All replies

  • User-821857111 posted

    You can't use CASE with Access.  It doesn't support it.  One way to do this is just retrieve the raw values, then use VB or C# in the RowDataBound event of the datacontrol to apply the logic.

     

    Monday, January 19, 2009 2:19 PM
  • User-1199946673 posted

    Mikesdotnetting

    You can't use CASE with Access.  It doesn't support it. 

    That's correct, however the equivalent for the SQL Server CASE statement in Access is SWITCH....

    -- SQL Server 
    SELECT CASE  
        WHEN On=1 THEN 'On' 
        WHEN On=0 THEN 'Off' 
    END FROM table 
     
    -- or 


    SELECT CASE On 
        WHEN 1 THEN 'On' 
        WHEN 0 THEN 'Off' 
    END FROM table 

    Translates in Access to:

    SELECT Switch( 
        On=1,'On', 
        On=0,'Off' 
    ) FROM table

    More information on the differences between Access and SQL Server can be found at:

    Mike, maybe a new topic for your website?

    Monday, January 19, 2009 6:14 PM
  • User-821857111 posted

    Mike, maybe a new topic for your website?
     

    LOL.  I think Aaron has done a nice enough job on his site covering this topic.

    Well, it's not actually "his" site any more.  He sold it a couple of years back...

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, January 20, 2009 2:31 AM