getting error in retrive data into access database

Question

0.00/5 (No votes)

See more:

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)."

C#

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 7:11am

Shambhoo kumar

Updated 18-Jan-13 7:18am

v2

Add a Solution

Comments

[no name] 18-Jan-13 13:29pm

Have you tested your query in SQL management studio ? Is it working fine there ?

Shambhoo kumar 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 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..

[no name] 18-Jan-13 13:36pm

Post here some other part of your code for accessing DB

Shambhoo kumar 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 18-Jan-13 14:53pm

Try to enclose the table's name with [ ] , like [advance].

chester_it21 18-Jan-13 14:58pm

or Try to enclose the all Columns name with [ ]

Shambhoo kumar 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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Dave Kreskowiak · Answer 1 · 2013-01-18T09:31:00

Solution 1

Access doesn't support the CASE WHEN construct.

Access supports a subset of TSQL, not the entire language specification.

Posted 18-Jan-13 9:31am

Dave Kreskowiak

Comments

Shambhoo kumar 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 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 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]'."