format autogenerated column in gridview 2

[cojiro]

I'm trying to format an autogenerated column in a gridview and I'm not sure why my current code isn't working. My results display without error, but without any formatting.

Please help!

Here's the code:

GridViewInceptionToDateExpenseReport_RowDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles GridViewInceptionToDateExpenseReport.RowDataBound
        Dim i As Integer
        For i = 0 To 7

            e.Row.Cells(i).Text = Format("{0:c}", e.Row.Cells(i).Text)

        Next i
    End Sub

 

[jmeckley]

because text is just that, text. formating only works with numbers. use the data source (e.Row.DataView) to get at the data, not the text.

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190
faq732-7259

 

[cojiro]

Jason,
I appreciate the response. I've tried seraching for a good coding example on the internet, but have come up short. Can you help me out with what I'll need to code to get this formatting working?

Thanks!

 

[jmeckley]

the problem with autogenerated *anything* is that you loose control of what's happening or how it's happening. It also doesn't leave room to inject functionality into the pipeline.

turn off autogenerate columns. then define which columns you want to display. set the format properties of the value(s) when binding.

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190
faq732-7259

 

[cojiro]

That's what I usually do, but I ran into this sitution due to some strange user requirements. The report I'm trying to display in this gridview is the output of an access crosstab query that has an uncertain amount of columns. Having autogenerated columns is the only way I can think to display this information. It works surprisingly well too, except for the formatting. Should I be doing this a different way?

As always, I appreciate your time with this.

 

[jmeckley]

your running a website with access on the backend? Beware that you may run into locking file and database connection issues if this is a high volume site. Access is not meant for more than a handful of concurrent users.

one option is to format the value in the query, if possible, returning a string instead of a number.

another option (although this feels like a hack to me)

e.Row.Cells(i).Text = Format("{0:c}", Convert.ToDouble(e.Row.Cells(i).Text))

either one of those will most likely be a quick fix. however you still have the issue of maintaining the code moving forward. Not sure about you, but I would hate to run across this code. It's not explicit in it's intent.

the alternative is a drastic departure from this approach.
1. fetch the data using some simple select statments:
select * from table where ...
2. aggregate the data into an object graph in code.
3. through a series of "service" objects transform the object graph into table metadata and tabular data.
4. dynamically build the html table strcture with the metadata.
5. populate the html table with the tabular data.

you would end up with many small classes; each with a single responsibility. composed together you can then output your table.

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190
faq732-7259

 

[cojiro]

Jason, thanks again for your response. So, it may be a hack, but I'd love to use this code:

Format("{0:c}", Convert.ToDouble(e.Row.Cells(i).Text))

Unfortunately, the "null" fields that are being passed are causing this to error out. I get this error: "Input string was not in a correct format".

I tried to use an if statement to skip the "null" fields, but I can't identify them. Apparently 6 mystery characters are being passed in each null field. At least that's what it said when I used the following code:

 e.row.cells(i).text = len(trim(e.row.cells(i).text))

Is there a way I can get around this error? Is there any way I can identify these 6 mystery characters, so I can skip them when I attempt to format my output?

Thanks!

 

[ca8msm]

You'll need to make sure that the value can actually be converted to a double prior to passing it in for formatting. Have a look at the help files for the Double.TryParse method for an example.

Mark,

Darlington Web Design
Experts, Information, Ideas & Knowledge
ASP.NET Tips & Tricks

 

[cojiro]

Good call Mark! In case you all are interested, here's the final code that worked for me:

    Protected Sub GridViewInceptionToDateExpenseReport_RowDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles GridViewInceptionToDateExpenseReport.RowDataBound
        Dim i As Integer
        Dim result As Double

        If (e.Row.RowType = DataControlRowType.DataRow) Then
            For i = 2 To e.Row.Cells.Count - 1
                If Double.TryParse(e.Row.Cells(i).Text, result) Then
                    e.Row.Cells(i).Text = FormatCurrency(Convert.ToDouble(e.Row.Cells(i).Text))
                End If
            Next i
        End If
    End Sub

Thanks Mark and Jason.

 

[jmeckley]

yup, that's messy you could tighten up the code a bit. here it is in c#

if(e.Row.RowType != DataControlRowType.DataRow) return;
for(var i = 2; i < e.Row.Cells.Count; i++)
{
   var number = 0;
   var cell = e.Row.Cells[i];
   if(!double.TryParse(cell.Text, out number)) continue;
   cell.Text = number.ToString("{0:C}");
}

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190
faq732-7259