[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

String was not recognized as a valid Boolean

Posted on 2006-04-24
30
Medium Priority
?
8,953 Views
Last Modified: 2008-01-09
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
0
Comment
Question by:dba123
  • 22
  • 7
30 Comments
 
LVL 12

Expert Comment

by:bsdotnet
ID: 16530739
what is the value of intBudget? If it's either "0" or "1", try using "True" or "False".
0
 
LVL 1

Author Comment

by:dba123
ID: 16530753
no, intBudget is a textbox
0
 
LVL 25

Expert Comment

by:DBAduck - Ben Miller
ID: 16531206
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 1

Author Comment

by:dba123
ID: 16533769
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
 
LVL 1

Author Comment

by:dba123
ID: 16533799
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
 
LVL 1

Author Comment

by:dba123
ID: 16533851
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
 
LVL 1

Author Comment

by:dba123
ID: 16533855
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
 
LVL 1

Author Comment

by:dba123
ID: 16534027
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
 
LVL 25

Accepted Solution

by:
DBAduck - Ben Miller earned 2000 total points
ID: 16534583
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
 
LVL 1

Author Comment

by:dba123
ID: 16534718
ahhhhhh....hold on...
0
 
LVL 1

Author Comment

by:dba123
ID: 16534726
should it also have an ID or just Checked= like you show?
0
 
LVL 1

Author Comment

by:dba123
ID: 16534738
nevermind, didn't read...hold on
0
 
LVL 1

Author Comment

by:dba123
ID: 16534764
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
 
LVL 25

Expert Comment

by:DBAduck - Ben Miller
ID: 16534955
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
 
LVL 1

Author Comment

by:dba123
ID: 16534993
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
 
LVL 1

Author Comment

by:dba123
ID: 16535000
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
 
LVL 1

Author Comment

by:dba123
ID: 16535013
>>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
 
LVL 1

Author Comment

by:dba123
ID: 16535019
what is ISNULL(CurrentMonthCollections, 0) doing?
0
 
LVL 1

Author Comment

by:dba123
ID: 16535101
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
 
LVL 1

Author Comment

by:dba123
ID: 16535134
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
 
LVL 25

Expert Comment

by:DBAduck - Ben Miller
ID: 16535137
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
 
LVL 1

Author Comment

by:dba123
ID: 16535321
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
 
LVL 25

Expert Comment

by:DBAduck - Ben Miller
ID: 16535350
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
 
LVL 1

Author Comment

by:dba123
ID: 16535409
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
 
LVL 1

Author Comment

by:dba123
ID: 16535434
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
 
LVL 1

Author Comment

by:dba123
ID: 16535439
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
 
LVL 1

Author Comment

by:dba123
ID: 16535458
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
 
LVL 25

Expert Comment

by:DBAduck - Ben Miller
ID: 16535559
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
 
LVL 1

Author Comment

by:dba123
ID: 16537485
Thanks for that, that was it, missing the customer ID.
0
 
LVL 25

Expert Comment

by:DBAduck - Ben Miller
ID: 16539660
Wow, all that and we missed the CustomerID Parameter.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article discusses the ASP.NET AJAX ModalPopupExtender control. In this article we will show how to use the ModalPopupExtender control, how to display/show/call the ASP.NET AJAX ModalPopupExtender control from javascript, how to show/display/cal…
One of the pain points with developing AJAX, JavaScript, JQuery, and other client-side behaviors is that JavaScript doesn’t allow for cross domain request for pulling content. For example, JavaScript code on www.johnchapman.name could not pull conte…
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question