Datatable Compute method

Results 1 to 4 of 4

Thread: Datatable Compute method

  1. #1
    Join Date
    Dec 1969

    Default Datatable Compute method

    I have a fairly complex stored proc which I use to display a table of results<BR><BR>Rather than requery the data to get a sum of some of the columns I am trying to add up the values in the columns of the dataset I use for display<BR><BR>I am using the Compute method of the datatable to do this.<BR><BR>The problem is that some of the rows contains NULL values from the DB<BR><BR>So I am trying to filter these out using the second parameter of the Compute method:<BR><BR>Function Compute(expression as String, filter as String) as Object<BR><BR>This is what I have so far<BR><BR>intStock = ds.Tables(0).Compute("sum(stock)", "stock &#060;&#062; " & System.DBNull.Value)<BR> <BR><BR>I.e. give me the sum of the column "Stock" where stock is not a null value<BR><BR>but this returns an error:<BR><BR>Syntax error: Missing operand after &#039;&#060;&#062;&#039; operator<BR><BR>Anyone know what the correct syntax might be??

  2. #2
    Join Date
    Dec 1969

    Default RE: Datatable Compute method

    In must DB&#039;s <BR>The syntax for null&#039;s would be<BR>intStock = ds.Tables(0).Compute("sum(stock)", "stock is not null"<BR>

  3. #3
    Join Date
    Dec 1969

    Default RE: Datatable Compute method

    no joy with that either<BR><BR>error:<BR>"Cast from type &#039;DBNull&#039; to type &#039;Integer&#039; is not valid"<BR><BR>It&#039;s as if the filter expression is getting ignored<BR><BR>I have now tried creating a new column based on the following expression <BR>isnull(stock, 0)<BR><BR>Which should create a new column replcing all the nulls with zeros in the new column.<BR><BR>And then I try to compute the sum in that colum<BR><BR>But I still get an illegal cast error<BR><BR>here&#039;s my code<BR><BR> Dim dt As DataTable = ds.Tables(0)<BR><BR> &#039;Create new column<BR> Dim cTotal As DataColumn<BR> cTotal = New DataColumn()<BR> With cTotal<BR> .DataType = System.Type.GetType("System.Int32")<BR> .ColumnName = "StockNoNull"<BR> .Expression = "IsNull(stock, 0)"<BR> End With<BR><BR> With dt.Columns<BR> .Add(cTotal)<BR> End With<BR><BR> intStock = dt.Compute("sum(StockNoNull)", "StockNoNull &#062; 0")<BR> <BR><BR>anyone any ideas?

  4. #4
    Join Date
    Feb 2010

    Default DataTable Compute Method

    Compute is one of the methods of DataTable class. This method computes an expression on the current rows that pass the filter criteria.

    Object DataTable.Compute(string expression , string filter)

    Expression - The expression to compute.
    Filter - The filter to limit the rows that evaluate in the expression.

    This method's return type is System.Object. So, you have to cast it with appropriate data type.

    The following operations can be passed through as expression parameter.
    Sum -> Sum
    Average -> Avg
    Minimum -> Min
    Maximum -> Max
    Count -> Count
    Statistical standard deviation ->StDev
    Statistical variance -> Var


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts