using case with access database : query is giving error : The Official Microsoft ASP.NET Forums

Reply

victor0282

Member

42 Points

using case with access database : query is giving error

Jan 19, 2009 04:06 PM|LINK

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

Moved by SGWellens on Jan 19, 2009 04:21 PM

Reply

Mikesdotnett...

All-Star

154858 Points

19858 Posts

Moderator

MVP

Re: using case with access database : query is giving error

Jan 19, 2009 06:19 PM|LINK

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.

Regards, Mike
Beginning ASP.NET Web Pages with WebMatrix | My Site | Twitter