Click here to Skip to main content
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)



Advertise | Privacy | Mobile
Web03 | 2.8.140709.1 | Last Updated 19 Jan 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid