add custom column to dataset problem...
-
Hi... I am trying to add a few custom columns to a dataset which will show percentages of already existing columns. The problem I am having is that in my SQL I am grouping by date and on some days there will be Zero for the number I am using as the denominator in the percentage column (the one I am adding) expression, therefore causing me to have a divide by zero error. My approach to solving this was to loop through all the rows in the dataset and if that denominator column was 0, I would add the column with no expression, otherwise I would add the column and set the expression = numer/denom*100. This does not work. I am confused... If I add the column say on the first row of the loop where the denom was 0, then on the second row of the loop the denom is a valid number, but when I try and change the column(whatever).datatype or column(whatever).expression it gives me "Object reference not set to an instance of an object" Here is the code... if anyone can help me figure out a working method for this I would GREATLY appreciate it... For x = 0 To ds.Tables("Received Total").Rows.Count - 1 Dim t, d As Integer Dim p As Decimal t = ds.Tables("Received Total").Rows(x).Item("Total ASN") d = ds.Tables("Received Total").Rows(x).Item("DARM") If t <> 0 Then p = d / t * 100 End If If ds.Tables("Received Total").Rows(x).Item("Total ASN") <> 0 Then 'Dim dcDARM As New DataColumn ds.Tables("Received Total").Columns("dcDARM").DataType = GetType(Decimal) ds.Tables("Received Total").Columns("dcDARM").ColumnName = "DARM%" ds.Tables("Received Total").Columns("dcDARM").Expression = "DARM/[Total ASN]*100" 'ds.Tables("Received Total").Columns.Add(dcDARM) Dim dcSupplierLink As New DataColumn dcSupplierLink.DataType = GetType(Decimal) dcSupplierLink.ColumnName = "Supplier Link%" dcSupplierLink.Expression = "[Supplier Link]/[Total ASN]*100" ds.Tables("Received Total").Columns.Add(dcSupplierLink) Dim dcVendor As New DataColumn dcVendor.DataType = GetType(Decimal) dcVendor.ColumnName = "Vendor%" dcVendor.Expression = "Vendor/[Total ASN]*100" ds.Tables("Received Total").Columns.Add(dcVendor) Dim dcOther As New DataColumn dcOther.DataType = GetType(Decimal) dcOther.ColumnName = "Other%" dcOther.Expression = "Other/[Total ASN]*100" ds.Tables("Received Total").Columns.Add(dcOther) Else Dim dcDARM As New DataColumn dcDARM.DataType = GetType(Decimal) dcDARM.ColumnName = "DARM%" Dim dcSupplierLink As New DataColumn Dim dcVendor As New DataColumn Dim dcOther As New DataColumn ds.Tables("Received T
-
Hi... I am trying to add a few custom columns to a dataset which will show percentages of already existing columns. The problem I am having is that in my SQL I am grouping by date and on some days there will be Zero for the number I am using as the denominator in the percentage column (the one I am adding) expression, therefore causing me to have a divide by zero error. My approach to solving this was to loop through all the rows in the dataset and if that denominator column was 0, I would add the column with no expression, otherwise I would add the column and set the expression = numer/denom*100. This does not work. I am confused... If I add the column say on the first row of the loop where the denom was 0, then on the second row of the loop the denom is a valid number, but when I try and change the column(whatever).datatype or column(whatever).expression it gives me "Object reference not set to an instance of an object" Here is the code... if anyone can help me figure out a working method for this I would GREATLY appreciate it... For x = 0 To ds.Tables("Received Total").Rows.Count - 1 Dim t, d As Integer Dim p As Decimal t = ds.Tables("Received Total").Rows(x).Item("Total ASN") d = ds.Tables("Received Total").Rows(x).Item("DARM") If t <> 0 Then p = d / t * 100 End If If ds.Tables("Received Total").Rows(x).Item("Total ASN") <> 0 Then 'Dim dcDARM As New DataColumn ds.Tables("Received Total").Columns("dcDARM").DataType = GetType(Decimal) ds.Tables("Received Total").Columns("dcDARM").ColumnName = "DARM%" ds.Tables("Received Total").Columns("dcDARM").Expression = "DARM/[Total ASN]*100" 'ds.Tables("Received Total").Columns.Add(dcDARM) Dim dcSupplierLink As New DataColumn dcSupplierLink.DataType = GetType(Decimal) dcSupplierLink.ColumnName = "Supplier Link%" dcSupplierLink.Expression = "[Supplier Link]/[Total ASN]*100" ds.Tables("Received Total").Columns.Add(dcSupplierLink) Dim dcVendor As New DataColumn dcVendor.DataType = GetType(Decimal) dcVendor.ColumnName = "Vendor%" dcVendor.Expression = "Vendor/[Total ASN]*100" ds.Tables("Received Total").Columns.Add(dcVendor) Dim dcOther As New DataColumn dcOther.DataType = GetType(Decimal) dcOther.ColumnName = "Other%" dcOther.Expression = "Other/[Total ASN]*100" ds.Tables("Received Total").Columns.Add(dcOther) Else Dim dcDARM As New DataColumn dcDARM.DataType = GetType(Decimal) dcDARM.ColumnName = "DARM%" Dim dcSupplierLink As New DataColumn Dim dcVendor As New DataColumn Dim dcOther As New DataColumn ds.Tables("Received T
You would stand a better chance if you posted this to the SQL ADO.NET group rather than the ASP.NET group. Rocky <>< www.HintsAndTips.com - Now with "Recommendation" postings www.MyQuickPoll.com - Now with Recent Poll List
-
You would stand a better chance if you posted this to the SQL ADO.NET group rather than the ASP.NET group. Rocky <>< www.HintsAndTips.com - Now with "Recommendation" postings www.MyQuickPoll.com - Now with Recent Poll List
-
I fixed it using another method... but it wasnt in the actual sql that I was trying to add a column... it was within the dataset... that is why I posted it here.. Thanks anyway! cavall'
That is the point, datasets are not ASP.NET (web site technology), they are ADO.NET and belong in the ADO.NET group :) Rocky <>< www.HintsAndTips.com - Now with "Recommendation" postings www.MyQuickPoll.com - Now with Recent Poll List