C#.Net altering sql statement

adam83

adam83

adam83

Associate
Joined
17 Mar 2008
Posts
135
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 said:
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.
Click to expand...


I would guess that the dropdown box value for "All" is either null or "All" and that @locationoption is expecting an integer?

any way you probably need something like the following:
Code: 
...
AND ((MAX(main_properties.area_id) = @locationoption ) 
OR (MAX(main_properties.area_id) = isnull(@locationoption,MAX(main_properties.area_id)))

then make sure that the @locationoption is null when you pass the parameter and it should be fine.

A bit more of the sourcecode wouldn't go amiss though!

Simon
 
If that's querying a SQL database, use a stored procedure. Your DBA will thank you!

It'll also make it much easier to maintain. :)
 
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>
 
Just a quick look but you are going to have to change the select on datasource3 to include the All
Code: 
SELECT [nodeid], [label], [depth] FROM [tbl_geotree_nodes] WHERE ([depth] = @depth) 
Union all
select 0,'-SelectAll', @depth
ORDER BY [label]

This will give you the data from the dropdown (asuming nodeid is numeric).
Your select will then finish with:
Code: 
AND (MAX(main_join_property_types.type_id) = @typeoption) 
AND ((MAX(main_properties.area_id) = case @locationoption when 0 then MAX(main_properties.area_id) else @locationoption end))" >

Simon
 
Scratch that first bit, I didn't notice the AppendDataBoundItems="true" on locationoptions dropdown list.
Just change the value of the list item:
Code: 
<asp:ListItem Value="'%2%'">Please select</asp:ListItem>
to
Code: 
<asp:ListItem Value="0">Please select</asp:ListItem>

Adam32 said:
As soon as I've got the functionality of the site sorted I will be looking at optimising it and sorting it all out.
Click to expand...
Ah the lament of the harassed developer!

Oh and as lokkers says. Stick it in a stored proc if possible. Especially if the db isn't local to the server that the website is on.

Simon
 
Last edited:
You my friend are a legend, thank you.

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

Mucho thanks.
 
adam83 said:
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.
Click to expand...

Doesn't matter where the database is stored, as executing a stored procedure is just like executing a direct SQL statement. If you have a DBA and they do not allow access to the database, then they should be responsible for writing your queries (and this is ideal, as it's their job and they *should* be good at it).

However, I would strongly recommend doing all transactions against a SQL database as stored procedures, for 4 reasons (there are probably more, but this is my experience):

1) Storing a statement as a procedure means that SQL server can optimise the query - if you throw SQL at SQL server it doesn't know what to expect so queries run unoptimised.

2) Its a simple way to avoid nasty SQL injection attacks (especially on a web frontend).

3) As your queries are stored on the SQL server, it means that SQL can validate your queries as you go, so you dont have to validate them by running the application.

3) It makes patching much easier, because if you find a bug in your SQL you need just amend the SP, rather than having to deploy code.
 
lokkers said:
2) Its a simple way to avoid nasty SQL injection attacks (especially on a web frontend).
Click to expand...

Unless you use stored procs but don't use parameters.. which in my experience is more common than you think :)

Stored procs are excellent for all the reasons above, and they are useful in that you can put together a working SQL query quickly if you are under heavy time constaints, then return to check your execution plans etc. without having to rebuild.
 
The code Adam has written does use typed parameters so shouldn't be susceptible to SQL injection. Also SQL server can cache and optimize parameterized queries so performance will be OK as well.
I'd still stick it in a stored proc anyway just so your data layer isn't in your display layer.

Simon
 
You must log in or register to reply here.
Back
Top Bottom