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_ld_act_125_gtr_fy_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_report_3b.Columns.Item(15).ToString)

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

Nov 21 '05 #1

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(15).To String)

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(myDatatable.Rows(myRowIndex)(myComputedColumn Name))

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**********@yaho.com> wrote in message
news:e4**************@TK2MSFTNGP12.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_ld_act_125_gtr_fy_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_report_3b.Columns.Item(15).ToString)

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

Nov 21 '05 #2

Cor Ligthert

Coleen,

ld_act_125_gtr_fy_hh_avg_grnd_tot = CDbl
(dt_stat_report_3b.Columns.Item(15).ToString)

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

Nov 21 '05 #3

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_act_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_report_3b.Rows(0).Item(0).ToString) 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**********@planet.nl> wrote in message news:uA**************@TK2MSFTNGP10.phx.gbl...

Coleen,

ld_act_125_gtr_fy_hh_avg_grnd_tot = CDbl
(dt_stat_report_3b.Columns.Item(15).ToString)

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

Nov 21 '05 #4

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:

PublicVariableName = DatatabelName.Row(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**********@yaho.com> wrote in message news:Oa*************@tk2msftngp13.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_act_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_report_3b.Rows(0).Item(0).ToString) 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**********@planet.nl> wrote in message news:uA**************@TK2MSFTNGP10.phx.gbl...

Coleen,

ld_act_125_gtr_fy_hh_avg_grnd_tot = CDbl
(dt_stat_report_3b.Columns.Item(15).ToString)

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

Nov 21 '05 #5

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**********@yaho.com> wrote in message news:OV*************@TK2MSFTNGP12.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:

PublicVariableName = DatatabelName.Row(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**********@yaho.com> wrote in message news:Oa*************@tk2msftngp13.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_act_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_report_3b.Rows(0).Item(0).ToString) 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**********@planet.nl> wrote in message news:uA**************@TK2MSFTNGP10.phx.gbl...

Coleen,

ld_act_125_gtr_fy_hh_avg_grnd_tot = CDbl
(dt_stat_report_3b.Columns.Item(15).ToString)

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

Nov 21 '05 #6

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.Compute
http://msdn.microsoft.com/library/de...mputetopic.asp

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

What is it you want?

Cor

Cor

Nov 21 '05 #7

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**********@planet.nl> wrote in message news:e1*************@TK2MSFTNGP11.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.Compute
http://msdn.microsoft.com/library/de...mputetopic.asp

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

What is it you want?

Cor



Cor

Nov 21 '05 #8

David

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

This is a multi-part message in MIME format.

------=_NextPart_000_0008_01C48157.E7166940
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.Columns.Add("ColumnAPercentage", _
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).

Nov 21 '05 #9

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(ByVal sender As _
Object, ByVal e As System.EventArgs) _
Handles MyBase.Load
Dim dt As New DataTable("Coleen")
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).Expression = _
"Fig * 100/ " & sum.ToString
DataGrid1.DataSource = dt
End Sub
///

Nov 21 '05 #10