title: ASP.NET SQLDataSource Expects columns in FilterExpression to be in Select Statement of Stored Procedure
body: <p>I'm building a repeater for search results using a stored procedure and FilterExpressions</p>
  
  <p>The only want to user 3 or 4 columns in my repeater but may have a few different queries in my where statement. </p>
  
  <p>My Stored Procedure will look something like this </p>
  
  <pre><code>Select organisationRegulatory.ID, 
      organisationRegulatory.name As regname,  
      regAddr1, regAddr2, regAddr3, regAddr4, regAddr5, 
      CRONumber, 
      CHYNumber, 
      legalObjects, 
      charityActCategoryID,
      isprimary,
      organisationVoluntary.overviewURL As overviewURL,
      min(targetCommunityId) as targetCommunityId
      from organisationRegulatory 
          inner join organisationVoluntary 
          on organisationRegulatory.ID =  organisationVoluntary.ID
          left outer join targetCommunitiesToOrganisation
          on organisationRegulatory.ID = targetCommunitiesToOrganisation.ID
          left outer join charityActCategoriesToOrganisation
          on organisationRegulatory.ID = charityActCategoriesToOrganisation.ID
          left outer join financial
          on organisationRegulatory.ID = financial.ID
      where organisationVoluntary.publishStatusID = 2 
  Group BY organisationRegulatory.ID, 
  regAddr1,regAddr2,regAddr3,regAddr4,regAddr5,
      CRONumber, 
      CHYNumber, 
      legalObjects,
      organisationRegulatory.name, 
      organisationVoluntary.overviewURL,
      nameCompSecretary,
      charityActCategoryID,
      isPrimary,
      overviewURL
      order by ID
  </code></pre>
  
  <p>And my filter expression might look something like this </p>
  
  <pre><code>AND (value &gt; 50000 AND financialActivityTypeID = 31) AND (isPrimary = 1 AND charityActCategoryID = 1)
  </code></pre>
  
  <p>If I add the Filter Expression to the where clause in the SQL normally I get the expected results, but if I try to add the FilterExpression to the Stpred Procedure via </p>
  
  <pre><code>SqlDataSource1.FilterExpression = "AND (value &gt; 50000 AND financialActivityTypeID = 31) AND (isPrimary = 1 AND charityActCategoryID = 1)";
  </code></pre>
  
  <p>I get an error, </p>
  
  <pre><code>Exception Details: System.Data.EvaluateException: Cannot find column [financialActivityTypeId].
  </code></pre>
  
  <p>until I add that column to the select statement of the SQL query, I don't want to do this as it slows down the query a great deal. Is it possible to get around this? </p>
  
  <p>Here's the repeater </p>
  
  <pre><code>&lt;asp:Repeater 
                                  id="rptSearchResults" 
                                  runat="server" 
                                  DataSourceID="SqlDataSource1"
                                  onitemdatabound="Repeater1_ItemDataBound"&gt;
  </code></pre>
  
  <p>and here's the SQL datasource </p>
  
  <pre><code>&lt;asp:SqlDataSource 
      ID="SqlDataSource1" 
      runat="server" 
      ConnectionString="&lt;%$ ConnectionStrings:DBConnectionString %&gt;"
      ProviderName="&lt;%$ ConnectionStrings:DBConnectionString.ProviderName %&gt;" 
      SelectCommand="spGetSearchResults" 
      SelectCommandType="StoredProcedure"
      &gt;
  
  &lt;/asp:SqlDataSource&gt;
  </code></pre>
  
  <p>Any help on this would be really appreciated. </p>
  
  <p>Thanks </p>