binding to checkbox column in radgrid throws Specified cast is not valid error

[Jim]

Need help resolving "Specified cast is not valid" error thrown when attempting to bind datatable to radgrid having checkbox column.

 

<telerik:gridtemplatecolumn headertext="Input" uniquename="TemplateColumn1">

  <itemtemplate>

    <asp:checkbox id="CheckBox1" runat="server" checked='<%# Eval("STATUS") %>' />  --> ERROR

  </itemtemplate>

  <edititemtemplate>

    <asp:checkbox id="CheckBox1" runat="server" checked='<%# Eval("STATUS") %>' />

  </edititemtemplate>

</telerik:gridtemplatecolumn>

 

[Elliott]

the stored procedure (or SQL) needs to return a Bit

SELECT RegistrationID,
       DMP = CASE DMP WHEN 1 THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT) END,
       LNS = CASE LNS WHEN 1 THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT) END,
       KMS = CASE KMS WHEN 1 THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT) END,
       StartTime
FROM Registration R

<asp:GridView ID="gvVendorMaintenance" AutoGenerateColumns="False" DataKeyNames="RegistrationID, VendorID, TrainingID" ShowFooter="true" HeaderStyle-CssClass="body_text_black_BOLD" runat="server" >
<Columns>
    <asp:BoundField DataField="RegistrationID" Visible="false" />
    <asp:TemplateField HeaderText="DMP" ItemStyle-HorizontalAlign="Center" FooterStyle-HorizontalAlign="Center">
        <ItemTemplate>
            <asp:CheckBox ID="chkDMP" Checked='<%# Eval("DMP") %>' OnCheckedChanged="chk_OnCheckChanged" runat="server" />
        </ItemTemplate>
        <FooterTemplate>
            <asp:CheckBox ID="chkNewDMP" OnCheckedChanged="chkNew_OnCheckChanged"  AutoPostBack="true" runat="server" />
        </FooterTemplate>
    </asp:TemplateField>
    <asp:TemplateField HeaderText="LNS" ItemStyle-HorizontalAlign="Center" FooterStyle-HorizontalAlign="Center">
        <ItemTemplate>
            <asp:CheckBox ID="chkLNS" Checked='<%# Eval("LNS") %>' OnCheckedChanged="chk_OnCheckChanged" runat="server" />
        </ItemTemplate>
        <FooterTemplate>
            <asp:CheckBox ID="chkNewLNS" OnCheckedChanged="chkNew_OnCheckChanged"  AutoPostBack="true" runat="server" />
        </FooterTemplate>
    </asp:TemplateField>
    <asp:TemplateField HeaderText="KMS" ItemStyle-HorizontalAlign="Center" FooterStyle-HorizontalAlign="Center">
        <ItemTemplate>
            <asp:CheckBox ID="chkKMS" Checked='<%# Eval("KMS") %>' OnCheckedChanged="chk_OnCheckChanged" runat="server" />
        </ItemTemplate>
        <FooterTemplate>
            <asp:CheckBox ID="chkNewKMS" OnCheckedChanged="chkNew_OnCheckChanged" AutoPostBack="true" runat="server" />
        </FooterTemplate>
    </asp:TemplateField>
    <asp:TemplateField HeaderText="Edit" ShowHeader="False" >
        <ItemTemplate>
            <asp:LinkButton ID="btnEdit" CommandName="Edit" CausesValidation="False" Text="Edit" runat="server" />
        </ItemTemplate>
        <EditItemTemplate>
            <asp:LinkButton ID="btnUpdate" CommandName="Update" CausesValidation="True" Text="Update" runat="server" />
            <asp:LinkButton ID="btnCancel" CommandName="Cancel" CausesValidation="False" Text="Cancel" runat="server" />
        </EditItemTemplate>
        <FooterTemplate>
            <asp:LinkButton ID="btnAddNew" CommandName="AddNew" Text="Add New" CausesValidation="False" runat="server" />
        </FooterTemplate>
    </asp:TemplateField>
    <asp:TemplateField HeaderText="Delete" ShowHeader="False">
        <ItemTemplate>
            <asp:LinkButton ID="btnDelete" CommandName="Delete" Text="Delete" CausesValidation="False" OnClientClick="return ConfirmDelete();" runat="server" />
        </ItemTemplate>
    </asp:TemplateField>
</Columns>
</asp:GridView>

[Jim]

 

 

I'm using an Oracle database, and they don't have a BIT data type, so I tried
DECODE(V.SUPPLIER_NO, NULL, 'FALSE', 'TRUE') AS STATUS

DECODE(V.SUPPLIER_NO, NULL, 0, 1) AS STATUS

DECODE(V.SUPPLIER_NO, NULL, '0' , '1' ) AS STATUS
All 3 variations rendered the same result: Specified cast is not valid
I could get the first option to work with the gridview, but I was hoping to use the radgrid. 

any ideas? 

[Elliott]

I haven't used Oracle for over 10 years
I Google'd and found that Oracle 9 has a Boolean datatype - can you CAST Status to Boolean?

if that doesn't work - then you'll have to go into the ItemDataBound event and get the Status out of the table cell and manually populate every row

[Jim]

Marianne,
Thank you for your suggestions.  Oracle doesn't have a Boolean data type, but rather a NUMBER data type that has a sub type of Boolean. I tried:

 

DECODE(V.SUPPLIER_NO, NULL, CAST(0 AS NUMBER) , CAST(1 AS NUMBER) ) AS STATUS

DECODE(V.SUPPLIER_NO, NULL, CAST(0 AS CHAR) , CAST(1 AS CHAR) ) AS STATUS
both of these options did not work unfortunately... :(

 

[Elliott]

Oracle 9i has a Boolean datatype

I am not a Telerik employee- I am just a mid-level developer who had a bit of trouble with the CheckBox grid column-and that was using SQL Server

hopefully, a Telerik person will read this thread tonight and give you an answer you can use

[Mira]

Hello guys,

I assume that the error is caused by binding the Checked property of the CheckBox to null value.
You can overcome this by setting it to false when the Status is null:

<asp:checkbox id="CheckBox1" runat="server" checked='(DataBinder.Eval(Container.DataItem,"Status") is DBNull ? false : Eval("Status"))' />

I hope this helps.

Best regards,
Mira
the Telerik team

[Jim]

this code snippet produced a runtime error: "Cannot create an object of type 'System.Boolean' from its string representation '(DataBinder.Eval(Container.DataItem,"Status") is DBNull ? false : Eval("Status")' for the 'Checked' property.
and a design time error:
  Validation (ASP.Net): The values permitted for this attribute do not include '(DataBinder.Eval(Container.DataItem,"Status") is DBNull ? false : Eval("Status")'

The DECODE statement I'm using in my Oracle sp prevents NULL values from being returned in the results set.  What value should I be returning?  True, 1 or '1' and what data type?  Oracle does not have a BIT data type.
I have tried all of these so far: 

DECODE(V.SUPPLIER_NO, NULL, 'FALSE', 'TRUE') AS STATUS

DECODE(V.SUPPLIER_NO, NULL, 0, 1) AS STATUS

DECODE(V.SUPPLIER_NO, NULL, '0' , '1' ) AS STATUS

DECODE(V.SUPPLIER_NO, NULL, CAST(0 AS NUMBER(1,1)) , CAST(1 AS NUMBER(1,1)) ) AS STATUS

DECODE(V.SUPPLIER_NO, NULL, CAST(0 AS NUMBER) , CAST(1 AS NUMBER) ) AS STATUS

DECODE(V.SUPPLIER_NO, NULL, CAST(0 AS CHAR) , CAST(1 AS CHAR) ) AS STATUS

DECODE(V.SUPPLIER_NO, NULL, CAST(0 AS NUMBER(1)) , CAST(1 AS NUMBER(1)) ) AS STATUS

[Elliott]

return boolean is
begin
    if upper(bool_in) = 'T' then
        return true;
    elsif upper(bool_in) = 'F' then return false;
    else    return null;
end if;
end;

the above function should work in Oracle 9i

if it doesn't you'll have to break your column up with a templated coulmn or manually populate the column in the ItemDataBound event

[Jim]

The bit data type is used to hold a single boolean value, 0 or 1. MS SQL does not support assigning NULL to this fields ( found this online)

However, either of these should work according to this description, but both ways render the same error

 

DECODE(V.SUPPLIER_NO, NULL, 0, 1) AS STATUS
DECODE(V.SUPPLIER_NO, NULL, CAST(0 AS NUMBER(1)) , CAST(1 AS NUMBER(1)) ) AS STATUS

Marianne, Oracle won't allow:
DECODE(V.SUPPLIER_NO, NULL, FALSE, TRUE) AS STATUS  -- this is the equivalent to your suggestion
it has to be either:

DECODE(V.SUPPLIER_NO, NULL, 'F', 'T') AS STATUS
DECODE(V.SUPPLIER_NO, NULL, 'FALSE', 'TRUE') AS STATUS
but neither of these work either. 

Telerik team suggested:

<asp:CheckBox ID="CheckBox1" runat="server" Checked='((DataBinder.Eval(Container.DataItem,"Status") == "FALSE") ? false : true' />

but this doesn't compile.

[Jim]

Telerik team suggested this, and it is now working!
<

 

 

asp:CheckBox ID="CheckBox2" runat="server" Checked='<%# Eval("Status").ToString() == "TRUE" %>' />

 

[Mira]

Hello Jim,

I am posting the code from a support ticket concerning the same issue here, as it might be useful for the community:

<asp:CheckBox ID="CheckBox1" runat="server" Checked='<%# Eval("Status").ToString() == "TRUE" %>' />

Best wishes,
Mira
the Telerik team

[Suresh]

Thanks for the solution

[Arivunidhi]

Thanks boss. very nice reply.