A few people have mentioned that i should use coalesce in the following statement. the problem is i don't know where i should be using it
Can someone show me where i should place it?

1    SELECT        (SELECT        Location
2                              FROM            Location_Table
3                              WHERE        (Property_Table.LocationID = LocationID)) AS Location,
4                                 (SELECT        TypeOfProperty
5                                   FROM            Type_Table
6                                   WHERE        (Property_Table.LocationID = TypeID)) AS TypeOfProperty, PropertyID, LocationID, TypeID, Title, Description, Price, Bedrooms
7    FROM            Property_Table
8    WHERE        (NULLIF (@MinPrice, 0) IS NULL) AND (NULLIF (@MaxPrice, 0) IS NULL) AND (NULLIF (@TypeID, 0) IS NULL) AND (NULLIF (@LocationID, 0) IS NULL) OR
9                             (NULLIF (@MinPrice, 0) IS NULL) AND (NULLIF (@MaxPrice, 0) IS NULL) AND (NULLIF (@LocationID, 0) IS NULL) AND (TypeID = @TypeID) OR
10                            (NULLIF (@MinPrice, 0) IS NULL) AND (NULLIF (@MaxPrice, 0) IS NULL) AND (NULLIF (@TypeID, 0) IS NULL) AND (LocationID = @LocationID) OR
11                            (NULLIF (@MinPrice, 0) IS NULL) AND (NULLIF (@MaxPrice, 0) IS NULL) AND (TypeID = @TypeID) AND (LocationID = @LocationID) OR
12                            (NULLIF (@MinPrice, 0) IS NULL) AND (NULLIF (@TypeID, 0) IS NULL) AND (NULLIF (@LocationID, 0) IS NULL) AND (Price <= @MaxPrice) OR
13                            (NULLIF (@MinPrice, 0) IS NULL) AND (NULLIF (@TypeID, 0) IS NULL) AND (LocationID = @LocationID) AND (Price <= @MaxPrice) OR
14                            (NULLIF (@MinPrice, 0) IS NULL) AND (NULLIF (@LocationID, 0) IS NULL) AND (TypeID = @TypeID) AND (Price <= @MaxPrice) OR
15                            (NULLIF (@MinPrice, 0) IS NULL) AND (TypeID = @TypeID) AND (LocationID = @LocationID) AND (Price <= @MaxPrice) OR
16                            (NULLIF (@TypeID, 0) IS NULL) AND (NULLIF (@LocationID, 0) IS NULL) AND (Price >= @MinPrice) AND (Price <= @MaxPrice) OR
17                            (NULLIF (@TypeID, 0) IS NULL) AND (LocationID = @LocationID) AND (Price >= @MinPrice) AND (Price <= @MaxPrice) OR
18                            (NULLIF (@LocationID, 0) IS NULL) AND (TypeID = @TypeID) AND (Price >= @MinPrice) AND (Price <= @MaxPrice) OR
19                            (TypeID = @TypeID) AND (LocationID = @LocationID) AND (Price >= @MinPrice) AND (Price <= @MaxPrice)

I think they're probably suggesting that you can simplify the WHERE clause by using COALESCE. If I've understood what you're trying to do correctly, this is a search query with 4 parameters - MinPrice, MaxPrice, LocationID and TypeID. Any number of these parameters can be set so the user could say, get me all properties with a MinPrice of 150,000 and you return all the properties, regardless of Type, Location etc. So, if I've got that bit right it means you can simplify your query. I'd use ISNULL rather than COALESCE (they do the same thing in this instance but I think ISNULL might be more efficient). Using ISNULL will rely on a parameter that isn't specified being NULL rather than 0 (which it looks like you're passing at the moment), so you could try something like this:

IF (@MinPrice = 0) SELECT @MinPrice = NULL
IF(@MaxPrice = 0) SELECT @MaxPrice = NULL
IF(@TypeID = 0) SELECT @TypeID = NULL
IF(@LocationID = 0) SELECT @LocationID = NULL

SELECT    (SELECT Location FROM Location_Table WHERE (Property_Table.LocationID = LocationID)) AS Location,
        (SELECT TypeOfProperty FROM Type_Table WHERE (Property_Table.LocationID = TypeID)) AS TypeOfProperty,
        PropertyID, LocationID, TypeID, Title, Description, Price, Bedrooms
FROM    Property_Table
WHERE    (Price >= ISNULL(@MinPrice, Price)) AND
        (Price <= ISNULL(@MaxPrice, Price)) AND
        (LocationID = ISNULL(@LocationID, LocationID) AND
        (TypeID = ISNULL(@TypeID, TypeID))

The only caveat with this syntax is that if any of your columns can contain NULL the statement won't work as expected. This is because NULL = NULL returns false. If this is the case you could expand the WHERE clause to something like this instead (which is also a little easier to understand):

SELECT    (SELECT Location FROM Location_Table WHERE (Property_Table.LocationID = LocationID)) AS Location,
        (SELECT TypeOfProperty FROM Type_Table WHERE (Property_Table.LocationID = TypeID)) AS TypeOfProperty,
        PropertyID, LocationID, TypeID, Title, Description, Price, Bedrooms
FROM    Property_Table
WHERE    (Price >= @MinPrice OR @MinPrice IS NULL) AND
        (Price <= @MaxPrice OR @MaxPrice IS NULL) AND
        (LocationID = @LocationID OR @LocationID IS NULL) AND
        (TypeID = @TypeID OR @TypeID IS NULL)

ramblor:

IF (@MinPrice = 0) SELECT @MinPrice = NULL
IF(@MaxPrice = 0) SELECT @MaxPrice = NULL
IF(@TypeID = 0) SELECT @TypeID = NULL
IF(@LocationID = 0) SELECT @LocationID = NULL

i get "Error in SELECT clause: expression near '='.
The ( SQL construct or statement is not supported."

can you point me to some good sites i can learn this stuff from? 

I'm not really sure of any articles I'm afraid. I found this one on comparing various ways of building your WHERE clause.

I don't know why you're getting the error (though it maybe down to me testing with a sproc and you're using inline SQL or something), but since you're passing 0 instead of NULL for a parameter that isn't specified maybe you could try this query instead:

SELECT    (SELECT Location FROM Location_Table WHERE (Property_Table.LocationID = LocationID)) AS Location,
        (SELECT TypeOfProperty FROM Type_Table WHERE (Property_Table.LocationID = TypeID)) AS TypeOfProperty,
        PropertyID, LocationID, TypeID, Title, Description, Price, Bedrooms
FROM    Property_Table
WHERE    (Price >= @MinPrice OR @MinPrice = 0) AND
        (Price <= @MaxPrice OR @MaxPrice = 0) AND
        (LocationID = @LocationID OR @LocationID = 0) AND
        (TypeID = @TypeID OR @TypeID = 0)

ramblor:

SELECT    (SELECT Location FROM Location_Table WHERE (Property_Table.LocationID = LocationID)) AS Location,
        (SELECT TypeOfProperty FROM Type_Table WHERE (Property_Table.LocationID = TypeID)) AS TypeOfProperty,
        PropertyID, LocationID, TypeID, Title, Description, Price, Bedrooms
FROM    Property_Table
WHERE    (Price >= @MinPrice OR @MinPrice = 0) AND
        (Price <= @MaxPrice OR @MaxPrice = 0) AND
        (LocationID = @LocationID OR @LocationID = 0) AND
        (TypeID = @TypeID OR @TypeID = 0)

Thats works great in the query builder.

what i have done is create a test page and attached the BLL Function to an objectdatasource and for now just added two dropdowns for the price and setting default values of 0 for @TypeID and @LocationID.
i have set up a breakpoint at the following function and also watched the values of  LocationID, TypeID, MinPrice, MaxPrice in the watch window.
Everything seems to be working and i get all the right values in the watch window But for some reason when ever i select a value other then 0 for both dropdowns it displays no results when i know it should display some.

here is the function in the BLL

1    <System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Select, False)> _
2        Public Function GetByLocationAndType(ByVal LocationID As Integer, ByVal TypeID As Integer, ByVal MinPrice As Integer, ByVal MaxPrice As Integer) As Property_DataSet1.Property_TableDataTable
3            Return Adapter.GetByLocationAndType(LocationID, TypeID, MinPrice, MaxPrice)
4        End Function

here is my test page

1    <%@ Page Language="VB" debug="true" AutoEventWireup="false" CodeFile="Default2.aspx.vb" Inherits="Default2" %>
2    
3    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
4    
5    <html xmlns="http://www.w3.org/1999/xhtml">
6    <head runat="server">
7        <title>Untitled Page</title>
8    </head>
9    <body>
10       <form id="form1" runat="server">
11       <div>
12           price greater than
13           <asp:DropDownList ID="greterthan" runat="server">
14               <asp:ListItem>0</asp:ListItem>
15               <asp:ListItem>100000</asp:ListItem>
16               <asp:ListItem>125000</asp:ListItem>
17               <asp:ListItem>150000</asp:ListItem>
18               <asp:ListItem>200000</asp:ListItem>
19               <asp:ListItem></asp:ListItem>
20           </asp:DropDownList>
21    Price less than
22           <asp:DropDownList ID="lessthan" runat="server" AutoPostBack="True">
23               <asp:ListItem>0</asp:ListItem>
24               <asp:ListItem>120000</asp:ListItem>
25               <asp:ListItem>160000</asp:ListItem>
26               <asp:ListItem>200000</asp:ListItem>
27               <asp:ListItem></asp:ListItem>
28           </asp:DropDownList>
29           <br />
30           <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" 
31               DataKeyNames="PropertyID" DataSourceID="ObjectDataSource1">
32               <Columns>
33                   <asp:BoundField DataField="PropertyID" HeaderText="PropertyID" 
34                       InsertVisible="False" ReadOnly="True" SortExpression="PropertyID" />
35                   <asp:BoundField DataField="LocationID" HeaderText="LocationID" 
36                       SortExpression="LocationID" />
37                   <asp:BoundField DataField="TypeID" HeaderText="TypeID" 
38                       SortExpression="TypeID" />
39                   <asp:BoundField DataField="Title" HeaderText="Title" SortExpression="Title" />
40                   <asp:BoundField DataField="Description" HeaderText="Description" 
41                       SortExpression="Description" />
42                   <asp:BoundField DataField="Price" HeaderText="Price" SortExpression="Price" />
43                   <asp:BoundField DataField="Location" HeaderText="Location" ReadOnly="True" 
44                       SortExpression="Location" />
45                   <asp:BoundField DataField="TypeOfProperty" HeaderText="TypeOfProperty" 
46                       ReadOnly="True" SortExpression="TypeOfProperty" />
47                   <asp:BoundField DataField="Bedrooms" HeaderText="Bedrooms" 
48                       SortExpression="Bedrooms" />
49               </Columns>
50           </asp:GridView>
51           <br />
52           <br />
53           <asp:ObjectDataSource ID="ObjectDataSource1" runat="server" 
54               InsertMethod="AddProperty" OldValuesParameterFormatString="original_{0}" 
55               SelectMethod="GetByLocationAndType" TypeName="PropertyBLL">
56               <SelectParameters>
57                   <asp:Parameter DefaultValue="0" Name="LocationID" Type="Int32" />
58                   <asp:Parameter DefaultValue="0" Name="TypeID" Type="Int32" />
59                   <asp:ControlParameter ControlID="greterthan" DefaultValue="" Name="MinPrice" 
60                       PropertyName="SelectedValue" Type="Int32" />
61                   <asp:ControlParameter ControlID="lessthan" DefaultValue="" Name="MaxPrice" 
62                       PropertyName="SelectedValue" Type="Int32" />
63               </SelectParameters>
64               <InsertParameters>
65                   <asp:Parameter Name="LocationID" Type="Int32" />
66                   <asp:Parameter Name="TypeID" Type="Int32" />
67                   <asp:Parameter Name="Title" Type="String" />
68                   <asp:Parameter Name="Description" Type="String" />
69                   <asp:Parameter Direction="InputOutput" Name="Price" Type="Decimal" />
70                   <asp:Parameter Name="Bedrooms" Type="Int32" />
71                  
72               </InsertParameters>
73           </asp:ObjectDataSource>
74           <br />
75           <br />
76       </div>
77       </form>
78   </body>
79   </html>
80   

I just spotted a small error in your SQL statement which may or may not be the problem. One of your sub-queries is checking the wrong thing:

(SELECT TypeOfProperty FROM Type_Table WHERE (Property_Table.LocationID = TypeID)) AS TypeOfProperty

If this isn't just in the post you'll need to update your SQL to:

SELECT (SELECT Location FROM Location_Table WHERE (Property_Table.LocationID = LocationID)) AS Location,
       (SELECT TypeOfProperty FROM Type_Table WHERE (Property_Table.TypeID = TypeID)) AS TypeOfProperty,
        PropertyID, LocationID, TypeID, Title, Description, Price, Bedrooms
FROM    Property_Table
WHERE    (Price >= @MinPrice OR @MinPrice = 0) AND
        (Price <= @MaxPrice OR @MaxPrice = 0) AND
        (LocationID = @LocationID OR @LocationID = 0) AND
        (TypeID = @TypeID OR @TypeID = 0)

Double-check the GetByLocationAndType function in your BLL is passing the parameters to your Adapter.GetByLocationAndType in the correct order. As they're all ints it would be easy to pass them in the wrong order and get the params mixed up:

Return Adapter.GetByLocationAndType(LocationID, TypeID, MinPrice, MaxPrice) 

ramblor:

Double-check the GetByLocationAndType function in your BLL is passing the parameters to your Adapter.GetByLocationAndType in the correct order.

I changed  

Return Adapter.GetByLocationAndType(LocationID, TypeID, MinPrice, MaxPrice) 

to

 Return Adapter.GetByLocationAndType(MinPrice, MaxPrice, LocationID, TypeID) 

Thanks, i was not aware that the order i was passing was important, just noticed the order they should be by looking at the .xsd file 

your a god 

Haha, not really, I've just done the same thing myself   If you "Mark as Answer" the posts that helped you it'll help other folk with the same problem find the answer (and I'll get some points and it's all about the points obviously )