[2005] Sum numbers

[Annas]

Hai...

Ok, what i try do here is to sum a number from database. i save the number as varchar, and upon retrieving from database i convert it to int.

Let say there is 10 row in a table. Each row holding different number.
So i want to call each row and sum it all. If it in sql yes i could do it.
But in vb2005, i don't know. Can anyone point me how to do it.

Thank You

[jmcilhinney]

First of all, if the data is numbers then why are you saving it as text? Unless there a good reason for that you should change the database schema.

If you want to execute an SQL aggregate function on the data in a DataTable you can use its Compute method. If your DataColumn contains Integers then you'd do it like this:

Dim sum As Integer = CInt(myDataTable.Compute("SUM(MyColumn)", Nothing))

If the column contains Strings then you need to convert them to Integers before summing:

Dim sum As Integer = CInt(myDataTable.Compute("SUM(CONVERT(MyColumn, 'System.Int32'))", Nothing))

[Annas]

Actually, the reason i save in string because. When i declare the data as double,
when i save it, it will automatically change it back to integer. For example, if i enter a number "2.3" it will save as "23". This happen in the sql server not in vb.net. I already pass it to my friends, to have a look. He also don't know what happen, actually its suppose to be not much of a problem.

Anyway thank u very much for helping me out.

[jmcilhinney]

You should never store data as text when it's not text if it can be avoided. Dates should be stored as dates; numbers should be stored as numbers; etc. There's no reason that floating-point values should spontaneously become integers. You must be handling it incorrectly somewhere along the line. I suggest that you fix the real problem instead of using a very hacky workaround.