Can't you use the DataTable as the DataSource for the control directly?

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

Hi Imar,

Everyone is smart, except for Richard.

I suppose its possible. I just don't know how to do it.

The only reason I was trying to use the datatable is because I thought I might be able to define the data type in the column. The format error I'm getting is actually in this sub when it binds the data. So I'm thinking that the data type in the dataset is assigning the data type as String and not allowing the format function to format the string as "##,###".

       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() 'Error on this line when trying to apply formatting to the Item.

I actually took this completely out of the sub and it works just fine, of course without the formatting of the Item data. Not even sure why that was in there.

        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


Anyway, Imar, you've been so kind. Thank you. This problem has just been maddening. I think I'll buy myself the acquavit. You can join me of course.

Thanks,
Richard

One more thing you can try (although I am really surprised the formatting doesn't work. I don't think I have seen the complete code, so again, I might be overlooking something).

Anyway, there is one more thing you can try. Create a TemplateColumn and add labels / text boxes / whatever you need in the template. Then set the Text property to something like:

Text='<%# FormatStuff(DataBinder.Eval("ColumnName") %>'

Then define FormatStuff as a String function that accepts an object and have it return the formatted data:

Public Function FormatStuff (ByVal theValue As Object) As String
  Return String.Format(theValue.ToString(), "##.###")
End Function

Would that work for you?

Imar - sipping away my acquavit now. Thanks


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

Thanks Imar. Are you feeling the effects yet???

You know. I'm think I'm going to go that way. I knew about that but everything in this app is going the direction to be dynamic in so many aspects. That's why I'm trying to understand how to accomplish those things at runtime since I won't always know what columns will be created. I know that after the first label column that the data in the Item and Alternating Item will be integer/float data. I've looked at this data and its all good, just won't format as an Integer. It insists on being a string and I'm not sure why.

I haven't posted all of the code cause its quite involved and didn't know if it would really help.

I am kinda bugged why its not possible to define the datatype here and if not how you would do it.

    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

I have a feeling its possible, I just don't even know where to look or how to accomplish this.

And I'm wondering why no one else here hasn't been interested in this topic.

Anyway, you've been a big help as usual.

Everyone is smart, except for me.

When I have some time, I'll try to reproduce this as I am quite intrigued....

In the mean-time, maybe someone else can jump in with an opinion??

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

Hi Imar,

I'm very intrigued by this problem myself. It hasn't been as important to solve this mystery for the site to work as it is to just solve the problem. I'd like to post the entire pertinent code here in case there is something in there that might stand out at once.

Here is the data directly from the SQL query that is generated by the getRuleDef() sub. The data looks good.

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

This sub generates the data grid. It fires a function that retrieves a dynamic SQL statement, getRuleDef(). I'll post that function just in case. This is where the error is thrown when I try to format the Item and Alternating Items in the Handles sub for this data grid.

    Sub getTotals(ByVal s As Object, ByVal e As EventArgs) Handles btnSearch.Click
        _oConn.Open()
        If Not CheckDate(txtInvDate.Text) Then
            Session("Alert") = "Please provide a valid date (ex: " & Now.ToShortDateString & ")"
        Else
            Dim SQL As String = "SELECT ComponentTypes.CompTypeName AS [Component], " & getRuleDef() & "FROM ComponentTypes WHERE ComponentTypes.Status = 1 AND CompTypeID IN (SELECT CompTypeID FROM RuleComponentTypes WHERE RuleID = " & _RuleID & ") ORDER BY [Component];"
            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()
        End If
        _oConn.Close()
    End Sub

This sub handles the data bound and SUMS the columns for the footer. This is also where I was trying to format the Item and Alternating Item as "##,###". It also has everything that I tried commented out. You can see here though that the formatting works and does not through an error when formatting the footer summed data.[red]When I get errors its actually not in this sub but in the sub above at grdTotals.Databind().[/red]

    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
            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")
                    If Len(e.Item.Cells(n).Text) = 0 Then
                        e.Item.Cells(n).Text = "0"
                    End If
                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
            For n = 1 To e.Item.Cells.Count - 1
                e.Item.Cells(n).Attributes.Add("align", "center")
                'If Len(e.Item.Cells(n).Text) = 0 Then
                ' e.Item.Cells(n).Text = "0"
                'End If
                'Dim mm As Integer
                'mm = CInt(e.Item.Cells(n).Text)
                'If IsNumeric(e.Item.Cells(n).Text) Then
                ' e.Item.Cells(n).Text = Format(CType((mm), Integer), "##,###")
                'End If
                'Response.Write("<br>Cell Data: " & Format(e.Item.Cells(n).Text, "##,###"))
                'e.Item.Cells(n).Text = String.Format("{##,###}", e.Item.Cells(n).Text)
                ' e.Item.Cells(n).Text = String.Format(e.Item.Cells(n).Text, "##,###")
                'e.Item.Cells(n).Text.Format("##,###", e.Item.DataItem)
                'e.Item.Cells(n).Text = String.Format("{0:##,###}", e.Item.Cells(n).Text)
                ' response.write("n=" & n & ", text=" & e.item.cells( n ).text)
                'e.Item.Cells(n).Text = Format(mm, "##,###")
                'e.Item.Cells(n).Text = Format(CType((e.Item.Cells(n).Text), Integer), "##,###")
            Next
            'e.Item.Cells(2).Text = Format(CInt(e.Item.Cells(1).Text), "##,###")
            'e.Item.Cells(2).Text.Format("##,###")
        ElseIf e.Item.ItemType = ListItemType.Header Then
            Dim n As Integer
            For n = 1 To e.Item.Cells.Count - 1
                e.Item.Cells(n).Attributes.Add("align", "center")
            Next
        End If
    End Sub

This last sub may or may not have anyting to do with the problem but I thought that I would include it anyway. It builds the SQL query dynamically based on data in a table in the DB. The summing works, meaning it correctly sums the columns and does not through any errors.

    Function getRuleDef() 'As String
        Dim Value As String
        Dim Subtotal As String
        Dim SQL As String = "SELECT RuleDefName, RuleDefDesc FROM RuleDefinitions WHERE RuleID = " & _RuleID & " AND RuleDefTotal = 1 ORDER BY RuleDefDesc;"
        Dim oRS As SqlDataReader = New SqlCommand(SQL, _oConn).ExecuteReader
        Do While oRS.Read
            Value += "ISNULL((SELECT SUM(Inventory." & oRS("RuleDefName") & ") FROM Inventory INNER JOIN Locations ON Inventory.LocID = Locations.LocID INNER JOIN Subareas ON Locations.SubID = Subareas.SubID INNER JOIN Areas ON Subareas.AreaID = Areas.AreaID WHERE Areas.FacID = " & _FacID & " AND Inventory.RuleID = " & _RuleID & " "
            If ddlAreaID.SelectedValue > 0 Then
                Value += "AND Areas.AreaID = " & ddlAreaID.SelectedValue & " "
                If ddlSubID.SelectedValue > 0 Then
                    Value += "AND Subareas.SubID = " & ddlSubID.SelectedValue & " "
                    If ddlLocID.SelectedValue > 0 Then
                        Value += "AND Locations.LocID = " & ddlLocID.SelectedValue & " "
                    End If
                End If
            End If
            Value += "AND Inventory.CompTypeID = ComponentTypes.CompTypeID AND '" & txtInvDate.Text & " " & Now.ToLongTimeString & "' BETWEEN Inventory.InvStart AND ISNULL(Inventory.InvEnd, DATEADD(d, 1, GETDATE()))), 0) AS [" & oRS("RuleDefDesc") & "], "
            Subtotal += "ISNULL((SELECT SUM(Inventory." & oRS("RuleDefName") & ") FROM Inventory INNER JOIN Locations ON Inventory.LocID = Locations.LocID INNER JOIN Subareas ON Locations.SubID = Subareas.SubID INNER JOIN Areas ON Subareas.AreaID = Areas.AreaID WHERE Areas.FacID = " & _FacID & " AND Inventory.RuleID = " & _RuleID & " "
            If ddlAreaID.SelectedValue > 0 Then
                Subtotal += "AND Areas.AreaID = " & ddlAreaID.SelectedValue & " "
                If ddlSubID.SelectedValue > 0 Then
                    Subtotal += "AND Subareas.SubID = " & ddlSubID.SelectedValue & " "
                    If ddlLocID.SelectedValue > 0 Then
                        Subtotal += "AND Locations.LocID = " & ddlLocID.SelectedValue & " "
                    End If
                End If
            End If
            Subtotal += "AND Inventory.CompTypeID = ComponentTypes.CompTypeID AND '" & txtInvDate.Text & " " & Now.ToLongTimeString & "' BETWEEN Inventory.InvStart AND ISNULL(Inventory.InvEnd, DATEADD(d, 1, GETDATE()))), 0) + "
        Loop
        oRS.Close()
        Return Value & Subtotal & "0 AS [Subtotal] "
    End Function

Hi Richard,

Couldn't resist, so I finally made some time to look at this.

I tried a couple of things with various results:

First, I tried this:

Try
  Dim myDouble As Double = Convert.ToDouble(e.Item.Cells(n).Text)
  e.Item.Cells(n).Text = myDouble.ToString("N")
Catch ex As Exception

End Try

I was a bit lazy so I just wrapped the conversion in a Try Catch block (as some of my columns contain text). However, you could solve this more elegantly.

With this code, I was able to properly change the formatting of the text.
I also tried a few others:

e.Item.Cells(n).Text = String.Format("{0:##.###}", myDouble)
e.Item.Cells(n).Text = String.Format("{0:##.###}", e.Item.Cells(n).Text)
e.Item.Cells(n).Text = String.Format("{0:N}", myDouble)
e.Item.Cells(n).Text = String.Format("{0:N}", e.Item.Cells(n).Text)

The ones where I converted the Text property to a Double were successful, while the others weren't. I think that makes sense, as the Text versions are just seen as text.

I think, however, there is a much easier way: use the DataFormatString property of the BoundColumn, like this: This formats each column as a number with a thousand separator and two decimals.

To make this a little more flexible and generic, you can determine the type at run-time and format accordingly: This code uses DataType.Name to determine the type of the column being added. Just as an example, all String types are prefix with the text "I am a string: ". Integers are formatted with 4 decimals and Decimals with 2.
There may be more elegant ways to determine the type, but this one at least works....

Hope this helps,

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

Hi Imar,

That is really awesome. I can't tell you how much I appreciate your work on this. Thank you so much.

I plugged the code in and it worked right off, except that the data type I had was Double so I just added that to the Select Case. And of course I removed the 'I am a string: ' label for "String". Even though I'm only formatting the Double here, I'll just leave that code block intact since I can just copy the whole block to use elsewhere, whenever.


Also, thanks for the data conversion attempts since I need to actually convert some data elsewhere in the app. That will come in very helpful too.

I'm glad this one is solved. And again, my sincerest appreciation for your determination.

Richard

You're welcome. Glad it's working now as it's supposed to....

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