String was not recognized as a valid Boolean

I get this error when trying to run my update function.  The error is happening it seems for Budget

String was not recognized as a valid Boolean.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.FormatException: String was not recognized as a valid Boolean.

Source Error:


Line 53:                 ds_dashboard.UpdateParameters("Budget").DefaultValue = intBudget
Line 54:
Line 55:                 ds_dashboard.Update()
Line 56:             End If
Line 57:         Next
 

    Public Sub UpdateCustomer_DashboardGraphs(ByVal sender As Object, ByVal e As System.EventArgs)
        For Each gvr As GridViewRow In gv_dashboard.Rows
            If gvr.RowType = DataControlRowType.DataRow Then

                '//--IDs

                Dim intCustomerID As String = CType(gvr.FindControl("lblCustomerID"), Label).Text.Trim()

                '//--Textboxes
                Dim intBudget As Integer
                Try
                    intBudget = CType(CType(gvr.FindControl("txtBudget"), TextBox).Text.Trim(), Int32)
                Catch
                    intBudget = 0
                End Try

                '//--Checkboxes
                Dim intCurrentMonthCollections As Boolean = CType(gvr.FindControl("chbx_CurrentMonthCollections"), CheckBox).Checked
                Dim intRevenueByMonth As Boolean = CType(gvr.FindControl("chbx_RevenueByMonth"), CheckBox).Checked
                Dim intPDCsCCsMonthly As Boolean = CType(gvr.FindControl("chbx_PDCsCCsMonthly"), CheckBox).Checked
                Dim intRevenueByClient As Boolean = CType(gvr.FindControl("chbx_RevenueByClient"), CheckBox).Checked

                '//--Assign UpdateParameters
                If intCurrentMonthCollections = True Then
                    ds_dashboard.UpdateParameters("CurrentMonthCollections").DefaultValue = 1
                Else
                    ds_dashboard.UpdateParameters("CurrentMonthCollections").DefaultValue = 0
                End If


                ds_dashboard.UpdateParameters("Budget").DefaultValue = intBudget

                ds_dashboard.Update()
            End If
        Next
    End Sub
LVL 1
dba123Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

bsdotnetCommented:
what is the value of intBudget? If it's either "0" or "1", try using "True" or "False".
0
dba123Author Commented:
no, intBudget is a textbox
0
DBAduck - Ben MillerPrincipal ConsultantCommented:
This indicates to me that the definition of the UpdateParameters is incorrectly saying that the Budget field it specified to be a boolean instead of a string.

I would check that first.

Ben.
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

dba123Author Commented:
everything about Budget is an integer.  In my stored proc update command, and in my code.  So why is it stating that?  It may not even be the Budget field, that's just what I assume after I hit continue, since it focused on that but it may be the other UpdateParams
0
dba123Author Commented:
Stack Trace:

String was not recognized as a valid Boolean.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.FormatException: String was not recognized as a valid Boolean.

Source Error:


Line 53:                 ds_dashboard.UpdateParameters("Budget").DefaultValue = intBudget
Line 54:
Line 55:                 ds_dashboard.Update()
Line 56:             End If
Line 57:         Next
 

Source File: Y:\inetpub\wwwroot\apex\webapps\dashboard\dashboard\index.aspx.vb    Line: 55

Stack Trace:


[FormatException: String was not recognized as a valid Boolean.]
   System.Boolean.Parse(String value) +2709172
   System.String.System.IConvertible.ToBoolean(IFormatProvider provider) +12
   System.Convert.ChangeType(Object value, TypeCode typeCode, IFormatProvider provider) +98
   System.Web.UI.WebControls.Parameter.GetValue(Object value, String defaultValue, TypeCode type, Boolean convertEmptyStringToNull, Boolean ignoreNullableTypeChanges) +257
   System.Web.UI.WebControls.Parameter.get_ParameterValue() +91
   System.Web.UI.WebControls.ParameterCollection.GetValues(HttpContext context, Control control) +282
   System.Web.UI.WebControls.SqlDataSourceView.InitializeParameters(DbCommand command, ParameterCollection parameters, IDictionary exclusionList) +344
   System.Web.UI.WebControls.SqlDataSourceView.ExecuteUpdate(IDictionary keys, IDictionary values, IDictionary oldValues) +449
   System.Web.UI.WebControls.SqlDataSourceView.Update(IDictionary keys, IDictionary values, IDictionary oldValues) +37
   System.Web.UI.WebControls.SqlDataSource.Update() +42
   dashboard._Default.UpdateCustomer_DashboardGraphs(Object sender, EventArgs e) in Y:\inetpub\wwwroot\sss\webapps\dashboard\dashboard\index.aspx.vb:55
   System.Web.UI.WebControls.Button.OnClick(EventArgs e) +96
   System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +116
   System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +31
   System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +32
   System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +72
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +3837
0
dba123Author Commented:
Here's complete information

Why am I getting this error: String was not recognized as a valid Boolean

For Line: ds_dashboard.Update() in my update function

My SQLDataSource:

        <asp:SqlDataSource

            ID="ds_dashboard"

            runat="server"

            ConnectionString="<%$ ConnectionStrings:DashboardConn %>"

            SelectCommand="aspx_Get_Customer_DashboardGraphs"

            SelectCommandType="StoredProcedure"

            UpdateCommand="aspx_Update_Customer_DashboardGraphs"

            UpdateCommandType="StoredProcedure"

            >

            <UpdateParameters>

                    <asp:Parameter Name="CustomerID" Type="Int32" />

                    <asp:Parameter Name="Budget" Type="Int32" />

                    <asp:Parameter Name="CurrentMonthCollections" Type="Boolean" />

                    <asp:Parameter Name="RevenueByMonth" Type="Boolean" />

                    <asp:Parameter Name="PDCsCCsMonthly" Type="Boolean" />

                    <asp:Parameter Name="RevenueByClient" Type="Boolean" />                                      

            </UpdateParameters>  

 

        </asp:SqlDataSource>

My GridView:

        <asp:GridView

         ID="gv_dashboard"

         runat="server"

         AutoGenerateColumns="False"

         DataSourceID="ds_dashboard"

         CellPadding="4"

         ForeColor="#333333"

         ShowFooter="True"  

         GridLines="None"

         CssClass="FormatFont">

     

            <Columns>

                <asp:TemplateField HeaderText="Cust #" SortExpression="CustomerID">

                <HeaderStyle HorizontalAlign="center"></HeaderStyle>

                    <ItemTemplate>

                        <asp:Label runat="server" id="lblCustomerID" Text='<%# Bind("Customer") %>' />

                    </ItemTemplate>

                </asp:TemplateField>

                <asp:TemplateField HeaderText="Budget" SortExpression="Name" ItemStyle-HorizontalAlign="Center">

                  <HeaderStyle HorizontalAlign="Left"></HeaderStyle>

                     <ItemTemplate>

                        <asp:TextBox width="55px"  MaxLength="10" ID="txtBudget" Text='<%# Bind("Budget") %>' runat="server" />

                    </ItemTemplate>

                </asp:TemplateField>

                <asp:TemplateField HeaderText="Current Month Collections" SortExpression="Name" ItemStyle-HorizontalAlign="Center">

                 <HeaderStyle HorizontalAlign="center"></HeaderStyle>

                     <ItemTemplate>

                        <asp:checkbox runat="server" Id="chbx_CurrentMonthCollections" />

                    </ItemTemplate>

                </asp:TemplateField>

                <asp:TemplateField HeaderText="Revenue By Month" SortExpression="Name" ItemStyle-HorizontalAlign="Center">

                 <HeaderStyle HorizontalAlign="center"></HeaderStyle>

                     <ItemTemplate>

                        <asp:checkbox runat="server" Id="chbx_RevenueByMonth" />

                    </ItemTemplate>

                </asp:TemplateField>

                <asp:TemplateField HeaderText="PDCs & CCS Monthly" SortExpression="Name" ItemStyle-HorizontalAlign="Center">

                 <HeaderStyle HorizontalAlign="center"></HeaderStyle>

                     <ItemTemplate>

                        <asp:checkbox runat="server" Id="chbx_PDCsCCsMonthly" />

                    </ItemTemplate>

                </asp:TemplateField>

                <asp:TemplateField HeaderText="Revenue By Client" SortExpression="Name" ItemStyle-HorizontalAlign="Center">

                 <HeaderStyle HorizontalAlign="center"></HeaderStyle>

                     <ItemTemplate>

                        <asp:checkbox runat="server" Id="chbx_RevenueByClient" />

                    </ItemTemplate>

                </asp:TemplateField>

            </Columns>

       

            <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />

            <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />

            <EditRowStyle BackColor="#DDDDDD" />

            <SelectedRowStyle BackColor="#DDDDDD" Font-Bold="True" ForeColor="#333333" />

            <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />

            <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />

            <AlternatingRowStyle BackColor="White" ForeColor="#284775" />

        </asp:GridView>


My Update Command (Stored Procedure):

ALTER PROCEDURE [dbo].[aspx_Update_Customer_DashboardGraphs]

 

      @CustomerID                         bigint,

      @Budget                                   int,

      @CurrentMonthCollections      bit,

      @RevenueByMonth                     bit,

      @PDCsCCsMonthly                     bit,

      @RevenueByClient              bit

 

AS

BEGIN

 

      UPDATE dbo.Customer_DashboardGraphs

      SET Budget = @Budget,

            CurrentMonthCollections = @CurrentMonthCollections,

            RevenueByMonth = @RevenueByMonth,

            PDCsCCsMonthly = @PDCsCCsMonthly,

            RevenueByClient = @RevenueByClient

      WHERE Customer_DashboardGraphs.Customer = @CustomerID

END


My Table Schema:


CREATE TABLE [dbo].[Customer_DashboardGraphs](

      [Customer] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

      [DashboardGraphID] [bit] NULL,

      [CurrentMonthCollections] [bit] NULL,

      [RevenueByMonth] [bit] NULL,

      [PDCsCCsMonthly] [bit] NULL,

      [RevenueByClient] [bit] NULL,

      [Budget] [int] NULL

) ON [PRIMARY]


Stack Trace:

String was not recognized as a valid Boolean.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.FormatException: String was not recognized as a valid Boolean.

Source Error:


Line 53:                 ds_dashboard.UpdateParameters("Budget").DefaultValue = intBudget
Line 54:
Line 55:                 ds_dashboard.Update()
Line 56:             End If
Line 57:         Next
 

Source File: Y:\inetpub\wwwroot\apex\webapps\dashboard\dashboard\index.aspx.vb    Line: 55

Stack Trace:


[FormatException: String was not recognized as a valid Boolean.]
   System.Boolean.Parse(String value) +2709172
   System.String.System.IConvertible.ToBoolean(IFormatProvider provider) +12
   System.Convert.ChangeType(Object value, TypeCode typeCode, IFormatProvider provider) +98
   System.Web.UI.WebControls.Parameter.GetValue(Object value, String defaultValue, TypeCode type, Boolean convertEmptyStringToNull, Boolean ignoreNullableTypeChanges) +257
   System.Web.UI.WebControls.Parameter.get_ParameterValue() +91
   System.Web.UI.WebControls.ParameterCollection.GetValues(HttpContext context, Control control) +282
   System.Web.UI.WebControls.SqlDataSourceView.InitializeParameters(DbCommand command, ParameterCollection parameters, IDictionary exclusionList) +344
   System.Web.UI.WebControls.SqlDataSourceView.ExecuteUpdate(IDictionary keys, IDictionary values, IDictionary oldValues) +449
   System.Web.UI.WebControls.SqlDataSourceView.Update(IDictionary keys, IDictionary values, IDictionary oldValues) +37
   System.Web.UI.WebControls.SqlDataSource.Update() +42
   dashboard._Default.UpdateCustomer_DashboardGraphs(Object sender, EventArgs e) in Y:\inetpub\wwwroot\sss\webapps\dashboard\dashboard\index.aspx.vb:55
   System.Web.UI.WebControls.Button.OnClick(EventArgs e) +96
   System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +116
   System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +31
   System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +32
   System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +72
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +3837
0
dba123Author Commented:
let's try that last paste again!

Why am I getting this error: String was not recognized as a valid Boolean
For Line: ds_dashboard.Update() in my update function
My SQLDataSource:
        <asp:SqlDataSource
            ID="ds_dashboard"
            runat="server"
            ConnectionString="<%$ ConnectionStrings:DashboardConn %>"
            SelectCommand="aspx_Get_Customer_DashboardGraphs"
            SelectCommandType="StoredProcedure"
            UpdateCommand="aspx_Update_Customer_DashboardGraphs"
            UpdateCommandType="StoredProcedure"
            >
            <UpdateParameters>
                    <asp:Parameter Name="CustomerID" Type="Int32" />
                    <asp:Parameter Name="Budget" Type="Int32" />
                    <asp:Parameter Name="CurrentMonthCollections" Type="Boolean" />
                    <asp:Parameter Name="RevenueByMonth" Type="Boolean" />
                    <asp:Parameter Name="PDCsCCsMonthly" Type="Boolean" />
                    <asp:Parameter Name="RevenueByClient" Type="Boolean" />                                      
            </UpdateParameters>  
 
        </asp:SqlDataSource>
My GridView:
        <asp:GridView
         ID="gv_dashboard"
         runat="server"
         AutoGenerateColumns="False"
         DataSourceID="ds_dashboard"
         CellPadding="4"
         ForeColor="#333333"
         ShowFooter="True"  
         GridLines="None"
         CssClass="FormatFont">
     
            <Columns>
                <asp:TemplateField HeaderText="Cust #" SortExpression="CustomerID">
                <HeaderStyle HorizontalAlign="center"></HeaderStyle>
                    <ItemTemplate>
                        <asp:Label runat="server" id="lblCustomerID" Text='<%# Bind("Customer") %>' />
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="Budget" SortExpression="Name" ItemStyle-HorizontalAlign="Center">
                  <HeaderStyle HorizontalAlign="Left"></HeaderStyle>
                     <ItemTemplate>
                        <asp:TextBox width="55px"  MaxLength="10" ID="txtBudget" Text='<%# Bind("Budget") %>' runat="server" />
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="Current Month Collections" SortExpression="Name" ItemStyle-HorizontalAlign="Center">
                 <HeaderStyle HorizontalAlign="center"></HeaderStyle>
                     <ItemTemplate>
                        <asp:checkbox runat="server" Id="chbx_CurrentMonthCollections" />
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="Revenue By Month" SortExpression="Name" ItemStyle-HorizontalAlign="Center">
                 <HeaderStyle HorizontalAlign="center"></HeaderStyle>
                     <ItemTemplate>
                        <asp:checkbox runat="server" Id="chbx_RevenueByMonth" />
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="PDCs & CCS Monthly" SortExpression="Name" ItemStyle-HorizontalAlign="Center">
                 <HeaderStyle HorizontalAlign="center"></HeaderStyle>
                     <ItemTemplate>
                        <asp:checkbox runat="server" Id="chbx_PDCsCCsMonthly" />
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="Revenue By Client" SortExpression="Name" ItemStyle-HorizontalAlign="Center">
                 <HeaderStyle HorizontalAlign="center"></HeaderStyle>
                     <ItemTemplate>
                        <asp:checkbox runat="server" Id="chbx_RevenueByClient" />
                    </ItemTemplate>
                </asp:TemplateField>
            </Columns>
       
            <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
            <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
            <EditRowStyle BackColor="#DDDDDD" />
            <SelectedRowStyle BackColor="#DDDDDD" Font-Bold="True" ForeColor="#333333" />
            <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
            <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
            <AlternatingRowStyle BackColor="White" ForeColor="#284775" />
        </asp:GridView>
My Update Command (Stored Procedure):
ALTER PROCEDURE [dbo].[aspx_Update_Customer_DashboardGraphs]

      @CustomerID                              bigint,
      @Budget                                    int,
      @CurrentMonthCollections      bit,
      @RevenueByMonth                        bit,
      @PDCsCCsMonthly                        bit,
      @RevenueByClient                  bit

AS
BEGIN

      UPDATE dbo.Customer_DashboardGraphs
      SET Budget = @Budget,
            CurrentMonthCollections = @CurrentMonthCollections,
            RevenueByMonth = @RevenueByMonth,
            PDCsCCsMonthly = @PDCsCCsMonthly,
            RevenueByClient = @RevenueByClient
      WHERE Customer_DashboardGraphs.Customer = @CustomerID
END
My Table Schema:
CREATE TABLE [dbo].[Customer_DashboardGraphs](
      [Customer] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [DashboardGraphID] [bit] NULL,
      [CurrentMonthCollections] [bit] NULL,
      [RevenueByMonth] [bit] NULL,
      [PDCsCCsMonthly] [bit] NULL,
      [RevenueByClient] [bit] NULL,
      [Budget] [int] NULL
) ON [PRIMARY]
Stack Trace:

String was not recognized as a valid Boolean.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.FormatException: String was not recognized as a valid Boolean.

Source Error:


Line 53:                 ds_dashboard.UpdateParameters("Budget").DefaultValue = intBudget
Line 54:
Line 55:                 ds_dashboard.Update()
Line 56:             End If
Line 57:         Next
 

Source File: Y:\inetpub\wwwroot\apex\webapps\dashboard\dashboard\index.aspx.vb    Line: 55

Stack Trace:


[FormatException: String was not recognized as a valid Boolean.]
   System.Boolean.Parse(String value) +2709172
   System.String.System.IConvertible.ToBoolean(IFormatProvider provider) +12
   System.Convert.ChangeType(Object value, TypeCode typeCode, IFormatProvider provider) +98
   System.Web.UI.WebControls.Parameter.GetValue(Object value, String defaultValue, TypeCode type, Boolean convertEmptyStringToNull, Boolean ignoreNullableTypeChanges) +257
   System.Web.UI.WebControls.Parameter.get_ParameterValue() +91
   System.Web.UI.WebControls.ParameterCollection.GetValues(HttpContext context, Control control) +282
   System.Web.UI.WebControls.SqlDataSourceView.InitializeParameters(DbCommand command, ParameterCollection parameters, IDictionary exclusionList) +344
   System.Web.UI.WebControls.SqlDataSourceView.ExecuteUpdate(IDictionary keys, IDictionary values, IDictionary oldValues) +449
   System.Web.UI.WebControls.SqlDataSourceView.Update(IDictionary keys, IDictionary values, IDictionary oldValues) +37
   System.Web.UI.WebControls.SqlDataSource.Update() +42
   dashboard._Default.UpdateCustomer_DashboardGraphs(Object sender, EventArgs e) in Y:\inetpub\wwwroot\sss\webapps\dashboard\dashboard\index.aspx.vb:55
   System.Web.UI.WebControls.Button.OnClick(EventArgs e) +96
   System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +116
   System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +31
   System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +32
   System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +72
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +3837

0
dba123Author Commented:
Ok, I changed these since SQL Server bit is expecting true or false, not 1 or
zero...that would be for an integer db field:

                If intCurrentMonthCollections = True Then
                    ds_dashboard.UpdateParameters("CurrentMonthCollections").DefaultValue = 1
                Else
                    ds_dashboard.UpdateParameters("CurrentMonthCollections").DefaultValue = 0
                End If
to

                If intCurrentMonthCollections = True Then
                    ds_dashboard.UpdateParameters("CurrentMonthCollections").DefaultValue = True
                Else
                    ds_dashboard.UpdateParameters("CurrentMonthCollections").DefaultValue = False
                End If

The error goes away but it's still not updating those bit fields in my table
0
DBAduck - Ben MillerPrincipal ConsultantCommented:
OK, where do you set the Checked on the checkboxes?

<asp:checkbox runat="server" Id="chbx_RevenueByMonth" />

Shouldn't it be :

<asp:checkbox runat="server" Id="chbx_RevenueByMonth" Checked='<%# Bind("RevenueByMonth") %>' />

Everything else (Customer and Budget) are Bind("fieldname") so that the SQLDatasource can bind the results, etc.  But on the checkboxes they seem to be just unchecked all the time.  Did you check the data in the Table?  Are the bit fields 1 in the table?

Ben.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dba123Author Commented:
ahhhhhh....hold on...
0
dba123Author Commented:
should it also have an ID or just Checked= like you show?
0
dba123Author Commented:
nevermind, didn't read...hold on
0
dba123Author Commented:
An exception of type 'System.InvalidCastException' occurred in Microsoft.VisualBasic.dll but was not handled in user code

Additional information: Conversion from type 'DBNull' to type 'Boolean' is not valid.


                 <HeaderStyle HorizontalAlign="center"></HeaderStyle>
                     <ItemTemplate>
                        <asp:checkbox runat="server" Id="chbx_CurrentMonthCollections" Checked='<%# Bind("CurrentMonthCollections") %>'/>
                    </ItemTemplate>
                </asp:TemplateField>
0
DBAduck - Ben MillerPrincipal ConsultantCommented:
So first off, you should not have nulls in bit fields, but in the case that you do you can always default them to false coming out of the database.

So in your query, I would change the fields that are boolean and do this:

SELECT .... ISNULL(CurrentMonthCollections, 0) as CurrentMonthCollections, ...

So that if it is null then it will come back 0 and then you should be good to go.

Ben.
0
dba123Author Commented:
How do I have nulls in there, that's what I don't get.  This is a new table so there is gonna be nulls in there for the bit fields in the beginning.
0
dba123Author Commented:
so then should I pre poplulate all values in my table for the bits field sto zero so they don't contain nulls right off the bat?
0
dba123Author Commented:
>>So in your query, I would change the fields that are boolean and do this:

SELECT .... ISNULL(CurrentMonthCollections, 0) as CurrentMonthCollections, ...


so are you talking about my UpdateCommand query or SelectCommand query?
0
dba123Author Commented:
what is ISNULL(CurrentMonthCollections, 0) doing?
0
dba123Author Commented:
ok, cool.  I set the ISNULL to all incoming fields in my SelectCommand Query.  That got rid of the error.  Now when I update my form, it doesn't produce errors but it also doesn't update the data in my table.
0
dba123Author Commented:
Here's a recap of the code I have now...trying to figure out why with no errors, is it still not updating my table with the results:



        <asp:SqlDataSource
            ID="ds_dashboard"
            runat="server"
            ConnectionString="<%$ ConnectionStrings:DashboardConn %>"
            SelectCommand="aspx_Get_Customer_DashboardGraphs"
            SelectCommandType="StoredProcedure"
            UpdateCommand="aspx_Update_Customer_DashboardGraphs"
            UpdateCommandType="StoredProcedure"
            >
            <UpdateParameters>
                    <asp:Parameter Name="CustomerID" Type="Int32" />
                    <asp:Parameter Name="Budget" Type="Int32" />
                    <asp:Parameter Name="CurrentMonthCollections" Type="Boolean" />
                    <asp:Parameter Name="RevenueByMonth" Type="Boolean" />
                    <asp:Parameter Name="PDCsCCsMonthly" Type="Boolean" />
                    <asp:Parameter Name="RevenueByClient" Type="Boolean" />                                      
            </UpdateParameters>  
 
        </asp:SqlDataSource>


        <asp:GridView
         ID="gv_dashboard"
         runat="server"
         AutoGenerateColumns="False"
         DataSourceID="ds_dashboard"
         CellPadding="4"
         ForeColor="#333333"
         ShowFooter="True"  
         GridLines="None"
         CssClass="FormatFont">
     
            <Columns>
                <asp:TemplateField HeaderText="Cust #" SortExpression="CustomerID">
                <HeaderStyle HorizontalAlign="center"></HeaderStyle>
                    <ItemTemplate>
                        <asp:Label runat="server" id="lblCustomerID" Text='<%# Bind("Customer") %>' />
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="Budget" SortExpression="Name" ItemStyle-HorizontalAlign="Center">
                  <HeaderStyle HorizontalAlign="Left"></HeaderStyle>
                     <ItemTemplate>
                        <asp:TextBox width="55px"  MaxLength="10" ID="txtBudget" Text='<%# Bind("Budget") %>' runat="server" />
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="Current Month Collections" SortExpression="Name" ItemStyle-HorizontalAlign="Center">
                 <HeaderStyle HorizontalAlign="center"></HeaderStyle>
                     <ItemTemplate>
                        <asp:checkbox runat="server" Id="chbx_CurrentMonthCollections" Checked='<%# Bind("CurrentMonthCollections") %>'/>
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="Revenue By Month" SortExpression="Name" ItemStyle-HorizontalAlign="Center">
                 <HeaderStyle HorizontalAlign="center"></HeaderStyle>
                     <ItemTemplate>
                        <asp:checkbox runat="server" Id="chbx_RevenueByMonth" Checked='<%# Bind("RevenueByMonth") %>'/>
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="PDCs & CCS Monthly" SortExpression="Name" ItemStyle-HorizontalAlign="Center">
                 <HeaderStyle HorizontalAlign="center"></HeaderStyle>
                     <ItemTemplate>
                        <asp:checkbox runat="server" Id="chbx_PDCsCCsMonthly" Checked='<%# Bind("PDCsCCsMonthly") %>'/>
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="Revenue By Client" SortExpression="Name" ItemStyle-HorizontalAlign="Center">
                 <HeaderStyle HorizontalAlign="center"></HeaderStyle>
                     <ItemTemplate>
                        <asp:checkbox runat="server" Id="chbx_RevenueByClient" Checked='<%# Bind("RevenueByClient") %>'/>
                    </ItemTemplate>
                </asp:TemplateField>
            </Columns>
       
            <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
            <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
            <EditRowStyle BackColor="#DDDDDD" />
            <SelectedRowStyle BackColor="#DDDDDD" Font-Bold="True" ForeColor="#333333" />
            <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
            <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
            <AlternatingRowStyle BackColor="White" ForeColor="#284775" />
           
        </asp:GridView>


Stored proc behind the Update Command:

ALTER PROCEDURE [dbo].[aspx_Update_Customer_DashboardGraphs]

      @CustomerID                              bigint,
      @Budget                                    bigint,
      @CurrentMonthCollections      bit,
      @RevenueByMonth                        bit,
      @PDCsCCsMonthly                        bit,
      @RevenueByClient                  bit

AS
BEGIN

      UPDATE dbo.Customer_DashboardGraphs
      SET Budget = @Budget,
            CurrentMonthCollections = @CurrentMonthCollections,
            RevenueByMonth = @RevenueByMonth,
            PDCsCCsMonthly = @PDCsCCsMonthly,
            RevenueByClient = @RevenueByClient
      WHERE Customer_DashboardGraphs.Customer = @CustomerID
END

------------------------------------- Code Behind -------------------------------------------------------------

    Public Sub UpdateCustomer_DashboardGraphs(ByVal sender As Object, ByVal e As System.EventArgs)
        For Each gvr As GridViewRow In gv_dashboard.Rows
            If gvr.RowType = DataControlRowType.DataRow Then

                '//--IDs

                Dim intCustomerID As String = CType(gvr.FindControl("lblCustomerID"), Label).Text.Trim()

                '//--Textboxes
                Dim intBudget As Integer
                Try
                    intBudget = CType(CType(gvr.FindControl("txtBudget"), TextBox).Text.Trim(), Int32)
                Catch
                    intBudget = 0
                End Try

                '//--Checkboxes
                Dim bolCurrentMonthCollections As Boolean = CType(gvr.FindControl("chbx_CurrentMonthCollections"), CheckBox).Checked
                Dim bolRevenueByMonth As Boolean = CType(gvr.FindControl("chbx_RevenueByMonth"), CheckBox).Checked
                Dim bolPDCsCCsMonthly As Boolean = CType(gvr.FindControl("chbx_PDCsCCsMonthly"), CheckBox).Checked
                Dim bolRevenueByClient As Boolean = CType(gvr.FindControl("chbx_RevenueByClient"), CheckBox).Checked

                '//--Assign UpdateParameters
                If bolCurrentMonthCollections = True Then

                    ds_dashboard.UpdateParameters("CurrentMonthCollections").DefaultValue = True
                Else
                    ds_dashboard.UpdateParameters("CurrentMonthCollections").DefaultValue = False
                End If
                If bolRevenueByMonth = True Then
                    ds_dashboard.UpdateParameters("RevenueByMonth").DefaultValue = True
                Else
                    ds_dashboard.UpdateParameters("RevenueByMonth").DefaultValue = False
                End If
                If bolPDCsCCsMonthly = True Then
                    ds_dashboard.UpdateParameters("PDCsCCsMonthly").DefaultValue = True
                Else
                    ds_dashboard.UpdateParameters("PDCsCCsMonthly").DefaultValue = False
                End If
                If bolRevenueByClient = True Then
                    ds_dashboard.UpdateParameters("RevenueByClient").DefaultValue = True
                Else
                    ds_dashboard.UpdateParameters("RevenueByClient").DefaultValue = False
                End If

                ds_dashboard.UpdateParameters("Budget").DefaultValue = intBudget

                ds_dashboard.Update()
            End If
        Next
    End Sub

0
DBAduck - Ben MillerPrincipal ConsultantCommented:
It says in the SelectCommand to make the return a 0 if the field value in the database table is a NULL.  When the error was thrown and it said that you cannot set the value of a Checked field to DBNull, that meant that it encountered a NULL in the data coming back.

So in aspx_Get_Customer_DashboardGraphs you could go in and change the CurrentMonthCollections to ISNULL(CurrentMonthCollections, 0) as CurrentMonthCollections and so forth for all bit fields.

The other way to do it is to use the function in the code to do it.

Change to
<asp:checkbox runat="server" Id="chbx_CurrentMonthCollections" Checked='<%# GetBooleanValue(Bind("CurrentMonthCollections")) %>'/>

Then in your code

Private Function GetBooleanValue(boolValue as object) as Boolean
    If boolValue = DBNull.Value Then
        Return False
    Else
        Return CType(boolValue, Boolean)
    End If
End Function

If you add the code and do the above, then you would NOT change the stored procedure, you would just leave it as is.  I am not sure that the Bind() works inside a function call, but if it does not then you will want to put it as Eval( ) instead of Bind( ).

Ben.
0
dba123Author Commented:
a few questions to try and troubleshoot this more.

example, should this be type Boolean?  I assume yes but not sure exactly what value is returned (true, false, "true", or "false") by VB.NET

                Dim bolRevenueByClient As Boolean = CType(gvr.FindControl("chbx_RevenueByClient"), CheckBox).Checked

should this be set to "true" or "false", or true or false, or 1 or 0.  I know that 1 or 0 causes issues

                    ds_dashboard.UpdateParameters("CurrentMonthCollections").DefaultValue = True
                Else
                    ds_dashboard.UpdateParameters("CurrentMonthCollections").DefaultValue = False
                End If

how and what is being passed to my SQL DB table which has type bit for these fields?
0
DBAduck - Ben MillerPrincipal ConsultantCommented:
So when you do a .Checked it returns True or False.

Same when you try to set the .Checked to something.  All that will work is True and False and they are in fact Boolean.

When you bring things from SQL Server into VB.NET it will change the 0 or 1 that is actually stored in the table (because 0 or 1 are the only possible values for a bit field) and change it to a True or False, not "true" or "false".  So when putting things back, the UpdateParameters wants a Boolean (True or False) instead of a 1 or a 0 or a string and it will put it in correctly to the table as a 0 or a 1.

So the easiest way is to use the function to detect the DBNull.Value and otherwise return what is in the table.

Make sense?

Ben.
0
dba123Author Commented:
so in other words I should be doing:


               ds_dashboard.UpdateParameters("CurrentMonthCollections").DefaultValue = 1
                Else
                    ds_dashboard.UpdateParameters("CurrentMonthCollections").DefaultValue = 0
                End If

but that throws errors...saying it's not the right type for boolean
0
dba123Author Commented:
Also, If I open my table in SQL Management Studio in SQL 2005 and type in 1 for one of the bit fields, it tells me this:

Invalid Value for Cell
The changed value in this cell was not recognized as valid
.Net Framework Data Type: Boolean
Error Message: String was not recognized as a valid Boolean

ahh, so this explains it.  But you were saying that it can only hold a 1 or 0.  I agree with you as I have worked with bit in the past.  But the only thing I can type in the DB directly, or update it with is the text True or False.
0
dba123Author Commented:
so this should be right:

               ds_dashboard.UpdateParameters("CurrentMonthCollections").DefaultValue = True
                Else
                    ds_dashboard.UpdateParameters("CurrentMonthCollections").DefaultValue = False

It looks like SQL Server bit type doesn't hold 1 or 0 litrally now, it holds True or False.
                End If
0
dba123Author Commented:
so getting back to the root of my problem now...being that my latest post is not updating my table....that long post that shows you my latest code.  Why is nothing being updated if no errors are occuring?  Is it something with my update statement in the UpdateCommand or what I'm passing to it ? (True or False) ?  The budget txt field isn't even updating either.
0
DBAduck - Ben MillerPrincipal ConsultantCommented:
OK, but where is your CustomerId parameter?

You have
Dim intCustomerID As String = CType(gvr.FindControl("lblCustomerID"), Label).Text.Trim()

But nowhere do I see you setting that Parameter up.

ds_dashboard.UpdateParameters("CustomerID").DefaultValue = intCustomerID

That would be one reason why the update is not happening.

Ben.
0
dba123Author Commented:
Thanks for that, that was it, missing the customer ID.
0
DBAduck - Ben MillerPrincipal ConsultantCommented:
Wow, all that and we missed the CustomerID Parameter.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.