Handling checkbox in <asp:ControlParameter> section of SqlDataSource

Question

I'm reusing some code that I've had a lot of success with on other pages and the only significant difference here is that I'm capturing the value of a Checkbox in the filter panel that I haven't done in the past. Code sample is provided below, but the crux of what I'm running into is that I can't seem to find an acceptable set of parameters for the listed under the within the SqlDataSource.

What it throws is this: "The data types varchar and bit are incompatible in the add operator."

This is when I'm using Type="Boolean" in the asp:ControlParameter. I've tried numerious Types and other syntax within the asp:ControlParameter with no success.

<%--From the filter pannel--%>

<%--The SqlDataSource--%>

    <asp:SqlDataSource runat="server" ID="SqlDataSource1" CancelSelectOnNullParameter="false" ConnectionString='<%$ ConnectionStrings:csrConnectionString %>' DeleteCommand="DELETE FROM [csr_refdata_ip360_HostVulnerabilityCSV] WHERE [RecID] = @RecID" InsertCommand="INSERT INTO [csr_refdata_ip360_HostVulnerabilityCSV] ([DNS Name], [NetBIOS Name], [IP], [OS], [Vulnerability Score], [Vulnerability], [Vulnerability ID], [TicketNumber], [TicketClosed], [AssetID], [ExceptionID], [Notes]) VALUES (@DNS_Name, @NetBIOS_Name, @IP, @OS, @Vulnerability_Score, @Vulnerability, @Vulnerability_ID, @TicketNumber, @TicketClosed, @AssetID, @ExceptionID, @Notes)" SelectCommand="SELECT RecID, [DNS Name] AS DNS_Name, [NetBIOS Name] AS NetBIOS_Name, IP, OS, [Vulnerability Score] AS Vulnerability_Score, Vulnerability, [Vulnerability ID] AS Vulnerability_ID, TicketNumber, TicketClosed, AssetID, ExceptionID, Notes FROM csr_refdata_ip360_HostVulnerabilityCSV WHERE (Excepted = @Excepted) AND (RTRIM(LTRIM(ISNULL(@RecID, ''))) = '' OR RecID LIKE '%' + @RecID + '%') AND (RTRIM(LTRIM(ISNULL(@DNS_Name, ''))) = '' OR [DNS Name] LIKE '%' + @DNS_Name + '%') AND (RTRIM(LTRIM(ISNULL(@NetBIOS_Name, ''))) = '' OR [NetBIOS Name] LIKE '%' + @NetBIOS_Name + '%') AND (RTRIM(LTRIM(ISNULL(@IP, ''))) = '' OR IP LIKE '%' + @IP + '%') AND (RTRIM(LTRIM(ISNULL(@OS, ''))) = '' OR OS LIKE '%' + @OS + '%') AND (RTRIM(LTRIM(ISNULL(@Vulnerability_Score, ''))) = '' OR [Vulnerability Score] LIKE '%' + @Vulnerability_Score + '%') AND (RTRIM(LTRIM(ISNULL(@Vulnerability, ''))) = '' OR Vulnerability LIKE '%' + @Vulnerability + '%') AND (RTRIM(LTRIM(ISNULL(@Vulnerability_ID, ''))) = '' OR [Vulnerability ID] LIKE '%' + @Vulnerability_ID + '%') AND (RTRIM(LTRIM(ISNULL(@TicketNumber, ''))) = '' OR TicketNumber LIKE '%' + @TicketNumber + '%') AND (RTRIM(LTRIM(ISNULL(@TicketClosed, ''))) = '' OR TicketClosed LIKE '%' + @TicketClosed + '%') AND (RTRIM(LTRIM(ISNULL(@Notes, ''))) = '' OR Notes LIKE '%' + @Notes + '%') AND (RTRIM(LTRIM(ISNULL(@AssetID, ''))) = '' OR AssetID LIKE '%' + @AssetID + '%') AND (RTRIM(LTRIM(ISNULL(@ExceptionID, ''))) = '' OR ExceptionID LIKE '%' + @ExceptionID + '%') ORDER BY Vulnerability_Score DESC, Vulnerability, NetBIOS_Name" UpdateCommand="UPDATE [csr_refdata_ip360_HostVulnerabilityCSV] SET [DNS Name] = @DNS_Name, [NetBIOS Name] = @NetBIOS_Name, [IP] = @IP, [OS] = @OS, [Vulnerability Score] = @Vulnerability_Score, [Vulnerability] = @Vulnerability, [Vulnerability ID] = @Vulnerability_ID, [TicketNumber] = @TicketNumber, [TicketClosed] = @TicketClosed, [AssetID] = @AssetID, [ExceptionID] = @ExceptionID, [Notes] = @Notes WHERE [RecID] = @RecID">
    <DeleteParameters>
        <asp:Parameter Name="RecID" Type="Int32"></asp:Parameter>
    </DeleteParameters>
    <InsertParameters>
        <asp:Parameter Name="DNS_Name" Type="String"></asp:Parameter>
        <asp:Parameter Name="NetBIOS_Name" Type="String"></asp:Parameter>
        <asp:Parameter Name="IP" Type="String"></asp:Parameter>
        <asp:Parameter Name="OS" Type="String"></asp:Parameter>
        <asp:Parameter Name="Vulnerability_Score" Type="Int16"></asp:Parameter>
        <asp:Parameter Name="Vulnerability" Type="String"></asp:Parameter>
        <asp:Parameter Name="Vulnerability_ID" Type="Int32"></asp:Parameter>
        <asp:Parameter Name="TicketNumber" Type="Int32"></asp:Parameter>
        <asp:Parameter Name="TicketClosed" Type="Boolean"></asp:Parameter>
        <asp:Parameter Name="AssetID" Type="Int64"></asp:Parameter>
        <asp:Parameter Name="ExceptionID" Type="Int32"></asp:Parameter>
        <asp:Parameter Name="Notes" Type="String"></asp:Parameter>
    </InsertParameters>
    <SelectParameters>
        <asp:Parameter DefaultValue="N" Name="Excepted" Type="String"></asp:Parameter>
        <asp:ControlParameter Name="RecID" ControlID="RecID_Tbx" Type="Int32" ConvertEmptyStringToNull="true" />
        <asp:ControlParameter Name="DNS_Name" ControlID="DNSName_Tbx" Type="String" ConvertEmptyStringToNull="true"/>
        <asp:ControlParameter Name="NetBIOS_Name" ControlID="NetBIOSName_Tbx" Type="String" ConvertEmptyStringToNull="true" />
        <asp:ControlParameter Name="IP" ControlID="IP_Tbx" Type="String" ConvertEmptyStringToNull="true"/>
        <asp:ControlParameter Name="OS" ControlID="OS_Tbx" Type="String" ConvertEmptyStringToNull="true" />
        <asp:ControlParameter Name="Vulnerability_Score" ControlID="Vulnerability_Tbx" Type="Int16" ConvertEmptyStringToNull="true" />
        <asp:ControlParameter Name="Vulnerability" ControlID="Vulnerability_Tbx" Type="String" ConvertEmptyStringToNull="true" />
        <asp:ControlParameter Name="Vulnerability_ID" ControlID="VulnerabilityID_Tbx" Type="Int32" ConvertEmptyStringToNull="true" />
        <asp:ControlParameter Name="TicketNumber" ControlID="TicketNum_Tbx" Type="String" ConvertEmptyStringToNull="true" />

    <%--<%--<%-- This is the problem line below --%>--%>--%>
        <asp:ControlParameter Name="TicketClosed" ControlID="TicketClosed_ChBox" Type="Boolean" ConvertEmptyStringToNull="true" />
        <asp:ControlParameter Name="Notes" ControlID="Notes_Tbx" Type="String" ConvertEmptyStringToNull="true" />
        <asp:ControlParameter Name="AssetID" ControlID="AssetID_Tbx" Type="Int64" ConvertEmptyStringToNull="true" />
        <asp:ControlParameter Name="ExceptionID" ControlID="ExceptionID_Tbx" Type="String" ConvertEmptyStringToNull="true" />
    </SelectParameters>
    <UpdateParameters>
        <asp:Parameter Name="DNS_Name" Type="String"></asp:Parameter>
        <asp:Parameter Name="NetBIOS_Name" Type="String"></asp:Parameter>
        <asp:Parameter Name="IP" Type="String"></asp:Parameter>
        <asp:Parameter Name="OS" Type="String"></asp:Parameter>
        <asp:Parameter Name="Vulnerability_Score" Type="Int16"></asp:Parameter>
        <asp:Parameter Name="Vulnerability" Type="String"></asp:Parameter>
        <asp:Parameter Name="Vulnerability_ID" Type="Int32"></asp:Parameter>
        <asp:Parameter Name="TicketNumber" Type="Int32"></asp:Parameter>
        <asp:Parameter Name="TicketClosed" Type="Boolean"></asp:Parameter>
        <asp:Parameter Name="AssetID" Type="Int64"></asp:Parameter>
        <asp:Parameter Name="ExceptionID" Type="Int32"></asp:Parameter>
        <asp:Parameter Name="Notes" Type="String"></asp:Parameter>
        <asp:Parameter Name="RecID" Type="Int32"></asp:Parameter>
    </UpdateParameters>
</asp:SqlDataSource>

Any insight on how I can stuff this checkbox into my ControlParameter properly would be greatly appreciated. I've pretty much exhausted my ideas.

have you thought about changing the type from Boolean to Byte.. for example <asp:ControlParameter ControlID="TicketClosed_ChBox" Name="TicketClosed" PropertyName="Checked" Type="Byte" DefaultValue="0" /> — MethodMan
– MethodMan, Commented Apr 11, 2017 at 13:42
Yes.. I've done that, as well as Char, Int16, String, and Sbyte with similar results. I'm considering changing the database table to Char. I noticed something with your example that I may have missed.. trying it now. I had PropertyName="Checked" without the type being set. — Ken Carter
– Ken Carter, Commented Apr 11, 2017 at 13:47
@MethodMan I tweeked it to read as you suggested The result was "Conversion failed when converting the varchar value '%' to data type int. " Switched back to Boolean with your syntax and got "The data types varchar and bit are incompatible in the add operator. " as well. — Ken Carter
– Ken Carter, Commented Apr 11, 2017 at 13:59

Ken Carter · Accepted Answer · 2017-04-11 20:18:23Z

I ended up altering the db table changing the column to varchar(1) so that I could set all BoundFields in the gridview. Then updated my to:

<SelectParameters>
<asp:Parameter DefaultValue="N" Name="Excepted" Type="String"></asp:Parameter>
<asp:ControlParameter Name="RecID" ControlID="RecID_Tbx" Type="String" ConvertEmptyStringToNull="true" />
<asp:ControlParameter Name="DNS_Name" ControlID="DNSName_Tbx" Type="String" ConvertEmptyStringToNull="true"/>
<asp:ControlParameter Name="NetBIOS_Name" ControlID="NetBIOSName_Tbx" Type="String" ConvertEmptyStringToNull="true" />
<asp:ControlParameter Name="IP" ControlID="IP_Tbx" Type="String" ConvertEmptyStringToNull="true"/>
<asp:ControlParameter Name="OS" ControlID="OS_Tbx" Type="String" ConvertEmptyStringToNull="true" />
<asp:ControlParameter Name="Vulnerability_Score" ControlID="VulnerabilityScore_Tbx" Type="String" ConvertEmptyStringToNull="true" />
<asp:ControlParameter Name="Vulnerability" ControlID="Vulnerability_Tbx" Type="String" ConvertEmptyStringToNull="true" />
<asp:ControlParameter Name="Vulnerability_ID" ControlID="VulnerabilityID_Tbx" Type="String" ConvertEmptyStringToNull="true" />
<asp:ControlParameter Name="TicketNumber" ControlID="TicketNum_Tbx" Type="String" ConvertEmptyStringToNull="true" />
<asp:ControlParameter Name="TicketClosed" ControlID="TicketClosed_Tbx" Type="String" ConvertEmptyStringToNull="true" />
<asp:ControlParameter Name="Notes" ControlID="Notes_Tbx" Type="String" ConvertEmptyStringToNull="true" />
<asp:ControlParameter Name="AssetID" ControlID="AssetID_Tbx" Type="String" ConvertEmptyStringToNull="true" />
<asp:ControlParameter Name="ExceptionID" ControlID="ExceptionID_Tbx" Type="String" ConvertEmptyStringToNull="true" />

Everything is working fine now.... MethodMan, thanks for your input! This one is done!

Collectives™ on Stack Overflow

Handling checkbox in <asp:ControlParameter> section of SqlDataSource

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