Get Max Number from Datatable using the Compute method

This is a nifty little thing I came across recently.  I wanted to get the maximum value of a number field from a datatable, and learned about the Datatable’s Compute method.  It’s really easy to use.  The method takes two arguments – an expression argument, which is the aggregate expression you want to use (Count, Max, Sum, etc), and a filter expression, which functions like a SQL WHERE clause.  If you don’t want to filter by anything, you can send Nothing for the second argument (Null in C#).

An example:

Dim expression As String
Dim filter as string
Dim maxNum As Integer

‘get maximum job number from table
expression = “Max(JobNumber)”
filter = “JobDate > 12/31/06 AND JobDate < 1/1/08”
maxNum = CInt(tblJobInfo.Compute(expression, filter))

If you don’t want to filter, it would look like this:

expression = “Max(JobNumber)”
maxNum = CInt(tblJobInfo.Compute(expression, Nothing))

Advertisements

Gridview cell text values all Empty Strings

I spent a good part of today butting up against this problem – puzzled and Googled and experimented and Googled some more, but could not for the life of me figure it out – I kept doing exactly what the examples said I should do.  But then I finally found the answer, and so thought I would post it in the hopes of saving someone else the same hassle.

First, what I was trying to do.  I have an updatable gridview of a set size – it always displays 5 rows, and all 4 columns for all 5 rows will need to contain data when the user saves the page.  So, on page load, I want to loop through the gridview, inspect each cell to see whether it contains a value, and set its backcolor to yellow if it doesn’t to remind the user to fill it in.

The articles I read said that there were basically two ways to see if the cell has a value.  For a regular boundfield, you just check the cell’s text property directly, like so (All these examples are in the Gridview RowDataBound event, using that event’s System.Web.UI.WebControls.GridViewRowEventArgs argument.  This event fires on every postback.):

Dim c As Integer
Dim cell As TableCell

If e.Row.RowType = DataControlRowType.DataRow Then
   For c = 0 To e.Row.Cells.Count – 1

      ‘get cell
      cell = e.Row.Cells(c)

      ‘if cell is empty, make cell yellow
      If String.IsNullOrEmpty(cell.Text) Then
          cell.BackColor = Drawing.Color.Yellow
      End If
   Next
End If

And for a named control in a Template field, you use the cell’s FindControl method to get the control, and then check the control’s text property.  So if you had a Template field with a label named lblValue, you’d do this:

Dim cell As TableCell
Dim lbl As Label

If e.Row.RowType = DataControlRowType.DataRow Then

      ‘get cell
      cell = e.Row.Cells(0)

      ‘get control
      lbl = CType(cell.FindControl(“lblValue”), Label)

      ‘if label text is empty, make cell yellow
      If String.IsNullOrEmpty(lbl.Text) Then
          cell.BackColor = Drawing.Color.Yellow
      End If
End If

Turned out my problem was that I was using Template fields, but not using named controls (at least not in the item template) – so I couldn’t use the FindControl method because I didn’t have a control name to feed it, but just checking the cell’s text property returned an empty string.  Because, I discovered, when you use a Template field without a named control, like this:

<asp:TemplateField HeaderText=”Value”>
    <ItemTemplate>
        <%#Me.FormatCell(Eval(“Value”))%>
    </ItemTemplate>
    <EditItemTemplate>
        <asp:TextBox ID=”txtValue” runat=”server” Text='<%# Bind(“Value”) %>’>
        </asp:TextBox>
    </EditItemTemplate>
</asp:TemplateField>

even though you don’t specifically put the Item Template value in a control, ASP.NET does.  You can’t tell this from looking at the source code – the value just appears to be in a regular <td> cell.  But if you look at the cell in the Command Window during Debugging (as I finally thought to do), you’ll see that there is a control in the cell’s control collection – a DataBoundLiteralControl.

So I needed to do a variation of the second get-the-value-from-the-control method.  Plus I had the added difficulty that I wanted the cell background to stay yellow even while the user was editing it.  But of course, when the row was in edit mode, the cell had 3 controls, not one – a LiteralControl, a Textbox, and another LiteralControl.  And, I didn’t want to have to name every control in order to use the cell’s FindControl method – I wanted to look at every cell.

So I worked out a procedure that found the controls I needed based on their position in the cell’s control collection, rather than their name – and adjusted for rows in edit mode.  My final code ended up looking like this:

Dim c As Integer
Dim cell As TableCell
Dim td As DataBoundLiteralControl
Dim txtBox As TextBox
Dim text As String

If e.Row.RowType = DataControlRowType.DataRow Then

   For c = 0 To e.Row.Cells.Count – 1

      ‘get cell
      cell = e.Row.Cells(c)

      ‘if row is in edit mode, get textbox control’s text value
      If e.Row.RowIndex = Me.Gridview1.EditIndex Then 
          txtBox = CType(cell.Controls(1), TextBox)
          text = txtBox.Text.Trim
      Else ‘otherwise, get DataBoundLiteralControl’s text value
          td = CType(cell.Controls(0), DataBoundLiteralControl)
          text = td.Text.Trim
      End If

      ‘if text is blank, set cell backcolor to yellow
      If String.IsNullOrEmpty(text) Then
          cell.BackColor = Drawing.Color.Yellow
      End If
   Next

End If