Results 1 to 5 of 5

Thread: need summary for programatically generated columns in gridview

  1. #1

    Thread Starter
    Hyperactive Member kuldevbhasin's Avatar
    Join Date
    Mar 2008
    Location
    Mumbai, India
    Posts
    488

    need summary for programatically generated columns in gridview

    hi
    i have a report that needs to be generated from the deduction master i.e. there r n no. of deductions in the master and the report should show the deductions for the chqs. being drawn. till this its ok but the part that is confusing me is :

    if there r more than 1 entries for a party then the gridview should show the total in the next line.

    this is very confusing and cant think of anything how to do it. attached is the excel file showing the data have generated the summarry in excel and have marked it red to show what i need.

    the cols. that r being generated r coming from a sql table deduction master.

    also attached is the code for where i am generating the data.
    Code:
      Private Sub GetData(ByVal xToPrint As Boolean)
    
            Dim dTableDet As New DataTable("dTableDedDet")
            'Dim auto As New DataColumn("SrNo", GetType(Integer))
            'auto.AutoIncrement = True
            'auto.AutoIncrementSeed = 1
            'auto.AutoIncrementStep = 1
            'dTableDet.Columns.Add(auto)
    
            'dTableDet.Columns.Add(New DataColumn("Date", GetType(Date)))
            dTableDet.Columns.Add(New DataColumn("SrNo", GetType(Integer)))
            dTableDet.Columns.Add(New DataColumn("PartyName", GetType(String)))
            dTableDet.Columns.Add(New DataColumn("ItemName", GetType(String)))
            dTableDet.Columns.Add(New DataColumn("ChqAmt", GetType(Integer)))
            dTableDet.Columns.Add(New DataColumn("IsSummarry", GetType(Boolean)))
            dTableDet.Columns.Add(New DataColumn("PartyCode", GetType(String)))
            mCmd = "SELECT * From DeductionMaster ORDER BY Name "
            Dim ds_Temp As New DataSet
            Dim da_Temp As New SqlDataAdapter(mCmd, MyConConnection)
            Dim dTab As New DataTable
            da_Temp.Fill(ds_Temp, "DeductionMaster")
            dTab = ds_Temp.Tables(0)
            Dim dTableDedMaster As New DataTable
            dTableDedMaster = ds_Temp.Tables("DeductionMaster")
            Dim a4ColTotal() As Double
            Dim iRowCount As Integer = dTab.Rows.Count - 1
            For i As Integer = 0 To iRowCount
                Dim iRow As DataRow = dTab.Rows(i)
                dTableDet.Columns.Add(New DataColumn(iRow!Name, GetType(Integer)))
                ReDim Preserve a4ColTotal(i + 1)
                a4ColTotal(i) = 0
            Next
            Dim mMaxCol As Integer = iRowCount + 4
            Dim mTotalDeductionCols As Integer = iRowCount
            Dim mChqNo As String = ""
            Dim mDate As Date
            dTableDet.Columns.Add(New DataColumn("GrossAmt", GetType(Integer)))
           
    
            Dim dNewRow As DataRow = dTableDet.NewRow
            dNewRow("PartyName") = "Meera Bhayandar Municipal Corporation"
            dTableDet.Rows.Add(dNewRow)
    
            dNewRow = dTableDet.NewRow
            dNewRow("PartyName") = "Accounts Department"
            dTableDet.Rows.Add(dNewRow)
    
            mCmd = "SELECT CB.CashBookDate, CB.CashBookSrNo, CB.GrossAmount, CB.TotalAmount, CB.RTGSGroupSerialNo, "
            mCmd = mCmd & " CB.SerialNo, CBE.ItemCode,  IM.ItemName as BudgetHead,CB.ChqNo, CB.PartyCode, PM.Name As PartyName "
            mCmd = mCmd & " FROM RTGSCashBook CB "
            mCmd = mCmd & " INNER JOIN RTGSCashBookEntry CBE ON CB.Prd_Code = CBE.Prd_Code AND CB.SerialNo  = CBE.SerialNo  "
            mCmd = mCmd & " INNER JOIN ItemMaster IM ON CBE.ItemCode = IM.ItemCode "
            mCmd = mCmd & " INNER JOIN PartyMaster PM ON CB.PartyCode = PM.Code "
            mCmd = mCmd & " WHERE CB.PRd_Code = @mPRd AND CB.RTGSGroupSerialNo = @mSrNo "
            mCmd = mCmd & " ORDER BY CB.CashBookDate, CB.RTGSGroupSerialNo, CB.PartyCode "
            Dim da_Temp2 As New SqlDataAdapter(mCmd, MyConConnection)
            Dim ds_Temp2 As New DataSet
            Dim dTab2 As New DataTable
            da_Temp2.SelectCommand.Parameters.AddWithValue("@mPRd", X_PrdCode)
            da_Temp2.SelectCommand.Parameters.AddWithValue("@mSrNo", txtGroupSrNo.Text)
    
            da_Temp2.Fill(ds_Temp2, "RTGSCashBook")
            dTab2 = ds_Temp2.Tables("RTGSCashBook")
            iRowCount = dTab2.Rows.Count - 1
    
            Dim mTotalChqAmt As Double = 0
            Dim mTotlGrossAmt As Double = 0
    
            MyConConnection.Open()
            For i As Integer = 0 To iRowCount
                Dim iRow As DataRow = dTab2.Rows(i)
                mChqNo = iRow!ChqNo
                mDate = iRow!CashBookDate
    
                Dim drNewRow As DataRow = dTableDet.NewRow
                'drNewRow("RTGSSerialNo") = iRow!RTGSGroupSerialNo
                'drNewRow("Date") = iRow!CashBookDate
                'drNewRow("DVNo") = iRow!CashBookSrNo
                drNewRow("ItemName") = iRow!BudgetHead
                drNewRow("GrossAmt") = iRow!GrossAmount
                drNewRow("PartyCode") = iRow!PartyCode
                drNewRow("IsSummarry") = False
                'drNewRow("ChqNo") = iRow!ChqNo
                drNewRow("PartyName") = iRow!PartyName
                drNewRow("SrNo") = i + 1
                mTotlGrossAmt = mTotlGrossAmt + iRow!GrossAmount
    
                Dim mTotalAmount As Double = 0
                mCmd = "SELECT * From DeductionMaster ORDER BY Name "
                ds_Temp = New DataSet
                da_Temp = New SqlDataAdapter(mCmd, MyConConnection)
                dTab = New DataTable
                da_Temp.Fill(ds_Temp, "DeductionMaster")
                dTab = ds_Temp.Tables(0)
    
                Dim iRowCount2 As Integer = dTab.Rows.Count - 1
                For i2 As Integer = 0 To iRowCount2
                    Dim iRow2 As DataRow = dTab.Rows(i2)
                    drNewRow(iRow2!Name) = 0
                Next
    
                Dim mTot As Integer = 0
                mCmd = "SELECT CBD.*, DM.Name As DedName From RTGSCashBookDet CBD "
                mCmd = mCmd & " INNER JOIN DeductionMaster DM ON CBD.Code = DM.Code "
                mCmd = mCmd & " WHERE CBD.Prd_Code = @mPRd AND CBD.SerialNo = @mSrNo "
                da_Temp = New SqlDataAdapter(mCmd, MyConConnection)
                da_Temp.SelectCommand.Parameters.AddWithValue("@mPRd", X_PrdCode)
                da_Temp.SelectCommand.Parameters.AddWithValue("@mSrNo", iRow!SerialNo)
                ds_Temp = New DataSet
                dTab = New DataTable
                da_Temp.Fill(ds_Temp, "RTGSCashBookDet")
                dTab = ds_Temp.Tables("RTGSCashBookDet")
                iRowCount2 = dTab.Rows.Count - 1
                For i2 As Integer = 0 To iRowCount2
                    Dim iRow2 As DataRow = dTab.Rows(i2)
                    drNewRow.Item(iRow2!DedName) = iRow2!Amount
                    mTot = mTot + iRow2!Amount
    
                    For iD As Integer = 0 To mTotalDeductionCols
                        Dim iRowDed As DataRow = dTableDedMaster.Rows(iD)
                        If iRowDed!Code = iRow2!Code Then
                            a4ColTotal(iD) = a4ColTotal(iD) + iRow2!Amount
                        End If
                    Next
                Next
                'drNewRow("TotalDed") = mTot
                drNewRow("ChqAmt") = iRow!TotalAmount
                mTotalChqAmt = mTotalChqAmt + iRow!TotalAmount
                'drNewRow("SerialNo") = iRow!SerialNo
                dTableDet.Rows.Add(drNewRow)
            Next
    
            ''' To Show Party Wise Summarry -- trying to generate but cant think of anything
    
            'Dim mPName As String = ""
            'iRowCount = dTableDet.Rows.Count - 1
            'For i As Integer = 0 To iRowCount
            '    Dim iRow As DataRow = dTableDet.Rows(i)
            '    If mPName = "" Then
            '        mPName = iRow!PartyName
            '    End If
    
    
    
            '    mCmd = "SELECT * From DeductionMaster ORDER BY Name "
            '    ds_Temp = New DataSet
            '    da_Temp = New SqlDataAdapter(mCmd, MyConConnection)
            '    dTab = New DataTable
            '    da_Temp.Fill(ds_Temp, "DeductionMaster")
            '    dTab = ds_Temp.Tables(0)
            '    Dim drNewRow As DataRow = dTableDet.NewRow
            '    Dim mTotal As Double = 0
            '    Dim iRowCount2 As Integer = dTab.Rows.Count - 1
            '    For i2 As Integer = 0 To iRowCount2
            '        Dim iRow2 As DataRow = dTab.Rows(i2)
            '        'mTotal = mTotal + iRow2(iRow2!Name)
    
    
    
            '        Dim foundRows As DataRow() = dTableDet.Select("Party = " & iRow!PartyName & " - Total ")
            '        Dim mFound As Boolean = False
            '        For i22 As Integer = 0 To foundRows.GetUpperBound(0)
            '            mFound = True
            '            mTotal = iRow2(iRow2!Name) + foundRows(i2).Item(iRow2!Name)
            '        Next
            '        Dim drNewRow As DataRow
            '        If mFound = False Then
            '            mAmount = 0
            '            drNewRow = dTableRTGS.NewRow
            '            mAmount = iRow!TotalAmount
            '        Else
            '            drNewRow = foundRows(0)
            '        End If
    
            '    Next
    
    
    
    
            'Next
            dNewRow = dTableDet.NewRow
            dNewRow("PartyName") = "Grand Total"
            dNewRow("GrossAmt") = mTotlGrossAmt
            dNewRow("ChqAmt") = mTotalChqAmt
            mCmd = "SELECT * From DeductionMaster ORDER BY Name "
            ds_Temp = New DataSet
            da_Temp = New SqlDataAdapter(mCmd, MyConConnection)
            dTab = New DataTable
            da_Temp.Fill(ds_Temp, "DeductionMaster")
            dTab = ds_Temp.Tables(0)
            Dim iRowCount3 As Integer = dTab.Rows.Count - 1
            For i2 As Integer = 0 To iRowCount3
                Dim iRow2 As DataRow = dTab.Rows(i2)
                Dim mTempTot As Double = a4ColTotal(i2)
                dNewRow(iRow2!Name) = a4ColTotal(i2)
            Next
    
            dTableDet.Rows.Add(dNewRow)
    
            dNewRow = dTableDet.NewRow
            dNewRow("PartyName") = "Cheque No.: " & mChqNo
            dTableDet.Rows.Add(dNewRow)
    
            dNewRow = dTableDet.NewRow
            dNewRow("PartyName") = "Date.: " & Format(mDate, "dd-MMM-yyyy")
            dTableDet.Rows.Add(dNewRow)
    
            dNewRow = dTableDet.NewRow
            dNewRow("PartyName") = "Amount: " & MyFuncCollection.Num2WordConverter(mTotlGrossAmt.ToString)
            dTableDet.Rows.Add(dNewRow)
    
    
            dNewRow = dTableDet.NewRow
            dNewRow("PartyName") = "Bank Name: "
            dTableDet.Rows.Add(dNewRow)
    
            Dim dgMast As New GridView()
            dgMast.AutoGenerateColumns = False
    
            For i As Integer = 0 To dTableDet.Columns.Count - 1
                Dim boundfield As New BoundField()
                boundfield.DataField = dTableDet.Columns(i).ColumnName.ToString()
                boundfield.HeaderText = dTableDet.Columns(i).ColumnName.ToString()
                dgMast.Columns.Add(boundfield)
            Next
    
    
            '' Programmatically register the event-handling method.
            'AddHandler dgMast.RowDataBound, AddressOf RowDataBound
    
            dgMast.DataSource = dTableDet
            dgMast.DataBind()
            dgMast.Width = 600
            dgMast.EnableViewState = True
            dgMast.CssClass = "table table-striped table-hover table-condensed small-top-margin"
    
            PanDedRpt.Controls.Add(dgMast)
            ScriptManager.RegisterStartupScript(Page, Me.[GetType](), "Key", "<script>MakeStaticHeader('" + dgMast.ClientID + "', 450, 1150 , 40 ,false); </script>", False)
    
    
            'mCmd = "SELECT * From DeductionMaster Order BY Name "
            'cmd = New SqlCommand(mCmd, MyConConnection)
            'dr = (cmd.ExecuteReader)
            'While dr.Read
            '    For Each column As DataGridColumn In dgMast.Columns
            '        If column.HeaderText = dr("Code") Then
            '            column.HeaderText = dr("Name") ' String.Concat(dr("Code"), dr("Name"))
            '        End If
            '    Next
            'End While
            'dr.Close()
    
            Session.Add("DedictonReportChecking", dTableDet)
    
    
        End Sub
    Attached Images Attached Images
    The only time you run out of chances is when you stop taking them.
    The mind is like a parachute.
    It doesn’t work unless it’s open.

  2. #2
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: need summary for programatically generated columns in gridview

    The important things when you want "totals" rows is that you are adding the rows yourself (which you are already doing), and that you are storing the totals you want (as you are doing with mTotlGrossAmt and mTotalChqAmt , etc).

    At the moment you don't seem to be storing anything for the new sub-totals rows, so you need to work out what to store:
    • the PartyName (so you can tell if it has changed or not),
    • the amount of rows for that PartyName (so you can tell if it is "more than 1")
    • the totals for that PartyName (based on your example, that seems to be just ChqAmt).


    Rather than adding another loop to try to add the sub-totals rows (which would mean having to work out where to add them, etc), it would be much easier to add them during the existing loop.

    Use one variable for each of the things in the list above (and anything else you want to store), and for each row of data check if the PartyName is the same... if it is, increase the "amount of rows" variable and increase the total(s) variables. If PartyName is different, add the sub-total row if appropriate ("more than 1") and reset the variables.

  3. #3

    Thread Starter
    Hyperactive Member kuldevbhasin's Avatar
    Join Date
    Mar 2008
    Location
    Mumbai, India
    Posts
    488

    Re: need summary for programatically generated columns in gridview

    thanks for ur guidance. will try as ur suggestion and will get back with the result.
    The only time you run out of chances is when you stop taking them.
    The mind is like a parachute.
    It doesn’t work unless it’s open.

  4. #4
    Frenzied Member KGComputers's Avatar
    Join Date
    Dec 2005
    Location
    Cebu, PH
    Posts
    2,020

    Re: need summary for programatically generated columns in gridview

    Here's a C# snippet manipulating a DataTable that's derived from post #2. The third column is the amount and the fourth column is the party code used as indicator to display running total.

    Code:
    string flag = dt.Rows[0][4].ToString(); 
    int subTotal = 0;
    for (int i = 0; i < dt.Rows.Count; i++)
    {
    	if (dt.Rows[i][4].ToString() != flag)
    	{
    		DataRow toInsert = dt.NewRow();
    		toInsert[0] = DBNull.Value;
    		toInsert[1] = string.Format("Total For {0}", dt.Rows[i-1][1].ToString());
    		toInsert[2] = "";
    		toInsert[3] = subTotal;
    		toInsert[4] = null;
    		dt.Rows.InsertAt(toInsert, i);
    		subTotal = 0;
    		flag = dt.Rows[i+1][4].ToString();
    		continue;
    	}
    	else
    	{
    		subTotal += Convert.ToInt32(dt.Rows[i][3].ToString());
    
    		if (i == dt.Rows.Count - 1)
    		{
    			DataRow toInsert = dt.NewRow();
    			toInsert[0] = DBNull.Value;
    			toInsert[1] = string.Format("Total For {0}", dt.Rows[i][1].ToString());
    			toInsert[2] = "";
    			toInsert[3] = subTotal;
    			toInsert[4] = null;
    			dt.Rows.InsertAt(toInsert, i+1);
    			break;
    		}
    	}
    }
    
    GridView1.DataSource = dt;
    GridView1.DataBind();
    Note: Another approach is to add table row to a gridview using JavaScript.
    Last edited by KGComputers; Nov 29th, 2017 at 01:21 AM.
    CodeBank: VB.NET & C#.NET | ASP.NET
    Programming: C# | VB.NET
    Blogs: Personal | Programming
    Projects: GitHub | jsFiddle
    ___________________________________________________________________________________

    Rating someone's post is a way of saying Thanks...

  5. #5

    Thread Starter
    Hyperactive Member kuldevbhasin's Avatar
    Join Date
    Mar 2008
    Location
    Mumbai, India
    Posts
    488

    Re: need summary for programatically generated columns in gridview

    thanks a lot KGComputers using ur idea i got to work it around here is the code thats working...posting the code just in case someone may find it useful

    Code:
       Private Sub GetData(ByVal xToPrint As Boolean)
    
            Dim dTableDet As New DataTable("dTableDedDet")
            'Dim auto As New DataColumn("SrNo", GetType(Integer))
            'auto.AutoIncrement = True
            'auto.AutoIncrementSeed = 1
            'auto.AutoIncrementStep = 1
            'dTableDet.Columns.Add(auto)
    
            'dTableDet.Columns.Add(New DataColumn("Date", GetType(Date)))
    
            Dim dTableTotals As New DataTable
            dTableTotals.Columns.Add(New DataColumn("SrNo", GetType(Integer)))
            dTableTotals.Columns.Add(New DataColumn("PartyName", GetType(String)))
            dTableTotals.Columns.Add(New DataColumn("ItemName", GetType(String)))
            dTableTotals.Columns.Add(New DataColumn("ChqAmt", GetType(Integer)))
            dTableTotals.Columns.Add(New DataColumn("IsSummarry", GetType(Boolean)))
            dTableTotals.Columns.Add(New DataColumn("PartyCode", GetType(String)))
    
    
    
            dTableDet.Columns.Add(New DataColumn("SrNo", GetType(Integer)))
            dTableDet.Columns.Add(New DataColumn("PartyName", GetType(String)))
            dTableDet.Columns.Add(New DataColumn("ItemName", GetType(String)))
            dTableDet.Columns.Add(New DataColumn("ChqAmt", GetType(Integer)))
            dTableDet.Columns.Add(New DataColumn("IsSummarry", GetType(Boolean)))
            dTableDet.Columns.Add(New DataColumn("PartyCode", GetType(String)))
            mCmd = "SELECT * From DeductionMaster ORDER BY Name "
            Dim ds_Temp As New DataSet
            Dim da_Temp As New SqlDataAdapter(mCmd, MyConConnection)
            Dim dTab As New DataTable
            da_Temp.Fill(ds_Temp, "DeductionMaster")
            dTab = ds_Temp.Tables(0)
            Dim dTableDedMaster As New DataTable
            dTableDedMaster = ds_Temp.Tables("DeductionMaster")
            Dim a4ColTotal() As Double
            Dim iRowCount As Integer = dTab.Rows.Count - 1
            For i As Integer = 0 To iRowCount
                Dim iRow As DataRow = dTab.Rows(i)
                dTableDet.Columns.Add(New DataColumn(iRow!Name, GetType(Integer)))
                dTableTotals.Columns.Add(New DataColumn(iRow!Name, GetType(Integer)))
                ReDim Preserve a4ColTotal(i + 1)
                a4ColTotal(i) = 0
            Next
            'Dim mMaxCol As Integer = iRowCount + 4
            Dim mTotalDeductionCols As Integer = iRowCount
            Dim mChqNo As String = ""
            Dim mDate As Date
            dTableDet.Columns.Add(New DataColumn("GrossAmt", GetType(Integer)))
            dTableTotals.Columns.Add(New DataColumn("GrossAmt", GetType(Integer)))
            Dim dNewRow As DataRow
            'Dim dNewRow As DataRow = dTableDet.NewRow
            'dNewRow("PartyName") = "Meera Bhayandar Municipal Corporation"
            'dTableDet.Rows.Add(dNewRow)
    
            'dNewRow = dTableDet.NewRow
            'dNewRow("PartyName") = "Accounts Department"
            'dTableDet.Rows.Add(dNewRow)
    
    
            mCmd = "SELECT CB.CashBookDate, CB.CashBookSrNo, CB.GrossAmount, CB.TotalAmount, CB.RTGSGroupSerialNo, "
            mCmd = mCmd & " CB.SerialNo, CBE.ItemCode,  IM.ItemName as BudgetHead,CB.ChqNo, CB.PartyCode, PM.Name As PartyName "
            mCmd = mCmd & " FROM RTGSCashBook CB "
            mCmd = mCmd & " INNER JOIN RTGSCashBookEntry CBE ON CB.Prd_Code = CBE.Prd_Code AND CB.SerialNo  = CBE.SerialNo  "
            mCmd = mCmd & " INNER JOIN ItemMaster IM ON CBE.ItemCode = IM.ItemCode "
            mCmd = mCmd & " INNER JOIN PartyMaster PM ON CB.PartyCode = PM.Code "
            mCmd = mCmd & " WHERE CB.PRd_Code = @mPRd AND CB.RTGSGroupSerialNo = @mSrNo "
            mCmd = mCmd & " ORDER BY CB.CashBookDate, CB.RTGSGroupSerialNo, CB.PartyCode "
            Dim da_Temp2 As New SqlDataAdapter(mCmd, MyConConnection)
            Dim ds_Temp2 As New DataSet
            Dim dTab2 As New DataTable
            da_Temp2.SelectCommand.Parameters.AddWithValue("@mPRd", X_PrdCode)
            da_Temp2.SelectCommand.Parameters.AddWithValue("@mSrNo", txtGroupSrNo.Text)
    
            da_Temp2.Fill(ds_Temp2, "RTGSCashBook")
            dTab2 = ds_Temp2.Tables("RTGSCashBook")
            iRowCount = dTab2.Rows.Count - 1
    
            Dim mTotalChqAmt As Double = 0
            Dim mTotlGrossAmt As Double = 0
    
            MyConConnection.Open()
            For i As Integer = 0 To iRowCount
                Dim iRow As DataRow = dTab2.Rows(i)
                mChqNo = iRow!ChqNo
                mDate = iRow!CashBookDate
    
                Dim drNewRow As DataRow = dTableDet.NewRow
                'drNewRow("RTGSSerialNo") = iRow!RTGSGroupSerialNo
                'drNewRow("Date") = iRow!CashBookDate
                'drNewRow("DVNo") = iRow!CashBookSrNo
                drNewRow("ItemName") = iRow!BudgetHead
                drNewRow("GrossAmt") = iRow!GrossAmount
                drNewRow("PartyCode") = iRow!PartyCode
                drNewRow("IsSummarry") = False
                'drNewRow("ChqNo") = iRow!ChqNo
                drNewRow("PartyName") = iRow!PartyName
                drNewRow("SrNo") = i + 1
                mTotlGrossAmt = mTotlGrossAmt + iRow!GrossAmount
    
                Dim mTotalAmount As Double = 0
                mCmd = "SELECT * From DeductionMaster ORDER BY Name "
                ds_Temp = New DataSet
                da_Temp = New SqlDataAdapter(mCmd, MyConConnection)
                dTab = New DataTable
                da_Temp.Fill(ds_Temp, "DeductionMaster")
                dTab = ds_Temp.Tables(0)
    
                Dim iRowCount2 As Integer = dTab.Rows.Count - 1
                For i2 As Integer = 0 To iRowCount2
                    Dim iRow2 As DataRow = dTab.Rows(i2)
                    drNewRow(iRow2!Name) = 0
                Next
    
                Dim mTot As Integer = 0
                mCmd = "SELECT CBD.*, DM.Name As DedName From RTGSCashBookDet CBD "
                mCmd = mCmd & " INNER JOIN DeductionMaster DM ON CBD.Code = DM.Code "
                mCmd = mCmd & " WHERE CBD.Prd_Code = @mPRd AND CBD.SerialNo = @mSrNo "
                da_Temp = New SqlDataAdapter(mCmd, MyConConnection)
                da_Temp.SelectCommand.Parameters.AddWithValue("@mPRd", X_PrdCode)
                da_Temp.SelectCommand.Parameters.AddWithValue("@mSrNo", iRow!SerialNo)
                ds_Temp = New DataSet
                dTab = New DataTable
                da_Temp.Fill(ds_Temp, "RTGSCashBookDet")
                dTab = ds_Temp.Tables("RTGSCashBookDet")
                iRowCount2 = dTab.Rows.Count - 1
                For i2 As Integer = 0 To iRowCount2
                    Dim iRow2 As DataRow = dTab.Rows(i2)
                    drNewRow.Item(iRow2!DedName) = iRow2!Amount
                    mTot = mTot + iRow2!Amount
    
                    For iD As Integer = 0 To mTotalDeductionCols
                        Dim iRowDed As DataRow = dTableDedMaster.Rows(iD)
                        If iRowDed!Code = iRow2!Code Then
                            a4ColTotal(iD) = a4ColTotal(iD) + iRow2!Amount
                        End If
                    Next
                Next
                'drNewRow("TotalDed") = mTot
                drNewRow("ChqAmt") = iRow!TotalAmount
                mTotalChqAmt = mTotalChqAmt + iRow!TotalAmount
                'drNewRow("SerialNo") = iRow!SerialNo
                dTableDet.Rows.Add(drNewRow)
            Next
    
            Dim iR As DataRow = dTableDet.Rows(0)
            Dim mPName As String = iR!PartyName
            Dim mCCount As Integer = dTableDet.Columns.Count - 1
            iRowCount = dTableDet.Rows.Count - 1
            Dim mChqTotal As Double = 0
            Dim mTotals(mCCount - 6) As Double
    
    
            Dim i22 As Integer = 0
            Do While (i22 <= dTableDet.Rows.Count - 1)
                If ((dTableDet.Rows(i22)("PartyName").ToString <> mPName) OrElse (i22 = dTableDet.Rows.Count)) Then
                    Dim toInsert As DataRow = dTableDet.NewRow
                    Dim mPartyN As String = dTableDet.Rows((i22 - 1))("PartyName").ToString
                    toInsert(0) = 0
                    toInsert(1) = String.Format("Total  {0} ", dTableDet.Rows((i22 - 1))("PartyName").ToString)
                    toInsert(2) = " "
                    toInsert(3) = mChqTotal
                    toInsert(4) = True
                    toInsert(5) = dTableDet.Rows((i22 - 1))("PartyCode").ToString
    
                    Dim mSubTotal As Double = 0
                    For i2 As Integer = 6 To mCCount
                        toInsert(i2) = mTotals(i2 - 6)
                    Next
                    'dTableTotals.Rows.Add(toInsert) ' InsertAt(toInsert, i)
                    'dTableDet.Rows.Add(toInsert)
                    dTableDet.Rows.InsertAt(toInsert, (i22))
                    mChqTotal = 0
                    mPName = dTableDet.Rows((i22 + 1))("PartyName")
                    For i2 As Integer = 6 To mCCount
                        mTotals(i2 - 6) = 0
                    Next
    
                Else
                    mChqTotal = mChqTotal + dTableDet.Rows(i22)("ChqAmt")
                    For i2 As Integer = 6 To mCCount
                        mTotals(i2 - 6) = mTotals(i2 - 6) + dTableDet.Rows(i22)(i2)
                    Next
                    If (i22 = (dTableDet.Rows.Count - 1)) Then
                        Dim toInsert As DataRow = dTableDet.NewRow
                        toInsert(0) = 0
                        toInsert(1) = String.Format("Total {0} ", dTableDet.Rows(i22)("PartyName").ToString)
                        toInsert(2) = " "
                        toInsert(3) = mChqTotal
                        toInsert(4) = True
    
    
                        For i2 As Integer = 6 To mCCount
                            toInsert(i2) = mTotals(i2 - 6)
                        Next
                        'dTableTotals.Rows.Add(toInsert)
                        dTableDet.Rows.InsertAt(toInsert, (i22 + 1))
                        'dTableDet.Rows.Add(toInsert)
                        'TODO: Warning!!! break;If
                        Exit Do
                    End If
    
                End If
    
    
    
                i22 = (i22 + 1)
            Loop
    
            dNewRow = dTableDet.NewRow
            dNewRow("PartyName") = "Grand Total"
            dNewRow("GrossAmt") = mTotlGrossAmt
            dNewRow("ChqAmt") = mTotalChqAmt
            mCmd = "SELECT * From DeductionMaster ORDER BY Name "
            ds_Temp = New DataSet
            da_Temp = New SqlDataAdapter(mCmd, MyConConnection)
            dTab = New DataTable
            da_Temp.Fill(ds_Temp, "DeductionMaster")
            dTab = ds_Temp.Tables(0)
            Dim iRowCount3 As Integer = dTab.Rows.Count - 1
            For i2 As Integer = 0 To iRowCount3
                Dim iRow2 As DataRow = dTab.Rows(i2)
                Dim mTempTot As Double = a4ColTotal(i2)
                dNewRow(iRow2!Name) = a4ColTotal(i2)
            Next
    
            dTableDet.Rows.Add(dNewRow)
    
            dNewRow = dTableDet.NewRow
            dNewRow("PartyName") = "Cheque No.: " & mChqNo
            dTableDet.Rows.Add(dNewRow)
    
            dNewRow = dTableDet.NewRow
            dNewRow("PartyName") = "Date.: " & Format(mDate, "dd-MMM-yyyy")
            dTableDet.Rows.Add(dNewRow)
    
            dNewRow = dTableDet.NewRow
            dNewRow("PartyName") = "Amount: " & MyFuncCollection.Num2WordConverter(mTotlGrossAmt.ToString)
            dTableDet.Rows.Add(dNewRow)
    
    
            dNewRow = dTableDet.NewRow
            dNewRow("PartyName") = "Bank Name: "
            dTableDet.Rows.Add(dNewRow)
    
            Dim dgMast As New GridView()
            dgMast.AutoGenerateColumns = False
    
            For i As Integer = 0 To dTableDet.Columns.Count - 1
                Dim boundfield As New BoundField()
                boundfield.DataField = dTableDet.Columns(i).ColumnName.ToString()
                boundfield.HeaderText = dTableDet.Columns(i).ColumnName.ToString()
                dgMast.Columns.Add(boundfield)
            Next
            dgMast.DataSource = dTableDet
            dgMast.DataBind()
            dgMast.Width = 600
            dgMast.EnableViewState = True
            dgMast.CssClass = "table table-striped table-hover table-condensed small-top-margin"
    
            PanDedRpt.Controls.Add(dgMast)
            ScriptManager.RegisterStartupScript(Page, Me.[GetType](), "Key", "<script>MakeStaticHeader('" + dgMast.ClientID + "', 450, 1150 , 40 ,false); </script>", False)
    
    
            Session.Add("DedictonReportChecking", dTableDet)
    
    
        End Sub
    Last edited by kuldevbhasin; Nov 29th, 2017 at 10:15 AM. Reason: updating
    The only time you run out of chances is when you stop taking them.
    The mind is like a parachute.
    It doesn’t work unless it’s open.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width