C#.Net altering sql statement

adam83 · 1 Jul 2008 at 14:28

Puzzled. So any help appreciated.

I have an sql statement that I need to change, as it works it selects depending on the users input from a drop down list (location). But if I want to select 'all' I'm getting errors with conversions fails (int).

Tried using wildcards (e.g. %) but to no avail.

For anyone interested the statement is:

Code:

SelectCommand="SELECT main_properties.rowid, MAX(main_properties.name_no) AS Name, MAX(main_properties.address) AS Address, MAX(main_properties.postcode) AS [Post Code], MAX(main_properties.price) AS Price, MAX(main_properties.available_for) AS Tenure, MAX(main_properties.size1) AS Size, MAX(main_join_property_types.type_id) AS Expr1, MAX(main_join_property_types.property_id) AS Expr2, MAX(main_property_types.rowid) AS Expr4, MAX(main_property_types.decription) AS Expr3, MAX(main_property_images.rowid) AS Expr7, MAX(main_property_images.property_id) AS Expr10, MAX(main_property_images.image) AS image, MAX(main_properties.active) AS Expr6 FROM main_properties INNER JOIN main_join_property_types ON main_properties.rowid = main_join_property_types.property_id INNER JOIN main_property_types ON main_join_property_types.type_id = main_property_types.rowid INNER JOIN main_property_images ON main_properties.rowid = main_property_images.property_id CROSS JOIN tbl_geotree_nodes GROUP BY main_properties.rowid HAVING (MAX(main_properties.active) = 'Yes') AND (MAX(main_properties.available_for) != @tenuretype) AND (MAX(main_join_property_types.type_id) = @typeoption) AND ((MAX(main_properties.area_id) = @locationoption ))" >

I wanted to use an IF statement to change the statement but having problems. Maybe going at this the wrong way, so any feedback welcomed.

adam83 · 2 Jul 2008 at 09:47

SimonCHere - will try that later on, thanks for the input. You understood exactly what I was getting act. Thank you :-)

lokkers - I will need to look into this, I'm a bit restricted on my development machine at work - sql database is running on a separate server on the network which is quite restricting. Long story. But I'm unable to store it locally, so I'm trying to keep every action within the asp pages.

I'll post the complete source code for anyone interested, to be honest it's the first serious c#.net website I've started so I'm sure most of you will laugh at the mess. As soon as I've got the functionality of the site sorted I will be looking at optimising it and sorting it all out.

Code:

<%@ Page Language="C#" MasterPageFile="~/MasterPage.master" AutoEventWireup="true" CodeFile="Find-properties.aspx.cs" Inherits="Find_properties" Title="Untitled Page" %>

<asp:Content ID="Content1" ContentPlaceHolderID="head" Runat="Server">
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
    <div>
        

 

    
           <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
            ConnectionString="<%$ ConnectionStrings:cmsproprtConnectionString %>" 
            

            
               SelectCommand="SELECT main_properties.rowid, MAX(main_properties.name_no) AS Name, MAX(main_properties.address) AS Address, MAX(main_properties.postcode) AS [Post Code], MAX(main_properties.price) AS Price, MAX(main_properties.available_for) AS Tenure, MAX(main_properties.size1) AS Size, MAX(main_join_property_types.type_id) AS Expr1, MAX(main_join_property_types.property_id) AS Expr2, MAX(main_property_types.rowid) AS Expr4, MAX(main_property_types.decription) AS Expr3, MAX(main_property_images.rowid) AS Expr7, MAX(main_property_images.property_id) AS Expr10, MAX(main_property_images.image) AS image, MAX(main_properties.active) AS Expr6 FROM main_properties INNER JOIN main_join_property_types ON main_properties.rowid = main_join_property_types.property_id INNER JOIN main_property_types ON main_join_property_types.type_id = main_property_types.rowid INNER JOIN main_property_images ON main_properties.rowid = main_property_images.property_id INNER JOIN tbl_geotree_nodes ON main_properties.area_id = tbl_geotree_nodes.parentid GROUP BY main_properties.rowid HAVING (MAX(main_properties.active) = 'Yes') AND (MAX(main_properties.available_for) &lt;&gt; @tenuretype) AND (MAX(main_join_property_types.type_id) = @typeoption) AND (MAX(main_properties.area_id) = @locationoption)" >
     <SelectParameters>
         <asp:ControlParameter ControlID="tenureoption" DefaultValue="both" 
             Name="tenuretype" PropertyName="SelectedValue" />
         <asp:ControlParameter ControlID="typeidoption" DefaultValue="1" 
             Name="typeoption" PropertyName="SelectedValue" />
         <asp:ControlParameter ControlID="locationoption" DefaultValue="_" 
             Name="locationoption" PropertyName="SelectedValue" />
             
            
     
     </SelectParameters>
             
             
            
            </asp:SqlDataSource>
            
            
           <asp:SqlDataSource ID="SqlDataSource5" runat="server" 
            ConnectionString="<%$ ConnectionStrings:cmsproprtConnectionString %>" 
            

            
               SelectCommand="SELECT main_properties.rowid, MAX(main_properties.name_no) AS Name, MAX(main_properties.address) AS Address, MAX(main_properties.postcode) AS [Post Code], MAX(main_properties.price) AS Price, MAX(main_properties.available_for) AS Tenure, MAX(main_properties.size1) AS Size, MAX(main_join_property_types.type_id) AS Expr1, MAX(main_join_property_types.property_id) AS Expr2, MAX(main_property_types.rowid) AS Expr4, MAX(main_property_types.decription) AS Expr3, MAX(main_property_images.rowid) AS Expr7, MAX(main_property_images.property_id) AS Expr10, MAX(main_property_images.image) AS image, MAX(main_properties.active) AS Expr6 FROM main_properties INNER JOIN main_join_property_types ON main_properties.rowid = main_join_property_types.property_id INNER JOIN main_property_types ON main_join_property_types.type_id = main_property_types.rowid INNER JOIN main_property_images ON main_properties.rowid = main_property_images.property_id INNER JOIN tbl_geotree_nodes ON main_properties.area_id = tbl_geotree_nodes.parentid GROUP BY main_properties.rowid HAVING (MAX(main_properties.active) = 'Yes') AND (MAX(main_properties.available_for) &lt;&gt; @tenuretype) AND (MAX(main_join_property_types.type_id) = @typeoption) AND (MAX(tbl_geotree_nodes.parentid) = @locationoption)" >
     <SelectParameters>
         <asp:ControlParameter ControlID="tenureoption" DefaultValue="both" 
             Name="tenuretype" PropertyName="SelectedValue" />
         <asp:ControlParameter ControlID="typeidoption" DefaultValue="1" 
             Name="typeoption" PropertyName="SelectedValue" />
         <asp:ControlParameter ControlID="locationoption" DefaultValue="_" 
             Name="locationoption" PropertyName="SelectedValue" />
             
            
     
     </SelectParameters>
             
             
            
            </asp:SqlDataSource>
      
    
        <asp:SqlDataSource ID="SqlDataSource2" runat="server" 
            ConnectionString="<%$ ConnectionStrings:cmsproprtConnectionString %>" 
            
            SelectCommand="SELECT [rowid], [reference] FROM [main_property_types] ORDER BY [rowid] ASC">
        </asp:SqlDataSource>
    
          
      
    
        
    
          
      
    
        <asp:SqlDataSource ID="SqlDataSource3" runat="server" 
            ConnectionString="<%$ ConnectionStrings:cmsproprtConnectionString %>" 
            SelectCommand="SELECT [nodeid], [label], [depth] FROM [tbl_geotree_nodes] WHERE ([depth] = @depth) ORDER BY [label]">
            <SelectParameters>
                <asp:Parameter DefaultValue="2" Name="depth" Type="Int32" />
            </SelectParameters>
        </asp:SqlDataSource>
    
          
      
    
        
    
          
      
    
        <asp:SqlDataSource ID="SqlDataSource4" runat="server" 
            ConnectionString="<%$ ConnectionStrings:cmsproprtConnectionString %>" 
            
            SelectCommand="SELECT [parentid] FROM [tbl_geotree_nodes] WHERE ([nodeid] = @nodeid2)">
            <SelectParameters>
                <asp:ControlParameter ControlID="locationoption" Name="nodeid2" 
                    PropertyName="SelectedValue" Type="Int32" />
            </SelectParameters>
        </asp:SqlDataSource>
        
    
          
      
    
        
    
          
      
    
        <div id="refinesearch">
            <br />
        Tenure:
        <asp:DropDownList ID="tenureoption" runat="server">
            <asp:ListItem Value="0">Please select</asp:ListItem>
            <asp:ListItem Value="Lease">Freehold</asp:ListItem>
            <asp:ListItem Value="Sale">Leasehold</asp:ListItem>
        </asp:DropDownList>
&nbsp;Location:
        <asp:DropDownList ID="locationoption" runat="server" DataSourceID="SqlDataSource3" 
                DataTextField="label" DataValueField="nodeid" AppendDataBoundItems="true">
            <asp:ListItem Value="'%2%'">Please select</asp:ListItem>
            
                   
            
  
        </asp:DropDownList>
&nbsp;Type:
        <asp:DropDownList ID="typeidoption" runat="server" DataSourceID="SqlDataSource2" 
                DataTextField="reference" DataValueField="rowid" AppendDataBoundItems="true">
            <asp:ListItem Value="">Please Select</asp:ListItem>
                
        </asp:DropDownList>
            <asp:Button ID="Button2" runat="server" Text="Refine Search" />
        </div>
    
          
      
    
    </div>
    <asp:DataList ID="DataList1" runat="server" DataKeyField="rowid" 
        DataSourceID="SqlDataSource1" Visible="False">
        <ItemTemplate>
            <table style="width:100%;">
                <tr>
                    <td rowspan="4" width="115px">
                        <asp:Image ID="Image2" runat="server" Height="82px" 
                            ImageUrl='<%# Eval("image") %>' Width="110px" />
                    </td>
                    <td rowspan="3" valign="top">
                        <asp:HyperLink ID="HyperLink1" runat="server" 
                            NavigateUrl='<%# Eval("rowid", "Details.aspx?id={0}") %>' 
                            Text='<%# Eval("Name") %>'></asp:HyperLink>
                        <br />
                       
                        <asp:Label ID="Label2" runat="server" Text='<%# Eval("[Address]").ToString().Replace("<p>"," ") %>'></asp:Label>
                        <asp:Label ID="Label3" runat="server" Text='<%# Eval("[Post Code]") %>'></asp:Label>
                        <br />
                        <asp:Label ID="Label4" runat="server" Text='<%# Eval("Tenure") %>'></asp:Label>
                    </td>
                    <td width="100px">
                        <asp:HyperLink ID="HyperLink2" runat="server" 
                            NavigateUrl='<%# Eval("rowid", "Details.aspx?id={0}") %>' 
                            Text='Full Details'></asp:HyperLink>
                    </td>
                </tr>
                <tr>
                    <td>
                        <asp:HyperLink ID="HyperLink3" runat="server" 
                            NavigateUrl='<%# Eval("rowid", "map.aspx?id={0}") %>' 
                            Text='See on map'></asp:HyperLink>
                    </td>
                </tr>
                <tr>
                    <td>
                        <asp:HyperLink ID="HyperLink4" runat="server" 
                            NavigateUrl='<%# Eval("rowid", "email-friend.aspx?id={0}") %>'>Email Friend</asp:HyperLink>
                    </td>
                </tr>
                <tr>
                    <td>
                        &nbsp;</td>
                    <td>
                        <asp:HyperLink ID="HyperLink5" runat="server" 
                            NavigateUrl='<%# Eval("rowid", "add-shortlist.aspx?id={0}") %>'>Add to shortlist</asp:HyperLink>
                    </td>
                </tr>
            </table>

        </ItemTemplate>
    </asp:DataList>
    <asp:ListView ID="ListView1" runat="server" DataKeyNames="rowid" 
        DataSourceID="SqlDataSource1">
        <AlternatingItemTemplate>
            <table style="width:100%;">
                <tr>
                    <td rowspan="4" width="115px">
                        <asp:Image ID="Image2" runat="server" Height="82px" 
                            ImageUrl='<%# Eval("image") %>' Width="110px" />
                    </td>
                    <td rowspan="3" valign="top">
                        <asp:HyperLink ID="HyperLink1" runat="server" 
                            NavigateUrl='<%# Eval("rowid", "Details.aspx?id={0}") %>' 
                            Text='<%# Eval("Name") %>'></asp:HyperLink>
                        <br />
                        <asp:Label ID="Label2" runat="server" Text='<%# Eval("[Address]").ToString().Replace("<p>"," ") %>'></asp:Label>
                        <asp:Label ID="Label3" runat="server" Text='<%# Eval("[Post Code]") %>'></asp:Label>
                        <br />
                        <asp:Label ID="Label4" runat="server" Text='<%# Eval("Tenure") %>'></asp:Label>
                    </td>
                    <td width="100px">
                        <asp:HyperLink ID="HyperLink2" runat="server" 
                            NavigateUrl='<%# Eval("rowid", "Details.aspx?id={0}") %>' 
                            Text='Full Details'></asp:HyperLink>
                    </td>
                </tr>
                <tr>
                    <td>
                        <asp:HyperLink ID="HyperLink3" runat="server" 
                            NavigateUrl='<%# Eval("rowid", "map.aspx?id={0}") %>' 
                            Text='See on map'></asp:HyperLink>
                    </td>
                </tr>
                <tr>
                    <td>
                        <asp:HyperLink ID="HyperLink4" runat="server" 
                            NavigateUrl='<%# Eval("rowid", "email-friend.aspx?id={0}") %>'>Email Friend</asp:HyperLink>
                    </td>
                </tr>
                <tr>
                    <td>
                        &nbsp;</td>
                    <td>
                        <asp:HyperLink ID="HyperLink5" runat="server" 
                            NavigateUrl='<%# Eval("rowid", "add-shortlist.aspx?id={0}") %>'>Add to shortlist</asp:HyperLink>
                    </td>
                </tr>
            </table>
        </AlternatingItemTemplate>
        <LayoutTemplate>
            <div ID="itemPlaceholderContainer" runat="server" style="">
                <span ID="itemPlaceholder" runat="server" />
            </div>
            <div style="">
                <asp:DataPager ID="DataPager1" runat="server">
                    <Fields>
                        <asp:NextPreviousPagerField ButtonType="Button" ShowFirstPageButton="True" 
                            ShowNextPageButton="False" ShowPreviousPageButton="False" />
                        <asp:NumericPagerField />
                        <asp:NextPreviousPagerField ButtonType="Button" ShowLastPageButton="True" 
                            ShowNextPageButton="False" ShowPreviousPageButton="False" />
                    </Fields>
                </asp:DataPager>
            </div>
        </LayoutTemplate>
        <InsertItemTemplate>
            <table style="width:100%;">
                <tr>
                    <td rowspan="4" width="115px">
                        <asp:Image ID="Image2" runat="server" Height="82px" 
                            ImageUrl='<%# Eval("image") %>' Width="110px" />
                    </td>
                    <td rowspan="3" valign="top">
                        <asp:HyperLink ID="HyperLink1" runat="server" 
                            NavigateUrl='<%# Eval("rowid", "Details.aspx?id={0}") %>' 
                            Text='<%# Eval("Name") %>'></asp:HyperLink>
                        <br />
                        <asp:Label ID="Label2" runat="server" Text='<%# Eval("[Address]").ToString().Replace("<p>"," ") %>'></asp:Label>
                        <asp:Label ID="Label3" runat="server" Text='<%# Eval("[Post Code]") %>'></asp:Label>
                        <br />
                        <asp:Label ID="Label4" runat="server" Text='<%# Eval("Tenure") %>'></asp:Label>
                    </td>
                    <td width="100px">
                        <asp:HyperLink ID="HyperLink2" runat="server" 
                            NavigateUrl='<%# Eval("rowid", "Details.aspx?id={0}") %>' 
                            Text='Full Details'></asp:HyperLink>
                    </td>
                </tr>
                <tr>
                    <td>
                        <asp:HyperLink ID="HyperLink3" runat="server" 
                            NavigateUrl='<%# Eval("rowid", "map.aspx?id={0}") %>' 
                            Text='See on map'></asp:HyperLink>
                    </td>
                </tr>
                <tr>
                    <td>
                        <asp:HyperLink ID="HyperLink4" runat="server" 
                            NavigateUrl='<%# Eval("rowid", "email-friend.aspx?id={0}") %>'>Email Friend</asp:HyperLink>
                    </td>
                </tr>
                <tr>
                    <td>
                        &nbsp;</td>
                    <td>
                        <asp:HyperLink ID="HyperLink5" runat="server" 
                            NavigateUrl='<%# Eval("rowid", "add-shortlist.aspx?id={0}") %>'>Add to shortlist</asp:HyperLink>
                    </td>
                </tr>
            </table>
        </InsertItemTemplate>
        <SelectedItemTemplate>
            <table style="width:100%;">
                <tr>
                    <td rowspan="4" width="115px">
                        <asp:Image ID="Image2" runat="server" Height="82px" 
                            ImageUrl='<%# Eval("image") %>' Width="110px" />
                    </td>
                    <td rowspan="3" valign="top">
                        <asp:HyperLink ID="HyperLink1" runat="server" 
                            NavigateUrl='<%# Eval("rowid", "Details.aspx?id={0}") %>' 
                            Text='<%# Eval("Name") %>'></asp:HyperLink>
                        <br />
                        <asp:Label ID="Label2" runat="server" Text='<%# Eval("[Address]").ToString().Replace("<p>"," ") %>'></asp:Label>
                        <asp:Label ID="Label3" runat="server" Text='<%# Eval("[Post Code]") %>'></asp:Label>
                        <br />
                        <asp:Label ID="Label4" runat="server" Text='<%# Eval("Tenure") %>'></asp:Label>
                    </td>
                    <td width="100px">
                        <asp:HyperLink ID="HyperLink2" runat="server" 
                            NavigateUrl='<%# Eval("rowid", "Details.aspx?id={0}") %>' 
                            Text='Full Details'></asp:HyperLink>
                    </td>
                </tr>
                <tr>
                    <td>
                        <asp:HyperLink ID="HyperLink3" runat="server" 
                            NavigateUrl='<%# Eval("rowid", "map.aspx?id={0}") %>' 
                            Text='See on map'></asp:HyperLink>
                    </td>
                </tr>
                <tr>
                    <td>
                        <asp:HyperLink ID="HyperLink4" runat="server" 
                            NavigateUrl='<%# Eval("rowid", "email-friend.aspx?id={0}") %>'>Email Friend</asp:HyperLink>
                    </td>
                </tr>
                <tr>
                    <td>
                        &nbsp;</td>
                    <td>
                        <asp:HyperLink ID="HyperLink5" runat="server" 
                            NavigateUrl='<%# Eval("rowid", "add-shortlist.aspx?id={0}") %>'>Add to shortlist</asp:HyperLink>
                    </td>
                </tr>
            </table>
        </SelectedItemTemplate>
        <EmptyDataTemplate>
            <span><p>No results matched your search criteria.</p></span>
        </EmptyDataTemplate>
        <EditItemTemplate>
           <table style="width:100%;">
                <tr>
                    <td rowspan="4" width="115px">
                        <asp:Image ID="Image2" runat="server" Height="82px" 
                            ImageUrl='<%# Eval("image") %>' Width="110px" />
                    </td>
                    <td rowspan="3" valign="top">
                        <asp:HyperLink ID="HyperLink1" runat="server" 
                            NavigateUrl='<%# Eval("rowid", "Details.aspx?id={0}") %>' 
                            Text='<%# Eval("Name") %>'></asp:HyperLink>
                        <br />
                        <asp:Label ID="Label2" runat="server" Text='<%# Eval("[Address]").ToString().Replace("<p>"," ") %>'></asp:Label>
                        <asp:Label ID="Label3" runat="server" Text='<%# Eval("[Post Code]") %>'></asp:Label>
                        <br />
                        <asp:Label ID="Label4" runat="server" Text='<%# Eval("Tenure") %>'></asp:Label>
                    </td>
                    <td width="100px">
                        <asp:HyperLink ID="HyperLink2" runat="server" 
                            NavigateUrl='<%# Eval("rowid", "Details.aspx?id={0}") %>' 
                            Text='Full Details'></asp:HyperLink>
                    </td>
                </tr>
                <tr>
                    <td>
                        <asp:HyperLink ID="HyperLink3" runat="server" 
                            NavigateUrl='<%# Eval("rowid", "map.aspx?id={0}") %>' 
                            Text='See on map'></asp:HyperLink>
                    </td>
                </tr>
                <tr>
                    <td>
                        <asp:HyperLink ID="HyperLink4" runat="server" 
                            NavigateUrl='<%# Eval("rowid", "email-friend.aspx?id={0}") %>'>Email Friend</asp:HyperLink>
                    </td>
                </tr>
                <tr>
                    <td>
                        &nbsp;</td>
                    <td>
                        <asp:HyperLink ID="HyperLink5" runat="server" 
                            NavigateUrl='<%# Eval("rowid", "add-shortlist.aspx?id={0}") %>'>Add to shortlist</asp:HyperLink>
                    </td>
                </tr>
            </table>
        </EditItemTemplate>
        <ItemTemplate>
            <table style="width:100%;">
                <tr>
                    <td rowspan="4" width="115px">
                        <asp:Image ID="Image2" runat="server" Height="82px" 
                            ImageUrl='<%# Eval("image") %>' Width="110px" />
                    </td>
                    <td rowspan="3" valign="top">
                        <asp:HyperLink ID="HyperLink1" runat="server" 
                            NavigateUrl='<%# Eval("rowid", "Details.aspx?id={0}") %>' 
                            Text='<%# Eval("Name") %>'></asp:HyperLink>
                        <br />
                        <asp:Label ID="Label2" runat="server" Text='<%# Eval("[Address]").ToString().Replace("<p>"," ") %>'></asp:Label>
                        <asp:Label ID="Label3" runat="server" Text='<%# Eval("[Post Code]") %>'></asp:Label>
                        <br />
                        <asp:Label ID="Label4" runat="server" Text='<%# Eval("Tenure") %>'></asp:Label>
                    </td>
                    <td width="100px">
                        <asp:HyperLink ID="HyperLink2" runat="server" 
                            NavigateUrl='<%# Eval("rowid", "Details.aspx?id={0}") %>' 
                            Text='Full Details'></asp:HyperLink>
                    </td>
                </tr>
                <tr>
                    <td>
                        <asp:HyperLink ID="HyperLink3" runat="server" 
                            NavigateUrl='<%# Eval("rowid", "map.aspx?id={0}") %>' 
                            Text='See on map'></asp:HyperLink>
                    </td>
                </tr>
                <tr>
                    <td>
                        <asp:HyperLink ID="HyperLink4" runat="server" 
                            NavigateUrl='<%# Eval("rowid", "email-friend.aspx?id={0}") %>'>Email Friend</asp:HyperLink>
                    </td>
                </tr>
                <tr>
                    <td>
                        &nbsp;</td>
                    <td>
                        <asp:HyperLink ID="HyperLink5" runat="server" 
                            NavigateUrl='<%# Eval("rowid", "add-shortlist.aspx?id={0}") %>'>Add to shortlist</asp:HyperLink>
                    </td>
                </tr>
            </table>
        </ItemTemplate>
    </asp:ListView>
</asp:Content>

<asp:Content ID="Content3" runat="server" 
    contentplaceholderid="ContentPlaceHolder2">

        
     <div id="mapbox">



<div id="mapbox">
<h3>Sponsored Links</h3>
</div>

</asp:Content>

adam83 · 2 Jul 2008 at 12:48

No luck with the 'isnull' option. hmmm

s

adam83 · 2 Jul 2008 at 17:15

You my friend are a legend, thank you.

Never came across 'cases' within a statement before, very interesting.

Mucho thanks.