Koray Durudogan Koray Durudogan - 6 months ago 19
Vb.net Question

Can not use compute method for datatable

I have a datatable and when I debug, I see that it is full with correct datas. I am trying to use a compute method but it does not go well.
I tried

Dim xx As DataTable = gelenData.Compute("Sum(H_PERSON_P_NO_543)", "H_PERSON_P_CINS_544 = 'ERKEK'")


and this turned me an error like


Unable to cast object of type 'System.Int64' to type 'System.Data.DataTable'.


When I tried

Dim xx As DataView = gelenData.Compute("Sum(Convert([H_PERSON_P_NO_543], 'System.Int32'))", "H_PERSON_P_CINS_544 = 'ERKEK'")


or

Dim xx As DataView = gelenData.Compute("Sum(Convert([H_PERSON_P_NO_543], 'System.Int64'))", "H_PERSON_P_CINS_544 = 'ERKEK'")


I get this error


Syntax error in aggregate argument: Expecting a single column argument with possible 'Child' qualifier.


I am really stuck in here for hours and tried too many things but it seems I am missing something. What am I doing wrong ?

Answer

Looking at your first line of code:

Dim xx As DataTable = gelenData.Compute("Sum(H_PERSON_P_NO_543)", "H_PERSON_P_CINS_544 = 'ERKEK'")

You can't declare xx as a DataTable since it doesn't return a DataTable object, but rather an Int64.

What it actually does is to return the actual "Sum" of your expression.

So if you Think of it like this:

"Give me the total sum of H_PERSON_NO_543 where the H_PERSON_CINS_544 = 'ERKEK'"

And you have the following data (just for example):

H_PERSON_NO_543      H_PERSON_CINS_544
      1                 SOMETHING
      3                 ERKEK
      5                 ERKEK
      7                 SOMETHING ELSE

And then you run your code, it will return 8 (since there are two rows meeting the criteria, one with the value 3 and one with the value 5).

When you then try to cast it as a DataTable it will simply refuse, since it's a nice int and not a DataTable.

So, assuming you want to sum up the values from the H_PERSON_NO_543 column in the table your best bet would be:

Dim xx As Integer = gelenData.Compute("Sum(H_PERSON_P_NO_543)", "H_PERSON_P_CINS_544 = 'ERKEK'")