Click here to Skip to main content
11,649,522 members (78,871 online)
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C# .NET4
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 OUTER JOIN tbl_PurchaseMaster PM ON PD.Prod_ID = PM.Prod_ID) LEFT OUTER JOIN tbl_SaleMaster SM ON PD.Prod_ID = SM.Prod_ID) GROUP BY PD.Prod_ID, PD.Prod_ModelNo, PD.Prod_BrandName";

i m using this query to retrive data from access database but i m getting a error message
"Type mismatch in expression".

and i know that how it's give error because my column PD.Prod_ID is AutoNumber datatype.
 
and 
PM.Prod_ID is Number datatype
 
SM.Prod_ID is also Number Datatype.

but i dont know how i solve this problem...

so any one help me....
Posted 18-Jan-13 20:58pm
Edited 18-Jan-13 21:03pm
v2
Comments
Shambhoo kumar at 19-Jan-13 4:10am
   
can any one help me..

1 solution

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

Solution 1

I can give you a technique to solve your problem although I can't tell you exactly what is wrong ...

1. Take out all of the lines with SUM(...) in and run the query like that.
This shouldn't give you any problems - this just gives you your baseline.

2. Put the sections you took out back in one at a time ... e.g. put SUM(iif(Isnull(SM.Prod_Qty), 0, SM.Prod_Qty)) AS Sale_Item

I'm expecting you to get the error, but if not put the calculation for purchase_item back in and run the query again.

In other words - the technique is to start with something simple, make sure it works, then add in extra bits - testing each time that it still works. When it fails you know it's the last thing you changed.

A couple of other points ...You haven't said what database you are using, but in SQL the function Isnull takes two parameters - the column you are querying and then a value to substitute if it returns a null. So try replacing iif(Isnull(SM.Prod_Qty), 0, SM.Prod_Qty) with Isnull(SM.Prod_Qty,0) if your database caters for that - it's a lot neater and easier to read.

And finally - I don't think there is any need for the brackets around your FROM clause - they just add "noise"
  Permalink  
Comments
Shambhoo kumar at 19-Jan-13 12:45pm
   
thanks for recomend......
CHill60 at 19-Jan-13 15:20pm
   
My apologies - I've just realised that you did actually say in the title that it was an Access database you are using - so you are right to use Isnull with IIF (or you might be able to use the nz function depending on how you connect). http://stackoverflow.com/questions/137398/sql-null-set-to-zero-for-adding
Hope my suggested technique helps you find the problem

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

  Print Answers RSS
0 OriginalGriff 354
1 F-ES Sitecore 344
2 Kornfeld Eliyahu Peter 319
3 CPallini 290
4 Maciej Los 269
0 OriginalGriff 1,577
1 Sergey Alexandrovich Kryukov 1,087
2 CPallini 1,015
3 DamithSL 996
4 jyo.net 994


Advertise | Privacy | Mobile
Web04 | 2.8.150804.4 | Last Updated 19 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