locked
ASP.NET Populating Gridview (Complex) + Calulating 2 Columns with Stored Procedures. RRS feed

  • Question

  •                                                         

    I need to populate a Gridview, but I also need to populate 2 of fields using stored procedures to calculate them. I have include the Gridview Code, and the Stored Procedure below:

    How do I get it so, that I can populate each of the given fields: CurrentBalance, RemainingPayments below.
    The reason is: I have subscriptions, and would like to return how much time is left on the subscription, and what the balance is:    

                <asp:GridView runat="server" ID="dgReport_Transaction"
                AutoGenerateColumns="false"
                GridLines="None"
                AllowPaging="true"
                PageSize="50"
                OnPageIndexChanging="dgReport_Transaction_PageIndexChanging"
                CssClass="mGrid"
                PagerStyle-CssClass="pgr"
                AlternatingRowStyle-CssClass="alt" PagerSettings-Position="Bottom" >
                <Columns>
                    <asp:BoundField DataField="ID" Visible="false" HeaderText="ID" />
                    <asp:BoundField DataField="TransactionPrice" Visible="false" HeaderText="Price" DataFormatString="{0:0.00}" />
                    <asp:BoundField DataField="TransactionPaymentType" Visible="false" HeaderText="Method" />
                    <asp:BoundField DataField="TransactionInstallmentNumber" Visible="false" HeaderText="Installment" />
                    <asp:BoundField DataField="TransactionDepositValue" Visible="false" HeaderText="Deposit" DataFormatString="{0:0.00}" />
                    <asp:BoundField DataField="TransactionAmountPaid" Visible="false" HeaderText="Amount Paid" DataFormatString="{0:0.00}" />
                    <asp:BoundField DataField="TransactionPurchaseDate" Visible="false" HeaderText="Payment Date" />

    //This Lines here need to be calulated:

                    //See Stored Procedure below
                    <asp:BoundField DataField="CurrentBalance" Visible="false" HeaderText="Current Balance" DataFormatString="{0:0.00}" />
                    <asp:BoundField DataField="RemainingPayments" Visible="false" HeaderText="Number of Remaining Payments" />
                </Columns>
                <PagerSettings Position="Bottom" Mode="NumericFirstLast" />
                </asp:GridView>

            To populate the Gridview:     

            try
            {
                SqlDataAdapter objAdapter;
                DataSet objDataSet;
                String strSQLQuery;

                strSQLQuery = "SELECT * FROM Transactions " +
                              "INNER JOIN UserProfiles ON Transactions.TransactionUserName = UserProfiles.UserUsername ";

                SqlCommand objCommand = new SqlCommand(strSQLQuery, myConnection);

                objAdapter = new SqlDataAdapter(objCommand);
                objDataSet = new DataSet();
                objAdapter.Fill(objDataSet);
                dgReport_Transaction.DataSource = objDataSet;
                dgReport_Transaction.DataBind();
            }
            catch (Exception ex)
            {
                lblMessage2.Text = "Error: " + ex;
            }

            STORED PROCEDURE:

    DECLARE
    @CurrentDate date,
    @MonthDifference int,
    @RemainingValue int,
    @InstallmentValue int,
    @PurchasedDate datetime,
    @InstallmentDate int,
    @PaymentType nvarchar(150),
    @UserName nvarchar(150), @TransId int


    --Grab the Values needed
    SELECT DISTINCT @InstallmentValue=TransactionPaymentInstallmentNumber, @PurchasedDate=TransactionPurchaseDate,
    @InstallmentDate=TransactionInstallmentDate, @PaymentType=TransactionPaymentType FROM Transactions
    INNER JOIN UserProfiles ON Transactions.TransactionUserName = UserProfiles.UserUsername
    WHERE (Transactions.TransactionUserName=@UserName AND Transactions.Id=@TransId )

    --PRINT 'Installment Number: ' + CONVERT(CHAR(15), @InstallmentValue)
    --PRINT 'Pur Date:' + CONVERT(CHAR(15), @PurchasedDate)
    --PRINT 'Ins Date:' + CONVERT(CHAR(15), @InstallmentDate)
    --PRINT 'Payment: ' + CONVERT(CHAR(15), @PaymentType)

    IF (@PaymentType = 'Payment Plan')
        BEGIN
            SET @CurrentDate = GETDATE()
            SET @MonthDifference = DATEDIFF(MM, @PurchasedDate, @CurrentDate)

            IF (@CurrentDate > (SELECT dateadd(day,(@InstallmentDate-datepart(DD,@CurrentDate)),@CurrentDate)))
                BEGIN
                    SET @RemainingValue = (@InstallmentValue - (@MonthDifference + 1))
                END
            ELSE
                BEGIN
                    SET @RemainingValue = (@InstallmentValue - (@MonthDifference))
                END
        END        
    ELSE
        BEGIN
            SET @RemainingValue = 0
        END        

    --PRINT 'Remaing: ' + CONVERT(CHAR(15), @RemainingValue)
    --PRINT 'Current: ' + CONVERT(CHAR(15), @CurrentDate)
    --PRINT 'Month Diff: ' + CONVERT(CHAR(15), @MonthDifference)

    Return(@CurrentDate)




    • Moved by Mike Feng Thursday, June 13, 2013 2:22 AM
    Wednesday, June 12, 2013 1:01 AM

Answers

  • Check this 

    http://www.codeproject.com/Articles/36528/GridView-all-in-one

    The right forum should be http://forums.asp.net

    chanmm


    chanmm

    • Proposed as answer by Lisa Zhu Thursday, June 13, 2013 2:21 AM
    • Marked as answer by Just Karl Tuesday, October 1, 2013 5:18 PM
    Wednesday, June 12, 2013 1:16 AM
  • Hi captmorgan000,

    Welcome to the MSDN Forum.

    For ASP.NET related questions, please refer to this forum:http://forums.asp.net/

    Thanks  for your understanding and support.

    Regards,


    Lisa Zhu [MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by Just Karl Tuesday, October 1, 2013 5:18 PM
    Thursday, June 13, 2013 2:21 AM

All replies

  • For Example:

    I have 20 records in the Transaction table, so I would need to output all 20 records, and calculate Remaining, and CurrentBalance for all 20. 

    What would be the best method to do this?

    Wednesday, June 12, 2013 1:12 AM
  • Check this 

    http://www.codeproject.com/Articles/36528/GridView-all-in-one

    The right forum should be http://forums.asp.net

    chanmm


    chanmm

    • Proposed as answer by Lisa Zhu Thursday, June 13, 2013 2:21 AM
    • Marked as answer by Just Karl Tuesday, October 1, 2013 5:18 PM
    Wednesday, June 12, 2013 1:16 AM
  • Hi captmorgan000,

    Welcome to the MSDN Forum.

    For ASP.NET related questions, please refer to this forum:http://forums.asp.net/

    Thanks  for your understanding and support.

    Regards,


    Lisa Zhu [MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by Just Karl Tuesday, October 1, 2013 5:18 PM
    Thursday, June 13, 2013 2:21 AM