?
Solved

How do I use SqlDataReader to add Totals row to the bottom of my table

Posted on 2007-09-30
27
Medium Priority
?
919 Views
Last Modified: 2013-11-07
I am using SqlDataReader to build a table. I want to add a "Total" row at the bottom of the table to sum up all the values in each column. I know how to do this when using the Gridcontrol but for this project I need to use SqlDataReader. I'm in the very early stages of learning C# so my knowlegde of syntax and classes is pretty weak.  This table generation is working now , just the total columns is not added

Here is how I am building the table.

private void BuildTable(SqlDataReader data, Table table)
        { CreateColumns(table);
          CreateRowHeadings(table);
          while (data.Read())
           CreateRow(table, data);
        }

I know how to calculate the totals using the GridControl and display in the "FooterText" . So I'll try and adapt that once I figure out how to add the total row to the bottom.

This is how I am creating the rows and columns

private void CreateRowHeadings( Table table )
{ Row row = table.Rows.Add( 40, Font.TimesBold, 10, RgbColor.Black, RgbColor.LightGrey );
 row.Align = CellAlign.Center;
 row.VAlign = CellVAlign.Top;
 row.Cells.Add( "Day" );
 row.Cells.Add( "Present # " );
 row.Cells.Add( "Present $$" );
 row.Cells.Add( "Return #" );
 row.Cells.Add( "Return $$" );
 row.Cells.Add( "Collect #" );
 row.Cells.Add( "Collect $$" );
 row.Cells.Add( "Total #" );
 row.Cells.Add( "Total $$" );
 row.Cells.Add( "Echo Date" );
 row.Cells.Add( "DFS Date");
}
       
private void CreateRow(Table table, SqlDataReader data )
{
 Row row = table.Rows.Add( 20 );
 row.Cells.Add( data.GetString(0) , Font.Helvetica, 12, RgbColor.Black, RgbColor.LightGrey, 1 );
 row.Cells.Add( data.GetInt32(1).ToString( ));
 row.Cells.Add( data.GetDecimal(2).ToString( "$0.00" ));
 row.Cells.Add( data.GetInt32(3).ToString( ));
 row.Cells.Add(data.GetDecimal(4).ToString("($0.00)"));
 row.Cells.Add( data.GetInt32(5).ToString( ));
 row.Cells.Add(data.GetDecimal(6).ToString("$0.00"));
 row.Cells.Add( data.GetInt32(7).ToString( ));
 row.Cells.Add(data.GetDecimal(8).ToString("$0.00"));
 row.Cells.Add( data.GetString(9));
 row.Cells.Add( data.GetString(10));
}
Private void CreateColumns( Table table )
{
            table.Columns.Add(65);
            table.Columns.Add(25);
            table.Columns.Add(65);
            table.Columns.Add(25);
            table.Columns.Add(60);
            table.Columns.Add(25);
            table.Columns.Add(60);
            table.Columns.Add(25);
            table.Columns.Add(60);
            table.Columns.Add(60);
            table.Columns.Add(60);
}

-Steve

0
Comment
Question by:svasilakos
  • 14
  • 12
27 Comments
 
LVL 21

Expert Comment

by:surajguptha
ID: 19987140
Lets assume u want to sum up the total $

private void BuildTable(SqlDataReader data, Table table)
        {
          Decimal TotalDollar = 0.0;
          CreateColumns(table);
          CreateRowHeadings(table);
          while (data.Read())
{
           totaldollar+=CreateRow(table, data);
}
Grid.Footer = totalDollar.ToString($0.00);
        }

private Decimal CreateRow(Table table, SqlDataReader data )
{
 Row row = table.Rows.Add( 20 );
 row.Cells.Add( data.GetString(0) , Font.Helvetica, 12, RgbColor.Black, RgbColor.LightGrey, 1 );
 row.Cells.Add( data.GetInt32(1).ToString( ));
 row.Cells.Add( data.GetDecimal(2).ToString( "$0.00" ));
 row.Cells.Add( data.GetInt32(3).ToString( ));
 row.Cells.Add(data.GetDecimal(4).ToString("($0.00)"));
 row.Cells.Add( data.GetInt32(5).ToString( ));
 row.Cells.Add(data.GetDecimal(6).ToString("$0.00"));
 row.Cells.Add( data.GetInt32(7).ToString( ));
 row.Cells.Add(data.GetDecimal(8).ToString("$0.00"));
 row.Cells.Add( data.GetString(9));
 row.Cells.Add( data.GetString(10));

return  (data.GetDecinam(8));
}
0
 
LVL 29

Expert Comment

by:Gautham Janardhan
ID: 19987145
using the sqldatareader is a cvery bad idea in this case as u can use the Adapter.Fill() method to directly fill the datatable

still if u want to do it using SQlDAtaReader u can use the Datatable.Compute() function to calculate the total

object obj = DataTable.Compute("SUM(COLUMNNAME","ANY FILTER");

u can repeat  this on all columns u want to get the desired result
0
 

Author Comment

by:svasilakos
ID: 19987275
Where do I put the  object obj = DataTable.Compute("SUM(COLUMNNAME","ANY FILTER");

Regarding   surajguptha: , recomendation.
 I'm getting this error in the event log

The name 'Grid' does not exist in the current context.    I can see Grid does not exist. Should I use something else.

Also I assume you  meant

Grid.Footer = totalDollar.ToString("$0.00");
instead of  Grid.Footer = totalDollar.ToString($0.00);

and
{
       CreateRow(table, data);    
       totaldollar += CreateRow(table, data);
 }

instead  of

{
       totaldollar += CreateRow(table, data);
 }

-Steve
Lastly to comment on gauthampj: , thanks I know SqlDataReader is bad...I will move away from it to DataTable()  probably or Adapter.Fill() . I'm just testing a PDF export product from cete software and all their examples use SqlDataReader, and I'm rushing to get this "demo" project out.


0
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

 
LVL 21

Expert Comment

by:surajguptha
ID: 19987320
What is the name of ur datagrid? I just gave the same Grid, please reply it with ur datagrid name.

Here's another example for adding a footer to the datagrid
http://aspnet.4guysfromrolla.com/articles/020503-1.aspx

> Steve, just reiterating what gautham says, i think it would be better if u used the dataset/ datatable instead. you are just increasing the complexity of your code by using a datareader and then constructing a datatable. The Fill method would generate the datatable you are trying to build with just one line of code.
0
 

Author Comment

by:svasilakos
ID: 19987336
UnderStood on not using SqlDataReader. I'm trying to crank out a"demo" PDF generator using software called DynamicPDF from a company called cete (www.cete.com ) . I'll convert to DataTable() in a few days. It's just my understanding of c# is weak so I am modifying their examples which use SqlDataReader. I use DataTable with some other stuff I did in the past.

On to your question. This may be stupid but I'm not sure what the name is, they are not really using  a "grid" or atleast I don't think so.

Just to be complete here is my entire code, with your additions.

using System;
using System.Data;
using System.Data.SqlClient;
using ceTe.DynamicPDF;
using ceTe.DynamicPDF.IO;
using ceTe.DynamicPDF.Text;
using ceTe.DynamicPDF.PageElements;


public partial class WebApp_CheckDailySettleSumSiteDFSOPS_PDF : System.Web.UI.Page
   {
        protected CHKDailySumRepCriteria repCriteria;    
       
        static string dBConnString =
        System.Configuration.ConfigurationSettings.AppSettings["DFSConnectionString2"];

            protected void Page_Load(object sender, System.EventArgs e)
            {
            repCriteria = (CHKDailySumRepCriteria)Session[SessionKeys.CHKDailySumRepCriteria];      
           
            // Create a document and set it's properties
                  ceTe.DynamicPDF.Document document = new ceTe.DynamicPDF.Document();
            document.Creator = "CheckDailySettleSumSiteDFSOPS_PDF.aspx";
                  document.Author = "Steve Vasilakos";
                  document.Title = "Check Cashing Daily Summary Report";

                  // Establises connection to the database
                  SqlConnection connection = GetOpenDBConn();
                  SqlDataReader data = GetDailySumData( connection );
           
                  
                  //Create a Table and set it's properties
            Table table = new Table(0, 0,512,600, ceTe.DynamicPDF.Font.Helvetica, 10);
            table.BorderColor = Grayscale.Black;
                  table.RepeatColumnHeaderCount = 1;
                  table.RepeatRowHeaderCount = 1;
                  
                  // Builds the report
            BuildTable(data, table);
            int currentRow = 0;
            int currentColumn = 0;
            do
            {
                currentRow++;
                currentColumn = 1;
                AddTableToPage(document, table, currentRow, currentColumn);
                Table overflowTable = table.GetOverflowColumns();
                do
                {
                    currentColumn++;
                    AddTableToPage(document, overflowTable, currentRow, currentColumn);
                    overflowTable = overflowTable.GetOverflowColumns();
                }
                while (overflowTable != null);
                table = table.GetOverflowRows();
            } while (table != null);

            // Cleans up database connections
            data.Close();
            connection.Close();
                  
                  // Outputs the SimpleReport to the current web page
                  document.DrawToWeb( "ChkDailySumbySettleDate.pdf" );
            //Visible = false;
            }

            private void AddTableToPage( Document document, Table table, int currentRow, int currentColumn )
            {
            Page page = new Page(PageSize.Letter, PageOrientation.Landscape);
                  if( table != null )
                        page.Elements.Add( table );
            page.Elements.Add(new Label("(" + currentRow + "," + currentColumn + ")", 0, page.Dimensions.Body.Height - 40, page.Dimensions.Body.Width, 40, Font.Helvetica, 12, TextAlign.Center));
            document.Pages.Add( page );

           
            }

        private void BuildTable(SqlDataReader data, Table table) //SqlDataReader data, Table table )
        {
          double totaldollar = 0.0;
          CreateColumns(table);
          CreateRowHeadings(table);
          while (data.Read())
         {
           CreateRow(table, data);    
           totaldollar += CreateRow(table, data);
         }
         Grid.Footer = totalDollar.ToString("$0.00");
        }
       
       
        //{

        //CreateColumns(table);
        //CreateRowHeadings(table);
        //while (data.Read())
          //  CreateRow(table, data);
       // }

            private void CreateRowHeadings( Table table )
            {
                  Row row = table.Rows.Add( 40, Font.TimesBold, 10, RgbColor.Black, RgbColor.LightGrey );
                  row.Align = CellAlign.Center;
                  row.VAlign = CellVAlign.Top;
                  row.Cells.Add( "Day" );
                  row.Cells.Add( "Present # " );
                  row.Cells.Add( "Present $$" );
                  row.Cells.Add( "Return #" );
                  row.Cells.Add( "Return $$" );
                  row.Cells.Add( "Collect #" );
                  row.Cells.Add( "Collect $$" );
                  row.Cells.Add( "Total #" );
                  row.Cells.Add( "Total $$" );
                  row.Cells.Add( "Echo Date" );
            row.Cells.Add( "DFS Date");
            }
       
        private double CreateRow(Table table, SqlDataReader data )
            {
           

            Row row = table.Rows.Add( 20 );
                  row.Cells.Add( data.GetString(0) , Font.Helvetica, 12, RgbColor.Black, RgbColor.LightGrey, 1 );
                  row.Cells.Add( data.GetInt32(1).ToString( ));
            row.Cells.Add( data.GetDecimal(2).ToString( "$0.00" ));
            row.Cells.Add( data.GetInt32(3).ToString( ));
            row.Cells.Add(data.GetDecimal(4).ToString("($0.00)"));
            row.Cells.Add( data.GetInt32(5).ToString( ));
            row.Cells.Add(data.GetDecimal(6).ToString("$0.00"));
            row.Cells.Add( data.GetInt32(7).ToString( ));
            row.Cells.Add(data.GetDecimal(8).ToString("$0.00"));
            row.Cells.Add( data.GetString(9));
            row.Cells.Add( data.GetString(10));

            return  (data.GetDouble(8));

                                    
            }

            private void CreateColumns( Table table )
            {
            table.Columns.Add(65);
            table.Columns.Add(25);
            table.Columns.Add(65);
            table.Columns.Add(25);
            table.Columns.Add(60);
            table.Columns.Add(25);
            table.Columns.Add(60);
            table.Columns.Add(25);
            table.Columns.Add(60);
            table.Columns.Add(60);
            table.Columns.Add(60);
            }

        private SqlConnection GetOpenDBConn()
            {
                  // Creates and opens a database connection
            SqlConnection connection = new SqlConnection( dBConnString );
           
                  connection.Open();
                  return connection;
            }

       private SqlDataReader GetDailySumData(SqlConnection connection)
            {
                  
            DateTime from = this.repCriteria.fromDate;
            DateTime to = this.repCriteria.toDate;
            int siteId = this.repCriteria.siteId;
            int typeId = this.repCriteria.typeId;

                                   
            SqlCommand storedProcCommand = new SqlCommand("sp_REPORT_CheckDailySumBySettleDate_DFSOPS", connection);
            storedProcCommand.CommandType = CommandType.StoredProcedure;
            storedProcCommand.Parameters.Add("@sinceWhen", from);
            storedProcCommand.Parameters.Add("@untilWhen", to);
            storedProcCommand.Parameters.Add("@siteid", siteId);
            storedProcCommand.Parameters.Add("@transtype", typeId);

                  SqlDataReader dataReader = storedProcCommand.ExecuteReader();
                  return dataReader;
            }

            #region Web Form Designer generated code
            override protected void OnInit(EventArgs e)
            {
                  //
                  // CODEGEN: This call is required by the ASP.NET Web Form Designer.
                  //
                  InitializeComponent();
                  base.OnInit(e);
            }
            
            /// <summary>
            /// Required method for Designer support - do not modify
            /// the contents of this method with the code editor.
            /// </summary>
            private void InitializeComponent()
            {    
            }
            #endregion
      }



0
 

Author Comment

by:svasilakos
ID: 19987346
One other thing, the aspx page is  just this that's it, the cs file does all the work

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="CheckDailySettleSumSiteDFSOPS_PDF.aspx.cs" Inherits="WebApp_CheckDailySettleSumSiteDFSOPS_PDF" %>
-steve
0
 
LVL 21

Expert Comment

by:surajguptha
ID: 19987451
Call CreateFooter passing the totaldollar after other rows have been added

private Void CreateFooter(Table table, Decimal total)
{
 Row row = table.Rows.Add( 20 );
 row.Cells.Add( data.GetString(0) , Font.Helvetica, 12, RgbColor.Black, RgbColor.LightGrey, 1 );
 row.Cells.Add( "");
 row.Cells.Add( "");
 row.Cells.Add( "");
 row.Cells.Add( "");
 row.Cells.Add( "");
 row.Cells.Add( "");
 row.Cells.Add(total.ToString());
}
0
 

Author Comment

by:svasilakos
ID: 19987519
OK. I think I'm almost there.  I do not get any errors but I know I'm putting this in the wrong place and not calling it.  Last bit help

Here is what I have now.  Also I had to add SqlDataReader to your CreateFooter() becuase data was not defined.

 private void BuildTable(SqlDataReader data, Table table) //SqlDataReader data, Table table )
        {
          double totaldollar = 0.0;
          CreateColumns(table);
          CreateRowHeadings(table);
          while (data.Read())
         {
         
           CreateRow(table, data);
           CreateFooter(table, data);    
           totaldollar += CreateRow(table, data);
         }
         
        }
       
       
            private void CreateRowHeadings( Table table )
            {
                  Row row = table.Rows.Add( 40, Font.TimesBold, 10, RgbColor.Black, RgbColor.LightGrey );
                  row.Align = CellAlign.Center;
                  row.VAlign = CellVAlign.Top;
                  row.Cells.Add( "Day" );
                  row.Cells.Add( "Present # " );
                  row.Cells.Add( "Present $$" );
                  row.Cells.Add( "Return #" );
                  row.Cells.Add( "Return $$" );
                  row.Cells.Add( "Collect #" );
                  row.Cells.Add( "Collect $$" );
                  row.Cells.Add( "Total #" );
                  row.Cells.Add( "Total $$" );
                  row.Cells.Add( "Echo Date" );
            row.Cells.Add( "DFS Date");
            }
       
       
         private void CreateRow(Table table, SqlDataReader data )
             {
            Row row = table.Rows.Add( 20 );
            row.Cells.Add( data.GetString(0) , Font.Helvetica, 12, RgbColor.Black, RgbColor.LightGrey, 1 );
            row.Cells.Add( data.GetInt32(1).ToString( ));
            row.Cells.Add( data.GetDecimal(2).ToString( "$0.00" ));
            row.Cells.Add( data.GetInt32(3).ToString( ));
            row.Cells.Add(data.GetDecimal(4).ToString("($0.00)"));
            row.Cells.Add( data.GetInt32(5).ToString( ));
            row.Cells.Add(data.GetDecimal(6).ToString("$0.00"));
            row.Cells.Add( data.GetInt32(7).ToString( ));
            row.Cells.Add(data.GetDecimal(8).ToString("$0.00"));
            row.Cells.Add( data.GetString(9));
            row.Cells.Add( data.GetString(10));

            return  (data.GetDouble(8));

                                    
             }

    private void CreateFooter(Table table, SqlDataReader data, Decimal total)
    {
        Row row = table.Rows.Add(20);
        row.Cells.Add(data.GetString(0), Font.Helvetica, 12, RgbColor.Black, RgbColor.LightGrey, 1);
        row.Cells.Add("");
        row.Cells.Add("");
        row.Cells.Add("");
        row.Cells.Add("");
        row.Cells.Add("");
        row.Cells.Add("");
        row.Cells.Add(total.ToString());
    }
0
 
LVL 21

Expert Comment

by:surajguptha
ID: 19987538
private void BuildTable(SqlDataReader data, Table table) //SqlDataReader data, Table table )
        {
          double totaldollar = 0.0;
          CreateColumns(table);
          CreateRowHeadings(table);
          while (data.Read())
         {
         
           CreateRow(table, data);
           totaldollar += CreateRow(table, data);
         }
           CreateFooter(table, totaldollar );            
        }
       
0
 

Author Comment

by:svasilakos
ID: 19987569
Almost done..

If I use double I get an exception

\WebApp\CheckDailySettleSumSiteDFSOPS_PDF.aspx.cs(88): error CS0019: Operator '+=' cannot be applied to operands of type 'double' and 'void'

if I use decimal I get
    Exception message: c:\Inetpub\wwwroot\g8Ph2\WebApp\CheckDailySettleSumSiteDFSOPS_PDF.aspx.cs(81): error CS0664: Literal of type double cannot be implicitly converted to type 'decimal'; use an 'M' suffix to create a literal of this type

Also do I still need

return  (data.GetDecimal(8));     Under the CreateRow method

-Steve




0
 
LVL 21

Expert Comment

by:surajguptha
ID: 19988094
Change this signature    private void CreateRow(Table table, SqlDataReader data )  to
private double CreateRow(Table table, SqlDataReader data )
0
 

Author Comment

by:svasilakos
ID: 19988116
error CS1501: No overload for method 'CreateFooter' takes '2' arguments. I know that's an easy one..

Here is the code:

private void BuildTable(SqlDataReader data, Table table)
         {
          double totaldollar = 0.0;
          CreateColumns(table);
          CreateRowHeadings(table);
          while (data.Read())
         {
         
           CreateRow(table, data);
           totaldollar += CreateRow(table, data);
   
         }
           CreateFooter(table, totaldollar );            
        }

private void CreateFooter(Table table, SqlDataReader data, decimal total)
    {
        Row row = table.Rows.Add(20);
        row.Cells.Add(data.GetString(0), Font.Helvetica, 12, RgbColor.Black, RgbColor.LightGrey, 1);
        row.Cells.Add("");
        row.Cells.Add("");
        row.Cells.Add("");
        row.Cells.Add("");
        row.Cells.Add("");
        row.Cells.Add("");
        row.Cells.Add(total.ToString("$0.00"));
    }
0
 
LVL 21

Expert Comment

by:surajguptha
ID: 19988121
Change CreateFooter(table, totaldollar );  
To
CreateFooter(table, data, totaldollar );  
0
 

Author Comment

by:svasilakos
ID: 19988159
Very sorry to keep hammering you.....

Getting this error:
(92): error CS1502: The best overloaded method match for 'WebApp_CheckDailySettleSumSiteDFSOPS_PDF.CreateFooter(ceTe.DynamicPDF.PageElements.Table, System.Data.SqlClient.SqlDataReader, decimal)' has some invalid arguments


Code:
private void BuildTable(SqlDataReader data, Table table)
         {
          double totaldollar = 0.0;
          CreateColumns(table);
          CreateRowHeadings(table);
          while (data.Read())
         {
         
           CreateRow(table, data);
           totaldollar += CreateRow(table, data);
   
         }
           CreateFooter(table, data, totaldollar );            
        }
0
 
LVL 21

Expert Comment

by:surajguptha
ID: 19988331
private void CreateFooter(Table table, SqlDataReader data, double total)
    {
        Row row = table.Rows.Add(20);
        row.Cells.Add(data.GetString(0), Font.Helvetica, 12, RgbColor.Black, RgbColor.LightGrey, 1);
        row.Cells.Add("");
        row.Cells.Add("");
        row.Cells.Add("");
        row.Cells.Add("");
        row.Cells.Add("");
        row.Cells.Add("");
        row.Cells.Add(total.ToString("$0.00"));
    }

0
 

Author Comment

by:svasilakos
ID: 19988365
(130): error CS0266: Cannot implicitly convert type 'decimal' to 'double'. An explicit conversion exists (are you missing a cast?)

Here is line 130:

private double CreateRow(Table table, SqlDataReader data)
    {
        Row row = table.Rows.Add(20);
        row.Cells.Add(data.GetString(0), Font.Helvetica, 12, RgbColor.Black, RgbColor.LightGrey, 1);
        row.Cells.Add(data.GetInt32(1).ToString());
        row.Cells.Add(data.GetDecimal(2).ToString("$0.00"));
        row.Cells.Add(data.GetInt32(3).ToString());
        row.Cells.Add(data.GetDecimal(4).ToString("($0.00)"));
        row.Cells.Add(data.GetInt32(5).ToString());
        row.Cells.Add(data.GetDecimal(6).ToString("$0.00"));
        row.Cells.Add(data.GetInt32(7).ToString());
        row.Cells.Add(data.GetDecimal(8).ToString("$0.00"));
        row.Cells.Add(data.GetString(9));
        row.Cells.Add(data.GetString(10));

        return (data.GetDecimal(8));   //This is line 130


    }


    private void CreateFooter(Table table, SqlDataReader data, double total)
    {
        Row row = table.Rows.Add(20);
        row.Cells.Add(data.GetString(0), Font.Helvetica, 12, RgbColor.Black, RgbColor.LightGrey, 1);
        row.Cells.Add("");
        row.Cells.Add("");
        row.Cells.Add("");
        row.Cells.Add("");
        row.Cells.Add("");
        row.Cells.Add("");
        row.Cells.Add(total.ToString("$0.00"));
    }
0
 
LVL 21

Expert Comment

by:surajguptha
ID: 19988385
I have changed all of them to Decimal. No Double's anymore.

private Decimal CreateRow(Table table, SqlDataReader data)
    {
        Row row = table.Rows.Add(20);
        row.Cells.Add(data.GetString(0), Font.Helvetica, 12, RgbColor.Black, RgbColor.LightGrey, 1);
        row.Cells.Add(data.GetInt32(1).ToString());
        row.Cells.Add(data.GetDecimal(2).ToString("$0.00"));
        row.Cells.Add(data.GetInt32(3).ToString());
        row.Cells.Add(data.GetDecimal(4).ToString("($0.00)"));
        row.Cells.Add(data.GetInt32(5).ToString());
        row.Cells.Add(data.GetDecimal(6).ToString("$0.00"));
        row.Cells.Add(data.GetInt32(7).ToString());
        row.Cells.Add(data.GetDecimal(8).ToString("$0.00"));
        row.Cells.Add(data.GetString(9));
        row.Cells.Add(data.GetString(10));

        return (data.GetDecimal(8));   //This is line 130


    }


    private void CreateFooter(Table table, SqlDataReader data, Decimal total)
    {
        Row row = table.Rows.Add(20);
        row.Cells.Add(data.GetString(0), Font.Helvetica, 12, RgbColor.Black, RgbColor.LightGrey, 1);
        row.Cells.Add("");
        row.Cells.Add("");
        row.Cells.Add("");
        row.Cells.Add("");
        row.Cells.Add("");
        row.Cells.Add("");
        row.Cells.Add(total.ToString("$0.00"));
    }

private void BuildTable(SqlDataReader data, Table table)
         {
          Decimal totaldollar = 0.0;
          CreateColumns(table);
          CreateRowHeadings(table);
          while (data.Read())
         {
         
           CreateRow(table, data);
           totaldollar += CreateRow(table, data);
   
         }
           CreateFooter(table, data, totaldollar );            
        }
0
 

Author Comment

by:svasilakos
ID: 19988438
Thanks for your patience....I know what you mean now about not using SqlDataReader

I made those changes, but.. now

Exception information:
    Exception type: HttpCompileException
    Exception message: c:\Inetpub\wwwroot\g8Ph2\WebApp\CheckDailySettleSumSiteDFSOPS_PDF.aspx.cs(82): error CS0664: Literal of type double cannot be implicitly converted to type 'decimal'; use an 'M' suffix to create a literal of this type

I can change the data type in my stored procedure...I'm using smallmoney right now

0
 
LVL 21

Expert Comment

by:surajguptha
ID: 19988723
which line does this error occur in?
Can u change and try?
0
 

Author Comment

by:svasilakos
ID: 19988791
It occurs in this line, I need to rework the query a bit. It is currency I'm dealing with so I do not want to drop the decimal ( or cents I should say)

private void BuildTable(SqlDataReader data, Table table)
    {
        Decimal totaldollar = 0.0;  // this is line 82
        CreateColumns(table);
        CreateRowHeadings(table);
        while (data.Read())
        {

            CreateRow(table, data);
            totaldollar += CreateRow(table, data);

        }
        CreateFooter(table, data, totaldollar);
    }










0
 

Author Comment

by:svasilakos
ID: 19988798
I tried changing to Int but same problem...

I'll keep trying











0
 
LVL 21

Expert Comment

by:surajguptha
ID: 19988819
Try Decimal totaldollar = 0.0D; or
Decimal totaldollar = 0.0M;
0
 

Author Comment

by:svasilakos
ID: 19990611
Strange. Using M got rid of the error but I get

Exception information:
    Exception type: InvalidOperationException
    Exception message: Invalid attempt to read when no data is present.
0
 
LVL 21

Accepted Solution

by:
surajguptha earned 2000 total points
ID: 19991016
Before every
row.Cells.Add(data.GetString(10));
check if it is null
if(data != dbnull.value)
{
row.Cells.Add(data.GetString(10));
}

Do this for all Data.GetXXX
0
 

Author Comment

by:svasilakos
ID: 19991143
Do I need to define dbnull?

I'm getting

error CS0103: The name 'dbnull' does not exist in the current context

The other thing is I know all the columns have data: This is how the report looks
Day # Settled $'s Settled # of NSF NSF Amount # Collected Collect Amount Total Settled NET Settled Amount Echo Settle Date DFS Settle Date
09/04/2007 53 $7,180.00  2 ($500.00)  0 $0.00  55 $6,680.00  09/04/2007 09/06/2007
09/05/2007 11 $1,545.00  1 ($50.00)  0 $0.00  12 $1,495.00  09/05/2007 09/07/2007
09/06/2007 13 $1,375.00  0 ($0.00)  0 $0.00  13 $1,375.00  09/06/2007 09/10/2007
09/07/2007 22 $2,565.00  4 ($400.00)  0 $0.00  26 $2,165.00  09/07/2007 09/11/2007
09/10/2007 40 $5,965.00  0 ($0.00)  0 $0.00  40 $5,965.00  09/10/2007 09/12/2007
09/11/2007 15 $2,310.00  2 ($500.00)  0 $0.00  17 $1,810.00  09/11/2007 09/13/2007
09/12/2007 9 $960.00  0 ($0.00)  0 $0.00  9 $960.00  09/12/2007 09/14/2007
09/13/2007 13 $1,450.00  4 ($1,200.00)  0 $0.00  17 $250.00  09/13/2007 09/17/2007

-Steve

0
 
LVL 21

Expert Comment

by:surajguptha
ID: 19991245
DBNull.Value not dbnull.value

Buddy, with all due respects i have a suggestion. My guess is that If you got some training on c# , it would really help increase your productivity.
0
 

Author Comment

by:svasilakos
ID: 19991264
I fully agree. Enough said. I really appreaciate the nearly entire plus you spent on this....I'll take your suggestion..

Cheers
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Loops Section Overview
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question