How do I bind a gridview column based on a condition-CodePudding

In a sql table I have 2 columns 'county' for Florida counties and county_other for counties in other states. In the gridview I have bound just county but when the value for county is "Other" I want to bind the value from the county_other column. How do I set up a condition like this.

<asp:BoundField DataField="county" HeaderText="County" SortExpression="county" HeaderStyle-ForeColor="White" HeaderStyle-BackColor="#093145" ItemStyle-Wrap="False" />

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:DATACENTERConnectionString %>"
    OnSelected="SqlDataSource1_Selected" SelectCommand="SELECT id, ca_name, cp_title, cp_firstname, cp_lastname, cp_credentials, contact_firstname, contact_lastname, address, city, STATE, zipcode, county, county_other, phone, fax, tp_adults, tp_pediatrics,tp_wh, tp_family, tp_other, other, date_created, date_contract, date_memorandum, comments, ca_type, recent FROM clinical_agency LEFT JOIN (
SELECT clinical_agencyid
    ,term as recent
FROM clinical_students
WHERE id IN (
        SELECT max(id)
        FROM clinical_students
        GROUP BY clinical_agencyid
        )
    AND term &lt;&gt; 'Select...'
) clinical_students ON (clinical_agency.id = clinical_students.clinical_agencyid) WHERE (ca_name LIKE '%'   @ca_name   '%') ORDER BY CAST(date_created AS DATETIME) DESC">
    <SelectParameters>
        <asp:ControlParameter ControlID="TxtClinical" DefaultValue="%" Name="ca_name" PropertyName="Text"
            Type="String" />
        <asp:ControlParameter ControlID="TxtFirstname" DefaultValue="%"
            Name="cp_firstname" PropertyName="Text" Type="String" />
        <asp:ControlParameter ControlID="TxtCity" DefaultValue="%" Name="city"
            PropertyName="Text" Type="String" />
        <asp:ControlParameter ControlID="TxtLastname" DefaultValue="%"
            Name="cp_lastname" PropertyName="Text" Type="String" />
        <asp:ControlParameter ControlID="TxtZipcode" DefaultValue="%" Name="zipcode"
            PropertyName="Text" Type="String" />
        <asp:ControlParameter ControlID="txtCounty" DefaultValue="%" Name="county"
            PropertyName="SelectedValue" Type="String" />
        <asp:ControlParameter ControlID="DDLCP_Credentials" DefaultValue="%"
            Name="cp_credentials" PropertyName="SelectedValue" Type="String" />
        <asp:ControlParameter ControlID="HiddenField2" Name="date_memorandum"
            PropertyName="Value" DefaultValue="" />
    </SelectParameters>
</asp:SqlDataSource>

CodePudding user response：

I would do it in the SQL. Something like:

SELECT CASE County WHEN 'Other' THEN County_Other ELSE County END AS County

CodePudding user response：

change the sql script

SelectCommand="SELECT id, ....., STATE, zipcode,  CASE WHEN STATE='Florida' THEN county 
ELSE county_other  END AS county, phone, fax,..."