ARGH! Converting the value of a dtatable from a string to a Double

[Coleen]

Hi All :-)

I found the way to get my column sum (Thanks Cor I did it a little different, but the result is what I wanted) I used:

dt_stat_report_ 3b.Columns.Add( New DataColumn("Sum ", GetType(Double) , "sum(Column_10_ ld_act_125_gtr_ fy_fy_hh_avg)") )
where dt_stat_report_ 3b is my datatable, Sum is my column name and sum(Column_10_l d_act_125_gtr_f y_fy_hh_avg) is the sum of the column I needed. This Does return me the correct value in the column I've specified, but now I need that value to be stored in a variable that I can use in another calculation. I've tried:

ld_act_125_gtr_ fy_hh_avg_grnd_ tot = CDbl(dt_stat_re port_3b.Columns .Item(15).ToStr ing)

Where ld_act_125_gtr_ fy_hh_avg_grnd_ tot is the variable and is declared as a Double. This returns an error that the input string was not in the correct format. What I really want to do is get the value from that column and populate the variable with it. I CAN'T get the CDbl() to work! I've tried to do it as CDbl(), I tried System.Convert. ToDouble() but I ALWAYS get an error that the input string was in an incorrect format. I just want the VALUE from the Column that I've summed! Please, can someone explain how to get the string to convert to a double or how to get dt_stat_report_ 3b.Columns.Item (15).Value? Value is not an option and I don't know what else to try!

Any help is GREATLY Appreciated, since I have spent all day on this problem!

Thanks!

Coleen

[jim]

Hi Coleen,

i'm not 100% sure that i understand your problem. are you able to get the
value from the computed field? if so, what datatype is the column in which
the data resides.

if you just need to convert the string to a double then you can use the
parse method.

Double.Parse(dt _stat_report_3b .Columns.Item(1 5).ToString)

overloaded methods allow you to provide a formatprovider and/or a
numberstyle

if the computed column was created with a datatype of Double, then
theoretically

CDbl(myDatatabl e.Rows(myRowInd ex)(myComputedC olumnName))

should pull the data from the field... (theoretically. ..) but, i haven't
worked much with computed columns yet.

hope this helps.

good luck,

jim
"Coleen" <co**********@y aho.com> wrote in message
news:e4******** ******@TK2MSFTN GP12.phx.gbl...
Hi All :-)

I found the way to get my column sum (Thanks Cor I did it a little
different, but the result is what I wanted) I used:

dt_stat_report_ 3b.Columns.Add( New DataColumn("Sum ", GetType(Double) ,
"sum(Column_10_ ld_act_125_gtr_ fy_fy_hh_avg)") )
where dt_stat_report_ 3b is my datatable, Sum is my column name and
sum(Column_10_l d_act_125_gtr_f y_fy_hh_avg) is the sum of the column I
needed. This Does return me the correct value in the column I've specified,
but now I need that value to be stored in a variable that I can use in
another calculation. I've tried:

ld_act_125_gtr_ fy_hh_avg_grnd_ tot =
CDbl(dt_stat_re port_3b.Columns .Item(15).ToStr ing)

Where ld_act_125_gtr_ fy_hh_avg_grnd_ tot is the variable and is declared as a
Double. This returns an error that the input string was not in the correct
format. What I really want to do is get the value from that column and
populate the variable with it. I CAN'T get the CDbl() to work! I've tried
to do it as CDbl(), I tried System.Convert. ToDouble() but I ALWAYS get an
error that the input string was in an incorrect format. I just want the
VALUE from the Column that I've summed! Please, can someone explain how to
get the string to convert to a double or how to get
dt_stat_report_ 3b.Columns.Item (15).Value? Value is not an option and I
don't know what else to try!

Any help is GREATLY Appreciated, since I have spent all day on this problem!

Thanks!

Coleen

[Cor Ligthert]

Coleen,

ld_act_125_gt r_fy_hh_avg_grn d_tot = CDbl
(dt_stat_repo rt_3b.Columns.I tem(15).ToStrin g)

The datatable is created vertical (columns) and used horizontal (rows).

Is this what you mean?

ld_act_125_gtr_ fy_hh_avg_grnd_ tot = dt_stat_report_ 3b.rows("Sum"). Item(15)

I hope this helps?

Cor

[Coleen]

I've tried both suggestions. Here is the code I'm trying:

dt_stat_report_ 3b.Columns.Add( New DataColumn("Sum ", GetType(Double) , "sum(Column_10_ ld_act_125_gtr_ fy_fy_hh_avg)") )
where dt_stat_report_ 3b. is the datatable, Sum is the compute, and Column_10_ld_ac t_125_gtr_fy_fy _hh_avg is the column that I'm getting the grand total for.

I have a For/Next Loop where I have variable ld_pct_125_tot_ gtr_fy_hh that I need to have the following calculation:

ld_pct_125_tot_ gtr_fy_hh = ld_act_125_gtr_ fy_hh_avg/sum for 17 rows in the Loop. Each row has a different average thus the variable ld_act_125_gtr_ fy_hh_avg. the computed column "sum" is the total of all the rows of the variable ld_act_125_gtr_ fy_hh_avg.

I've tried EVERYTHING I can think of to get the value from the computed column "sum" into a variable name. I either get a message that the input string was not in the correct format, or I get a message when I try:

ld_act_125_gtr_ fy_hh_avg_grnd_ tot = CDbl(dt_stat_re port_3b.Rows(0) .Item(0).ToStri ng) that the Row or Item does not exist.

There HAS to be a way to get the value from the computed item into a variable as a double so that I can use that value in my next calculation. I' am at a loss and in desperate need of help! Thanks you so much for trying :-)

Coleen


"Cor Ligthert" <no**********@p lanet.nl> wrote in message news:uA******** ******@TK2MSFTN GP10.phx.gbl...

Coleen,

ld_act_125_gt r_fy_hh_avg_grn d_tot = CDbl
(dt_stat_repo rt_3b.Columns.I tem(15).ToStrin g)

The datatable is created vertical (columns) and used horizontal (rows).

Is this what you mean?

ld_act_125_gtr_ fy_hh_avg_grnd_ tot = dt_stat_report_ 3b.rows("Sum"). Item(15)

I hope this helps?

Cor

[Coleen]

For anyone looking at this, here is the solution thanks to my very good VB.Net Teacher whom I called in desperation - Thanks Jerry!!!

Dim the variable as Public. On PreRender use:

PublicVariableN ame = DatatabelName.R ow(0).Cell(15). Value

I could not get the option of value after row or cell in anything I tried within my For/Next Loop. I can now use this Variable value to do my calculations for the percentage rate. Again - THANKS Jerry!
"Coleen" <co**********@y aho.com> wrote in message news:Oa******** *****@tk2msftng p13.phx.gbl...
I've tried both suggestions. Here is the code I'm trying:

dt_stat_report_ 3b.Columns.Add( New DataColumn("Sum ", GetType(Double) , "sum(Column_10_ ld_act_125_gtr_ fy_fy_hh_avg)") )
where dt_stat_report_ 3b. is the datatable, Sum is the compute, and Column_10_ld_ac t_125_gtr_fy_fy _hh_avg is the column that I'm getting the grand total for.

I have a For/Next Loop where I have variable ld_pct_125_tot_ gtr_fy_hh that I need to have the following calculation:

ld_pct_125_tot_ gtr_fy_hh = ld_act_125_gtr_ fy_hh_avg/sum for 17 rows in the Loop. Each row has a different average thus the variable ld_act_125_gtr_ fy_hh_avg. the computed column "sum" is the total of all the rows of the variable ld_act_125_gtr_ fy_hh_avg.

I've tried EVERYTHING I can think of to get the value from the computed column "sum" into a variable name. I either get a message that the input string was not in the correct format, or I get a message when I try:

ld_act_125_gtr_ fy_hh_avg_grnd_ tot = CDbl(dt_stat_re port_3b.Rows(0) .Item(0).ToStri ng) that the Row or Item does not exist.

There HAS to be a way to get the value from the computed item into a variable as a double so that I can use that value in my next calculation. I' am at a loss and in desperate need of help! Thanks you so much for trying :-)

Coleen


"Cor Ligthert" <no**********@p lanet.nl> wrote in message news:uA******** ******@TK2MSFTN GP10.phx.gbl...

Coleen,

ld_act_125_gt r_fy_hh_avg_grn d_tot = CDbl
(dt_stat_repo rt_3b.Columns.I tem(15).ToStrin g)

The datatable is created vertical (columns) and used horizontal (rows).

Is this what you mean?

ld_act_125_gtr_ fy_hh_avg_grnd_ tot = dt_stat_report_ 3b.rows("Sum"). Item(15)

I hope this helps?

Cor

[Coleen]

Well, I spoke to soon...this works great if you just want to display the value, but if you want to use the value to perform another calculation, I get "Infinity" in the cell where I'm trying to use the grand total I get from the PreRender.. any other suggestions?

PLEASE? TIA
"Coleen" <co**********@y aho.com> wrote in message news:OV******** *****@TK2MSFTNG P12.phx.gbl...
For anyone looking at this, here is the solution thanks to my very good VB.Net Teacher whom I called in desperation - Thanks Jerry!!!

Dim the variable as Public. On PreRender use:

PublicVariableN ame = DatatabelName.R ow(0).Cell(15). Value

I could not get the option of value after row or cell in anything I tried within my For/Next Loop. I can now use this Variable value to do my calculations for the percentage rate. Again - THANKS Jerry!
"Coleen" <co**********@y aho.com> wrote in message news:Oa******** *****@tk2msftng p13.phx.gbl...
I've tried both suggestions. Here is the code I'm trying:

dt_stat_report_ 3b.Columns.Add( New DataColumn("Sum ", GetType(Double) , "sum(Column_10_ ld_act_125_gtr_ fy_fy_hh_avg)") )
where dt_stat_report_ 3b. is the datatable, Sum is the compute, and Column_10_ld_ac t_125_gtr_fy_fy _hh_avg is the column that I'm getting the grand total for.

I have a For/Next Loop where I have variable ld_pct_125_tot_ gtr_fy_hh that I need to have the following calculation:

ld_pct_125_tot_ gtr_fy_hh = ld_act_125_gtr_ fy_hh_avg/sum for 17 rows in the Loop. Each row has a different average thus the variable ld_act_125_gtr_ fy_hh_avg. the computed column "sum" is the total of all the rows of the variable ld_act_125_gtr_ fy_hh_avg.

I've tried EVERYTHING I can think of to get the value from the computed column "sum" into a variable name. I either get a message that the input string was not in the correct format, or I get a message when I try:

ld_act_125_gtr_ fy_hh_avg_grnd_ tot = CDbl(dt_stat_re port_3b.Rows(0) .Item(0).ToStri ng) that the Row or Item does not exist.

There HAS to be a way to get the value from the computed item into a variable as a double so that I can use that value in my next calculation. I' am at a loss and in desperate need of help! Thanks you so much for trying :-)

Coleen


"Cor Ligthert" <no**********@p lanet.nl> wrote in message news:uA******** ******@TK2MSFTN GP10.phx.gbl...

Coleen,

ld_act_125_gt r_fy_hh_avg_grn d_tot = CDbl
(dt_stat_repo rt_3b.Columns.I tem(15).ToStrin g)

The datatable is created vertical (columns) and used horizontal (rows).

Is this what you mean?

ld_act_125_gtr_ fy_hh_avg_grnd_ tot = dt_stat_report_ 3b.rows("Sum"). Item(15)

I hope this helps?

Cor

[Cor Ligthert]

Coleen,

I am really confused by you, do you want a "compute" the sum of a vertical
column or an expression to get the sum of horizontal items in a rows?

Datatable.Compu te
http://msdn.microsoft.com/library/de...mputetopic.asp

And what you are using in my opinion.
Datacolumn.Expr ession
http://msdn.microsoft.com/library/de...ssiontopic.asp

What is it you want?

Cor

Cor

[Coleen]

Thanks Cor :-)

I want to get the grand total (sum) of one column (not row) Then I need to take that grand total and divide each row of the column by the grand total in order to get percentage rate. This is done In MS Excel as:

Column A Column B

row 1 100 =100/600 (Column A Row 1/Column A Total)

row 2 200 =200/600 (Column A Row 1/Column A Total)

row 3 300 =300/600 (Column A Row 1/Column A Total)

Total 600

I need to get the value of "600" into a variable that I can use to divide each row for column A by. Does that Make a little more sense? I ended up creating two datagrids. One that does all the calculations up to the grand total that I need (column A) and I created a variable to store the total for Column A "600" in after I did the databind on the first datagrid. In the second datagrid I took the grand total from Column A "600" and used it in my second datagrid to divide the values from each row in Column A with. Since each row in column A has a variable associated with it (I know there will always be 17 rows of data) I created 17 separate variables for each row in datagrid 1 and use those variables divided by the grand total (Total Column A - "600") from datagrid 1 as available in datagrid 2. Now I hide both datagrids and populate an html table with the values from datagrid1 and datagrid 2 to display to the user. Not simple, not pretty, but it works.

If there is an easier way to get percentage rates from a calculated value for each row, I'd love to know about it...

Thanks - I hope you understand what I was trying to do. This type of calculation is EXTREMELY easy in Excel, but when you have to do all the work that Excel does for you automatically, it is VERY difficult.

Coleen

"Cor Ligthert" <no**********@p lanet.nl> wrote in message news:e1******** *****@TK2MSFTNG P11.phx.gbl...

Coleen,

I am really confused by you, do you want a "compute" the sum of a vertical
column or an expression to get the sum of horizontal items in a rows?

Datatable.Compu te
http://msdn.microsoft.com/library/de...mputetopic.asp

And what you are using in my opinion.
Datacolumn.Expr ession
http://msdn.microsoft.com/library/de...ssiontopic.asp

What is it you want?

Cor



Cor

[David]

On 2004-08-14, Coleen <co**********@y aho.com> wrote:

This is a multi-part message in MIME format.

------=_NextPart_000_ 0008_01C48157.E 7166940
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Thanks Cor :-)

I want to get the grand total (sum) of one column (not row) Then I need =
to take that grand total and divide each row of the column by the grand =
total in order to get percentage rate. This is done In MS Excel as:

Column A Column B

row 1 100 =3D100/600 (Column A Row 1/Column A Total)

row 2 200 =3D200/600 (Column A Row 1/Column A Total)

row 3 300 =3D300/600 (Column A Row 1/Column A Total)

Total 600=20

I need to get the value of "600" into a variable that I can use to =
divide each row for column A by. Does that Make a little more sense? I =
ended up creating two datagrids.

DataTable1.Colu mns.Add("Column APercentage", _
GetType(Decimal ), _
"ColumnA/SUM(ColumnA)")

Is that what you're after? This would give you a new column of:

16%
33%
50%

(assuming you format as percentages).

[Cor Ligthert]

Coleen,

Can you try this extremely easy sample, a new project a datagrid and than
run.

I (think) hope this helps?

Cor

\\\
Private Sub Form1_Load(ByVa l sender As _
Object, ByVal e As System.EventArg s) _
Handles MyBase.Load
Dim dt As New DataTable("Cole en")
dt.Columns.Add( "Fig", GetType(System. Int32))
dt.Columns.Add( "Perc", GetType(System. Int32))
For i As Integer = 0 To 5
Dim dr As DataRow = dt.NewRow
dr(0) = i + 1
dt.Rows.Add(dr)
Next
Dim dr2 As DataRow = dt.NewRow
Dim sum As Integer = _
CInt(dt.Compute ("Sum(Fig)", ""))
dr2(0) = sum
dt.Rows.Add(dr2 )
dt.Columns(1).E xpression = _
"Fig * 100/ " & sum.ToString
DataGrid1.DataS ource = dt
End Sub
///