Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C# .NET4
Hello every one my this code is work on sql database but when i use this query in access database then i m getting error.....
error is....."IErrorInfo.GetDescription failed with E_FAIL(0x80004005)."
 
cmdselect.CommandText = "select PD.Prod_ID,PD.Prod_BrandName, PD.Prod_ModelNo,sum(case when PM.prod_Qty is not null then PM.prod_Qty else 0 end ) as Purchase_Item,sum(case when SM.prod_Qty is not null then SM.prod_Qty else 0 end) as Sale_Item,sum(case when PM.prod_Qty is not null then PM.prod_Qty else 0 end )-  sum(case when SM.prod_Qty is not null then SM.prod_Qty else 0 end) as Balance_Item from tbl_ProductDetails PD left join tbl_PurchaseMaster PM on PD.Prod_ID=PM.Prod_ID left join tbl_SaleMaster SM on PD.Prod_ID=SM.Prod_ID group by PD.Prod_ID,PD.Prod_ModelNo,PD.Prod_BrandName";
 
plz help me.
Posted 18-Jan-13 8:11am
Edited 18-Jan-13 8:18am
v2
Comments
Sheikh Muhammad Haris at 18-Jan-13 13:29pm
   
Have you tested your query in SQL management studio ? Is it working fine there ?
Shambhoo kumar at 18-Jan-13 13:31pm
   
yes it's work fine in sql ...but when i use this in access database then it shows error..
Shambhoo kumar at 18-Jan-13 13:34pm
   
whenever i search this error on google then i got, my query is contain access database reserved word. but i unable to find it..plz help me..
Sheikh Muhammad Haris at 18-Jan-13 13:36pm
   
Post here some other part of your code for accessing DB
Shambhoo kumar at 18-Jan-13 13:47pm
   
tbl_ProductDetails
(
[Prod_ID] [int] IDENTITY(1,1) NOT NULL,
[Prod_BrandName] [nvarchar](255) NULL,
[Prod_ModelNo] [nvarchar](255) NULL,
[Prod_Category] [nvarchar](255) NULL,
[Prod_Description] [nvarchar](255) NULL,
[Prod_SalePrice] [int] NOT NULL,
[Prod_CostPrice] [int] NOT NULL,
[Prod_ListPrice] [int] NOT NULL,
[Prod_MRP] [int] NOT NULL,
[Prod_Status] [bit]
)
 
tbl_PurchaseMaster
(
[Master_ID] [int] IDENTITY(1,1) NOT NULL,
[PurchaseOrder_ID] [nvarchar](255) NULL,
[Prod_ID] [nvarchar](255) NULL,
[Prod_Qty] [int] NULL,
[Order_Status] [bit]
)
 
[tbl_SaleMaster]
(
[Master_ID] [int] IDENTITY(1,1) NOT NULL,
[SaleOrder_ID] [nvarchar](255) NULL,
[Prod_ID] [nvarchar](255) NULL,
[Prod_Qty] [int] NULL,
[Prod_ListPrice] [int] NULL,
[Order_Status] [bit]
)
 
it's my access database table
chester_it21 at 18-Jan-13 14:53pm
   
Try to enclose the table's name with [ ] , like [advance].
chester_it21 at 18-Jan-13 14:58pm
   
or Try to enclose the all Columns name with [ ]
Shambhoo kumar at 18-Jan-13 23:32pm
   
case when is not supported in access database so any one help me what i use to complete this query....give any example how to check nullcondition in IIF() function...
thanks in adv..

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Access doesn't support the CASE WHEN construct.
 
Access supports a subset of TSQL, not the entire language specification.
  Permalink  
Comments
Shambhoo kumar at 18-Jan-13 23:03pm
   
plz sir tell me how can i write this same query without "case",but i want to check null value ..so plz sir help me...
..
Dave Kreskowiak at 19-Jan-13 0:39am
   
I have no idea because I never use Access for anything anymore. I use SQL Server Express if I need a desktop database.
 
I seem to remember something about a Switch construct you can use in the query, but that was a long time ago.
Shambhoo kumar at 19-Jan-13 2:26am
   
hello sir i solve this but again i geting a error msg...
 
cmdselect.CommandText = "select PD.Prod_ID,PD.Prod_BrandName, PD.Prod_ModelNo,sum(iif(Isnull(PM.Prod_Qty), 0, PM.Prod_Qty)) as Purchase_Item,sum(iif(Isnull(SM.Prod_Qty), 0, SM.Prod_Qty)) as Sale_Item,sum(iif(Isnull(PM.Prod_Qty), 0, PM.Prod_Qty))- sum(iif(Isnull(SM.Prod_Qty), 0, SM.Prod_Qty)) as Balance_Item from tbl_ProductDetails PD left join tbl_PurchaseMaster PM on [PD.Prod_ID]=[PM.Prod_ID] left join tbl_SaleMaster SM on [PD.Prod_ID]=[SM.Prod_ID] group by PD.Prod_ID,PD.Prod_ModelNo,PD.Prod_BrandName";
 
error msg is :-
 
"Syntax error (missing operator) in query expression '[PD.Prod_ID]=[PM.Prod_ID] left join tbl_SaleMaster SM on [PD.Prod_ID]=[SM.Prod_ID]'."

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 680
1 Maciej Los 300
2 Suvendu Shekhar Giri 229
3 Sascha Lefévre 229
4 Richard Deeming 195
0 Sergey Alexandrovich Kryukov 9,678
1 OriginalGriff 9,135
2 Peter Leow 5,082
3 Kornfeld Eliyahu Peter 3,373
4 Maciej Los 2,606


Advertise | Privacy | Mobile
Web01 | 2.8.150327.1 | Last Updated 18 Jan 2013
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100