Hi All,

I'm really stumped by this one. I have an ItemDataBound Sub that totals the columns and writes the sum to the footer. That part is working fine and I'm able to format the data and align it to the center of the cell. But in the AlternatingItem and Item I cannot format the data. I keep getting "Input string is in correct format". I can align it in the cell fine with Attributes.Add.

Thank you for your response. Sorry if this has already been covered before. I searched this topic and was unable to find anything.

Much appreciated,
Richard
    Sub grdSummary_ItemDataBound(ByVal s As Object, ByVal e As DataGridItemEventArgs) Handles grdTotals.ItemDataBound
        If e.Item.ItemType = ListItemType.Footer Then
            Dim i As Integer
            Dim n As Integer
            For i = 0 To grdTotals.Items.Count - 1
                n = grdTotals.Items.Item(i).Cells.Count - 1
            Next
            Dim x(n) As Integer
            'This part for the footer formatting is working fine. It aligns the text into the center of the table cell and if the number is over 1,000 it adds the comma.
            For i = 0 To grdTotals.Items.Count - 1
                For n = 1 To grdTotals.Items.Item(i).Cells.Count - 1
                    x(n) += CInt(grdTotals.Items.Item(i).Cells(n).Text)
                    e.Item.Cells(n).Text = Format(x(n), "##,###")
                    e.Item.Cells(n).Attributes.Add("align", "center")
                Next
            Next
            e.Item.Cells(0).Text = "Total"
        ElseIf e.Item.ItemType = ListItemType.AlternatingItem Or e.Item.ItemType = ListItemType.Item Then
            Dim n As Integer
            'Here's where I'm having the problem. The centering (attributes.Add) is working fine but I cannot get the comma formatting on the numbers to work. You can see all of the things that I've tried.
            For n = 1 To e.Item.Cells.Count - 1
                e.Item.Cells(n).Attributes.Add("align", "center")
                'e.Item.Cells(n).Text = Format(CInt(e.Item.Cells(n).Text), "##,###") Input string was not in a correct format.
                'e.Item.Cells(n).Text.Format("##,###", e.Item.DataItem)Input string was not in a correct format.
                'e.Item.Cells(n).Text = String.Format("{0:##,###}", e.Item.Cells(n).Text)Input string was not in a correct format.
            Next

'e.Item.Cells(2).Text = Format(CInt(e.Item.Cells(1).Text), "##,###")

'I tried just hard coding the column. This one doesn't error out but it doesn't do the formatting either.

e.Item.Cells(2).Text.Format("##,###")
End If
 End Sub

When you debug the application what do you get when you put a watch on e.Item.Cells(n) or e.Item.Cells(n).Text?

Maybe there is another inner tag inside the cell, so it cannot be cast to an Int directly?

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

Hi Imar,

OK.. Thanks for the response. I'm getting my bottles of acquavit lined up now. I might just have to send some paypal cash and have you buy it. The postage is getting to be too high.

I watched e.Item.Cells(n).Text with this line in the Item loop:

e.Item.Cells(n).Text = Format("##,###", e.Item.Cells(n).Text)

All the data was good, meaning all of the NULLs had been replaced by "0" and otherwise the correct number value was showing up.

It didn't bomb out but it did not format the numbers. It just did nothing.

If I use:

e.Item.Cells(n).Text = Format( e.Item.Cells(n).Text, "##,###")

When I debug e.Item.Cells(n).Text it sets the value to "##,###"

and the stack trace says:

[InvalidCastException: Cast from string "##,###" to type 'Integer' is not valid.]
   Microsoft.VisualBasic.CompilerServices.IntegerType .FromString(String Value) +212

This one is hurting. And for some reason, like I said, this works when formatting the footer:

e.Item.Cells(n).Text = Format(x(n), "##,###")

I have no idea what the problem is here.

Thanks again,
Richard

Strange. We must be overlooking something....

What happens when you use String.Format instead of VB's Format function?

If you set a break point on this line:

e.Item.Cells(n).Text = Format( e.Item.Cells(n).Text, "##,###")

before the line is executed, does e.Item.Cells(n).Text contain a valid number on each iteration?

Maybe some cells work, and some don't. For example, if you have an update or delete button in one of your columns, the code will crash....

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

Oh, BTW, thanks for the offer for acquavit. Does that contain any alcohol?? ;)

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

I looked at all the values for e.Item.Cells(n).Text. There is a valid number on each iteration. No buttons in the columns. Its madddddening. Oh well, OK, I'll just start writing reports in Crystal Reports then.

I heard they tried to make a non-alcoholic acquavit and there was a riot in Oslo. So much for that idea. So they wanted to give the Nobel Peace Prize to the guy who put the alcohol back into acquavit. Was that you?

This one does nothing - no error - does not apply format
e.Item.Cells(n).Text = String.Format("{0:##,###}", e.Item.Cells(n).Text)

This one errors out
sets the value of e.Item.Cells(n).Text to ##,###
e.Item.Cells(n).Text = String.Format("##,###", e.Item.Cells(n).Text)

[FormatException: Input string was not in a correct format.]
   Microsoft.VisualBasic.CompilerServices.DoubleType. Parse(String Value, NumberFormatInfo NumberFormat) +195
   Microsoft.VisualBasic.CompilerServices.IntegerType .FromString(String Value) +97

[InvalidCastException: Cast from string "##,###" to type 'Integer' is not valid.]


This one does nothing - no error - doesn not apply the format
 e.Item.Cells(n).Text = Format("##,###", e.Item.Cells(n).Text)

It looks like you keep turning the object to format, and the format type around.

It should be:

String.Format(objectYoWantToFormat, style)

e.Item.Cells(n).Text = String.Format(e.Item.Cells(n).Text, "##,###")

but I think you already tried that, didn't you?

And no, it wasn't me, although I fully support the idea.... ;)


Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

haha,... good one

Hey, yeah, I tried that one. It doesn't error. It doesn't apply the format to the number either.

I just don't get this one since its formatting the footer fine. When I use the same syntax that I use on formatting the footer I get an error when using it in the Item.

Oh well... its not critical... The report would look better if it worked. I just like to know why things don't work.... Uh,... I like everything to work...

Thanks Imar... very much appreciated... If you ever come across a solution, let me know and if I find out here what's up with this thing, I'll post it here for sure.

Hey Imar,

I'm wondering if this problem has something to do with how the columns are defined here:

   Dim i As Integer
            For i = 0 To oDS.Tables(0).Columns.Count - 1
                Dim oDC As New BoundColumn
                oDC.DataField = oDS.Tables(0).Columns(i).ColumnName
                oDC.HeaderText = oDS.Tables(0).Columns(i).ColumnName
                grdTotals.Columns.Add(oDC)
            Next
            grdTotals.DataSource = oDS
            grdTotals.DataBind()

Can you define the data type in a bound column?

I've been messing around with trying to define the data type using a datatable and adding columns and defining the data type but don't know how to wire-up the datatable to the dataset with the defined columns.

Here's what I started with that works fine, except when I try to format the columns.
           Dim oDA As New SqlDataAdapter(SQL, _oConn)
            Dim oDS As New DataSet
            oDA.Fill(oDS)
            Dim i As Integer
            For i = 0 To oDS.Tables(0).Columns.Count - 1
                Dim oDC As New BoundColumn
                oDC.DataField = oDS.Tables(0).Columns(i).ColumnName
                oDC.HeaderText = oDS.Tables(0).Columns(i).ColumnName
                grdTotals.Columns.Add(oDC)
            Next
            grdTotals.DataSource = oDS
            grdTotals.DataBind()

Here's where I've been trying to define the data type using a datatable.

         Dim oDA As New SqlDataAdapter(SQL, _oConn)
            Dim oDS As New DataSet
            Dim oDT As New DataTable
            oDA.Fill(oDS)
            Dim n As Integer
            For n = 1 To oDS.Tables(0).Columns.Count - 1
                oDT.Columns.Add(New DataColumn(oDS.Tables(0).Columns(n).ColumnName, GetType(Integer)))
            Next
            Dim i As Integer
            For i = 0 To oDS.Tables(0).Columns.Count - 1
                Dim oDC As New BoundColumn
                oDC.DataField = oDS.Tables(0).Columns(i).ColumnName
                oDC.HeaderText = oDS.Tables(0).Columns(i).ColumnName
                grdTotals.Columns.Add(oDC)
            Next
            grdTotals.DataSource = oDS
            grdTotals.DataBind()

Of course this doesn't error out now because I can see its not being wired to the Dataset. Every time I try to wire it up to the dataset I get an error that says, "datatable already belongs to this dataset." I don't even know if this is even going in the right direction. Just for grins, here's the actual data that I've been trying to format.

Component,Accessible,Inaccessible,Subtotal
Atmospheric PRDs,8.0,6.0,14.0
Compressors,14.0,0.0,14.0
Drains,230.0,0.0,230.0
Flanges,8298.0,1485.0,9783.0
Others,1677.0,249.0,1926.0
Pumps,160.0,0.0,160.0
Threaded Connections,21641.0,3307.0,24948.0
Valves,5645.0,614.0,6259.0

Your response is very much appreciated.

Thanks,
Richard