Search code examples
c#asp.netcheckboxsqldatasource

Handling checkbox in <asp:ControlParameter> section of SqlDataSource

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.

Solution

  • 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!