Error when using a union query with a boolean value and setting it to a checkbox asp.net

Question

I have an ASP.NET page that has a gridview with a checkbox control, which is bound to a query (simplified version below):

<asp:SqlDataSource ID="gvPhonesDataSource" runat="server" OnInserted="gvPhonesDataSource_Inserted"
    ConnectionString="<%$ ConnectionStrings:ConnString %>" 
    SelectCommand="SELECT p.[CustomerPhoneID]
                ,p.[IsActive]
            FROM [dbo].[CustomerPhones] p
            WHERE p.CustomerContactID = @CustomerContactID
            UNION SELECT -1 AS CustomerPhoneID, 0 AS [IsActive]"
    <SelectParameters>
        <asp:ControlParameter Name="CustomerContactID" Type="Int32" ControlID="contactid" PropertyName="Text" />
    </SelectParameters>
</asp:SqlDataSource>

<asp:gridview ID="gvPhones" runat="server" DataSourceID="gvPhonesDataSource"
    AutoGenerateColumns="False" DataKeyNames="CustomerPhoneID" 
    CssClass="searchresultsgrid" ShowFooter="True" OnRowCommand="gvPhones_RowCommand" AllowSorting="True"
    OnRowDataBound="gvPhones_RowDataBound">
    <Columns>
        <asp:BoundField DataField="CustomerPhoneID" InsertVisible="false" ReadOnly="true" Visible="False" />

        <asp:TemplateField HeaderText="Active?" SortExpression="IsActive">
            <FooterTemplate>
                <asp:CheckBox runat="server" Checked='<%# Bind("IsActive") %>' ID="chkPhoneIsActive"></asp:CheckBox>
            </FooterTemplate>
            <EditItemTemplate>
                <asp:CheckBox runat="server" Checked='<%# Bind("IsActive") %>' ID="chkPhoneIsActive"></asp:CheckBox>
            </EditItemTemplate>
            <ItemTemplate>
                <asp:Label runat="server" Text='<%# Bind("IsActive") %>' ID="lblPhoneIsActive"></asp:Label>
            </ItemTemplate>
        </asp:TemplateField>


        <asp:TemplateField ShowHeader="False">
            <EditItemTemplate>
                <asp:LinkButton runat="server" Text="Update" CommandName="Update" CausesValidation="True" ID="PhoneUpdate"></asp:LinkButton>&nbsp;<asp:LinkButton runat="server" Text="Cancel" CommandName="Cancel" CausesValidation="False" ID="PhoneEditCancel"></asp:LinkButton>
            </EditItemTemplate>
            <ItemTemplate>
                <asp:LinkButton runat="server" Text="Edit" CommandName="Edit" CausesValidation="False" ID="PhoneEdit"></asp:LinkButton>&nbsp;<asp:LinkButton runat="server" Text="Delete" CommandName="Delete" CausesValidation="False" ID="PhoneDelete"></asp:LinkButton>
            </ItemTemplate>
            <FooterTemplate>
                <asp:LinkButton runat="server" Text="Save New Phone" CommandName="FooterInsert" CausesValidation="True" ID="PhoneInsert"></asp:LinkButton>
            </FooterTemplate>
        </asp:TemplateField>
    </Columns>
</asp:gridview>

Everything works fine, without the UNION SELECT in the SelectCommand in the SqlDataSource. But when I put in the UNION SELECT, the values in the gridview's IsActive column stop being listed as "Yes" or "No," and start being listed as "1" or "0," and when I try to go in to edit (which tries to bind IsActive to a checkbox), I get the following error:

Exception Details: System.InvalidCastException: Specified cast is not valid.

Source Error: 

Line 143:                            </FooterTemplate>
Line 144:                            <EditItemTemplate>
Line 145:                                <asp:CheckBox runat="server" Checked='<%# Bind("IsActive") %>' ID="chkPhoneIsActive"></asp:CheckBox>
Line 146:                            </EditItemTemplate>
Line 147:                            <ItemTemplate>

Source File: ...\ContactEdit.aspx    Line: 145

I suspect the issue is in the query... that the UNION is causing the bound IsActive column to be returned as string, and not as boolean (that's what the compiler thinks, anyway). How do I fix the UNION so that IsActive continues to be considered a boolean?

FYI, the relevant parts of the table (SQL Server 2014) is as follows:

CREATE TABLE [dbo].[CustomerPhones](
    [CustomerPhoneID] [int] IDENTITY(1,1) NOT NULL,
    [IsActive] [bit] NOT NULL CONSTRAINT [DF_CustomerPhones_IsActive]  DEFAULT ((1)),
 CONSTRAINT [PK_CustomerPhones] PRIMARY KEY CLUSTERED 
(
    [CustomerPhoneID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Many thanks!

I've updated the question with more error details. mjwills: I hadn't even thought of that! Kind of silly in retrospect. :) (So used to 0 just automatically being recognized as a boolean value.) I'll give it a shot now. — Katerine459
– Katerine459, Commented Mar 28, 2018 at 20:34

mjwills · Accepted Answer · 2018-03-28 20:39:25Z

1

Your

0 AS [IsActive]

means "0 as integer" rather than "0 as bit".

You can fix this using CAST:

CAST(0 AS BIT) AS [IsActive]

answered Mar 28, 2018 at 20:39

mjwills

24.1k6 gold badges45 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Error when using a union query with a boolean value and setting it to a checkbox asp.net

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related