locked
how to implement logic in programme in asp.net? RRS feed

  • Question

  • User1421057020 posted

    I m a student. I Implement the logic in notepad:

    I want to the when user select data from the drop-down list then display the data in grid view:

    and

    I divided the transaction amount in two-part credit and debit See Below query

    I write the below query in notepad:

    **logic for debit query**
    
    if debit has less then 0 than debit
    
    select transactiondate,transactionamount ,case when transactionamount<0 Then 'Debit' As Debit,sum(Debit-totalbalance)As Totalbalance
    from transactions
    
    
    **logic for credit query**
    
    if credit has grater then 0 than credit
    
    select transactiondate,transactionamount ,case when transactionamount>0 Then 'Credit' As Credit,sum(Credit-totalbalance)As Totalbalance
    from transactions

    In Asp.Net

    Bank.aspx

    <body>
        <form id="form1" runat="server">
    
           UserName: <asp:DropDownList ID="ddlusername" runat="server"></asp:DropDownList><br /><br/>
    
    
           Display Data: <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="transactionid" DataSourceID="SqlDataSource1">
                <Columns>
                    <asp:BoundField DataField="transactionid" HeaderText="transactionid" ReadOnly="True" SortExpression="transactionid" />
    
                    **here I divided the credit and debit but how to implement query in the program**
    
                    <%--<asp:BoundField DataField="transactionamount" HeaderText="transactionamount" SortExpression="transactionamount" />--%>
    
                    <asp:BoundField DataField="transactionamount" HeaderText="credit" />
                    <asp:BoundField DataField="transactionamount" HeaderText="debit" />
    
                    <asp:BoundField DataField="totalbalance" HeaderText="totalbalance" SortExpression="totalbalance" />
                    <asp:BoundField DataField="transactiondate" HeaderText="transactiondate" SortExpression="transactiondate" />
                    <asp:BoundField DataField="userid" HeaderText="userid" SortExpression="userid" />
                </Columns>
            </asp:GridView>
            <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="SELECT * FROM [transaction]"></asp:SqlDataSource>
        </form>
    </body>

    Bank.aspx.cs

        public partial class Bank : System.Web.UI.Page
        {
    
            protected void Page_Load(object sender, EventArgs e)
            {
                //here bind the data from database and fill the dropdown list user name
                SqlConnection cn = new SqlConnection(@"connectionstringname");
    
                string com = "Select * from users";
                SqlDataAdapter adpt = new SqlDataAdapter(com, cn);
                DataTable dt = new DataTable();
                adpt.Fill(dt);
                ddlusername.DataSource = dt;
                ddlusername.DataBind();
                ddlusername.DataTextField = "name";
    
                ddlusername.DataBind();
            }
    
            protected void ddlusername_SelectedIndexChanged(object sender, EventArgs e)
            {
                 //how to implement query
                SqlConnection cn = new SqlConnection(@"connectionstringname");
    
                //string com = "";
                //SqlDataAdapter adpt = new SqlDataAdapter(com, cn);
                //DataTable dt = new DataTable();
            }
    
        }

    table user

    table transactions

    I want to

    • when user select alex from the dropdown list then display the alex credit and debit records
    • when user select dm drop-down list then display the dm credit and debit records

    Current Output:

    I want to below Output:

    when user select dropdown list In alex then

    transactionid   credit  debit   totalbalance    transactiondate     userid
    1               5000    -       5000            01/02/2018 00:00:00 1
    4                -      -1500   3500            05/02/2019 00:00:00 1

    I bound the username in the dropdown list from the database successfully.

    how to implement the above query in asp.net??

    Tuesday, March 17, 2020 4:44 PM

Answers

  • User-1330468790 posted

    Hi, raju bhai,

      

    After you explained the problem becomes clear and the solution would be that we construct a proper SQL statement.

    Generally the statement could be like below:

    Select transactionid,
    transactiondate,
    case WHEN transactionamount >= 0 Then null else transactionamount END As Debit,
    case WHEN transactionamount< 0 Then null else transactionamount END As Credit,
    totalbalance,
    userid
    from transactions";

     

    Then we could rebind the gridview based on the selected value from DDL.

    More details, you can refer to below code:

    .aspx Page:

    <form id="form1" runat="server">
            <div>
                UserName:
                <asp:DropDownList ID="ddlusername" runat="server" OnSelectedIndexChanged="ddlusername_SelectedIndexChanged" AutoPostBack="true"></asp:DropDownList><br />
                <br />
    
    
                Display Data:
                <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="transactionid">
                    <Columns>
                        <asp:BoundField DataField="transactionid" HeaderText="transactionid" ReadOnly="True" SortExpression="transactionid" />
    
                        <asp:BoundField DataField="credit" HeaderText="credit" />
                        <asp:BoundField DataField="debit" HeaderText="debit" />
    
                        <asp:BoundField DataField="totalbalance" HeaderText="totalbalance" SortExpression="totalbalance" />
                        <asp:BoundField DataField="transactiondate" HeaderText="transactiondate" SortExpression="transactiondate" />
                        <asp:BoundField DataField="userid" HeaderText="userid" SortExpression="userid" />
                    </Columns>
                </asp:GridView>
    
            </div>
        </form>

    Code behind:

    protected void Page_Load(object sender, EventArgs e)
            {
                if (!IsPostBack)
                {
                    BindGridView();
                    BindDDL();
                }
            }
    
            public void BindGridView()
            {
                string sql = @"Select transactionid,
                            transactiondate, 
                            case WHEN transactionamount >= 0 Then null else transactionamount END As Debit,
                            case WHEN transactionamount< 0 Then null else transactionamount END As Credit,
                            totalbalance,
                            userid
                            from transactions";
                GridView1.DataSource = SelectFromDatabase(sql, null);
                GridView1.DataBind();
            }
    
            public void BindDDL()
            {
                string sql = @"Select userid, name from [user]";
                ddlusername.DataTextField = "name";
                ddlusername.DataValueField = "userid";
                ddlusername.DataSource = SelectFromDatabase(sql, null);
                ddlusername.DataBind();
            }
    
    
    
            public static DataTable SelectFromDatabase(string sql, SqlParameter[] parameters)
            {
                string constr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                using (SqlConnection con = new SqlConnection(constr))
                {
    
                    using (SqlCommand cmd = new SqlCommand(sql, con))
                    {
                        if (parameters != null)
                        {
                            cmd.Parameters.AddRange(parameters);
                        }
    
                        using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
                        {
                            DataTable dt = new DataTable();
                            sda.Fill(dt);
                            return dt;
                        }
                    }
                }
            }
    
            protected void ddlusername_SelectedIndexChanged(object sender, EventArgs e)
            {
                int selectedValue = Convert.ToInt32(ddlusername.SelectedValue);
                string sql = @"Select transactionid,
                            transactiondate, 
                            case WHEN transactionamount >= 0 Then null else transactionamount END As Debit,
                            case WHEN transactionamount< 0 Then null else transactionamount END As Credit,
                            totalbalance,
                            userid
                            from transactions where userid = @userid";
                SqlParameter[] parameters = new SqlParameter[] { new SqlParameter("userid", selectedValue) };
                GridView1.DataSource = SelectFromDatabase(sql, parameters);
                GridView1.DataBind();
    
            }

    Demo:

       

    Note that if you want a better user experience, you should polish the visual stuff like adding a default text "Select an User" to DDL and the value could be -1.

    You could check the value in event selectedIndexChange.

      

    Hope this can help you.

    Best regards,

    Sean

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, March 18, 2020 10:48 AM

All replies

  • User-1519014291 posted

    Hi raju bhai,

    Please share both user and transactions tables data for us , Is transactionamount column consists off credit + debit? 

    Tuesday, March 17, 2020 8:33 PM
  • User1421057020 posted

    I updated my question I add the transaction table and what I want to add the logic in asp.net  and I divided the transaction amount into two parts credit and debit.

    I have no credit and debit field and I have the only transaction amount

    I hope my question is understood.

    Wednesday, March 18, 2020 4:08 AM
  • User1421057020 posted

    please answer the above question because I have not any idea how to implement above query logic in notepad

    Wednesday, March 18, 2020 4:18 AM
  • User-1330468790 posted

    Hello, raju bhai,

      

    Your logic confused me since the database you give could not produce the result based on your description.

    For example, when  transactionid = 4,  transactionamount = -1500 and totalbalance = 4500. However, according to your logic, I can get 3000 for addition but can never get 3500

    Therefore, I think the first thing we need to do is to make the question clear.

     

    Purposes:

    1. The two users share the same account and you want to display what they do for this account.
    2. The two users have their own account and they do credit and debit separately for their own account. What you want is to list the records for one of them.

    Please make sure which one is your purpose.

      

    Details:

    What is the relationship between totalbalance and credit/debit? Why you always use "sum(credit/debit - totalbalance)" since "sum" here should not make sense?

      

    Best regards,

    Sean

    Wednesday, March 18, 2020 7:56 AM
  • User1421057020 posted

    I hope my question is understood and can u tell me I write my logic in noted it is wrong or right??

    Wednesday, March 18, 2020 8:21 AM
  • User-1330468790 posted

    Hi, raju bhai,

     

    I edited my previous post for asking about more details since I am afraid that I am not very clear about the question.

    Main reason is that the data confused me.

    Could you please specify the question?

     

    Thank you for your understanding.

     

    Best regards,

    Sean

    Wednesday, March 18, 2020 8:32 AM
  • User1421057020 posted

    The two users share the same account and you want to display what they do for this account.     

    yes exactly Sir

     question: What is the relationship between total balance and credit/debit? Why you always use "sum(credit/debit - total balance)" since "sum" here should not make sense?

     Answer: I have an only total balance and a total balance(if user credit(+5000) and if user debited(-1000) and I want to divide the debit and credit ) 

    transaction amount have debit and credit I want to divide the transaction amount  In credit and debit  so that is the reason I add the credit and debit field in grid view and remove the transaction amount field

    so that is why I m asking you my logic is right or wrong?

    I hope my question is understood 

    Wednesday, March 18, 2020 8:36 AM
  • User-1519014291 posted

    Hi raju bhai,

    Thank you for posting here

    I have reviewed your business logic is not clear if you want to find a balance for users you should create some thing like this.

    CREATE TABLE tblUsersBalance
    (
      TransDate DATE,
      Credit INT,
      Debit INT,
      UserID INT
    );
    
    
    
    INSERT INTO tblUsersBalance VALUES
    ('2020-01-01',   5000,      NULL , 1),  
    ('2020-01-07',   NULL,      2000 , 1),   
    ('2020-01-11',   5000,      NULL , 1),   
    ('2020-02-03',   6000,      NULL , 1),    
    ('2020-02-06',   NULL,      4000 , 1),    
    ('2020-02-11',   3000,      NULL , 1),   
    ('2020-02-21',   NULL,      1000 , 1),     
    ('2020-02-28',   2000,      NULL , 1),     
    ('2020-03-01',   5000,      NULL , 1) 
    
    
    
    SELECT t2.TransDate, 
           t2.Credit, 
           t2.Debit, 
           SUM(COALESCE(t1.credit, 0) - COALESCE(t1.debit, 0)) AS Balance
    FROM tblUsersBalance t1 
    INNER JOIN tblUsersBalance t2
        ON t1.TransDate <= t2.TransDate
    GROUP BY t2.TransDate, t2.Credit, t2.Debit

    here is the result

    TransDate      Credit   Debit   Balance
    -------- ------ ----- -------
    2020-01-01 5000 NULL 5000 2020-01-07 NULL 2000 3000 2020-01-11 5000 NULL 8000 2020-02-03 6000 NULL 14000 2020-02-06 NULL 4000 10000 2020-02-11 3000 NULL 13000 2020-02-21 NULL 1000 12000 2020-02-28 2000 NULL 14000 2020-03-01 5000 NULL 19000

    Wednesday, March 18, 2020 8:46 AM
  • User1421057020 posted

    Rebin.O.Q  thanks for the help but my logic is The two users share the same account and I want to display what they do for this account.

    I have only transaction amount field and they have the credit(+) and debit(-) that is the reason I m writing the logic in notepad

    I hope my question is understood

    Wednesday, March 18, 2020 9:01 AM
  • User-1330468790 posted

    Hi, raju bhai,

      

    After you explained the problem becomes clear and the solution would be that we construct a proper SQL statement.

    Generally the statement could be like below:

    Select transactionid,
    transactiondate,
    case WHEN transactionamount >= 0 Then null else transactionamount END As Debit,
    case WHEN transactionamount< 0 Then null else transactionamount END As Credit,
    totalbalance,
    userid
    from transactions";

     

    Then we could rebind the gridview based on the selected value from DDL.

    More details, you can refer to below code:

    .aspx Page:

    <form id="form1" runat="server">
            <div>
                UserName:
                <asp:DropDownList ID="ddlusername" runat="server" OnSelectedIndexChanged="ddlusername_SelectedIndexChanged" AutoPostBack="true"></asp:DropDownList><br />
                <br />
    
    
                Display Data:
                <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="transactionid">
                    <Columns>
                        <asp:BoundField DataField="transactionid" HeaderText="transactionid" ReadOnly="True" SortExpression="transactionid" />
    
                        <asp:BoundField DataField="credit" HeaderText="credit" />
                        <asp:BoundField DataField="debit" HeaderText="debit" />
    
                        <asp:BoundField DataField="totalbalance" HeaderText="totalbalance" SortExpression="totalbalance" />
                        <asp:BoundField DataField="transactiondate" HeaderText="transactiondate" SortExpression="transactiondate" />
                        <asp:BoundField DataField="userid" HeaderText="userid" SortExpression="userid" />
                    </Columns>
                </asp:GridView>
    
            </div>
        </form>

    Code behind:

    protected void Page_Load(object sender, EventArgs e)
            {
                if (!IsPostBack)
                {
                    BindGridView();
                    BindDDL();
                }
            }
    
            public void BindGridView()
            {
                string sql = @"Select transactionid,
                            transactiondate, 
                            case WHEN transactionamount >= 0 Then null else transactionamount END As Debit,
                            case WHEN transactionamount< 0 Then null else transactionamount END As Credit,
                            totalbalance,
                            userid
                            from transactions";
                GridView1.DataSource = SelectFromDatabase(sql, null);
                GridView1.DataBind();
            }
    
            public void BindDDL()
            {
                string sql = @"Select userid, name from [user]";
                ddlusername.DataTextField = "name";
                ddlusername.DataValueField = "userid";
                ddlusername.DataSource = SelectFromDatabase(sql, null);
                ddlusername.DataBind();
            }
    
    
    
            public static DataTable SelectFromDatabase(string sql, SqlParameter[] parameters)
            {
                string constr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                using (SqlConnection con = new SqlConnection(constr))
                {
    
                    using (SqlCommand cmd = new SqlCommand(sql, con))
                    {
                        if (parameters != null)
                        {
                            cmd.Parameters.AddRange(parameters);
                        }
    
                        using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
                        {
                            DataTable dt = new DataTable();
                            sda.Fill(dt);
                            return dt;
                        }
                    }
                }
            }
    
            protected void ddlusername_SelectedIndexChanged(object sender, EventArgs e)
            {
                int selectedValue = Convert.ToInt32(ddlusername.SelectedValue);
                string sql = @"Select transactionid,
                            transactiondate, 
                            case WHEN transactionamount >= 0 Then null else transactionamount END As Debit,
                            case WHEN transactionamount< 0 Then null else transactionamount END As Credit,
                            totalbalance,
                            userid
                            from transactions where userid = @userid";
                SqlParameter[] parameters = new SqlParameter[] { new SqlParameter("userid", selectedValue) };
                GridView1.DataSource = SelectFromDatabase(sql, parameters);
                GridView1.DataBind();
    
            }

    Demo:

       

    Note that if you want a better user experience, you should polish the visual stuff like adding a default text "Select an User" to DDL and the value could be -1.

    You could check the value in event selectedIndexChange.

      

    Hope this can help you.

    Best regards,

    Sean

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, March 18, 2020 10:48 AM
  • User1421057020 posted

    thanks for the answer and your effort but here give an error

    Hello Sean Fang

    I post my all code still trying to solve the issue

    web.config

    <connectionStrings>
      <add name="studdbEntities" connectionString="here connection string" providerName="System.Data.EntityClient" />
    </connectionStrings>
    </configuration>

    .aspx

        <form id="form1" runat="server">
            UserName: <asp:DropDownList ID="ddlusername" runat="server"  AutoPostBack="true" OnSelectedIndexChanged="ddlusername_SelectedIndexChanged"></asp:DropDownList><br /><br/>
    
            Display Data:  <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="transactionid">
                    <Columns>
                        <asp:BoundField DataField="transactionid" HeaderText="transactionid" ReadOnly="True" SortExpression="transactionid" />
    
                        <asp:BoundField DataField="credit" HeaderText="credit" />
                        <asp:BoundField DataField="debit" HeaderText="debit" />
    
                        <asp:BoundField DataField="totalbalance" HeaderText="totalbalance" SortExpression="totalbalance" />
                        <asp:BoundField DataField="transactiondate" HeaderText="transactiondate" SortExpression="transactiondate" />
                        <asp:BoundField DataField="userid" HeaderText="userid" SortExpression="userid" />
                    </Columns>
                </asp:GridView>
        </form>
    </body>

    .aspx.cs

            protected void Page_Load(object sender, EventArgs e)
            {
                if(!IsPostBack)
                {
                    BindDDL();           //when i m debugging first method debugging successfully
                    BindGridView();      //when come here second time then going to the here SelectFromDatabase and give the error
                } 
            }
    
            public void BindGridView()
            {
                string sql = @"Select transactionid,
                            transactiondate, 
                            case WHEN transactionamount >= 0 Then null else transactionamount END As Debit,
                            case WHEN transactionamount< 0 Then null else transactionamount END As Credit,
                            totalbalance,
                            userid
                            from transaction";
                GridView1.DataSource = SelectFromDatabase(sql, null);
                GridView1.DataBind();
            }
    
            public void BindDDL()
            {
                string sql = @"Select userid, name from [user]";
                ddlusername.DataTextField = "name";
                ddlusername.DataValueField = "userid";
                ddlusername.DataSource = SelectFromDatabase(sql, null);
                ddlusername.DataBind();
            }
    
            public static DataTable SelectFromDatabase(string sql, SqlParameter[] parameters)
            {
                //string constr = ConfigurationManager.ConnectionStrings["studdbEntities"].ConnectionString;
    
                using (SqlConnection con = new SqlConnection(@"connection string "))
                {
                    using (SqlCommand cmd = new SqlCommand(sql, con))
                    {
                        if (parameters != null)
                        {
                            cmd.Parameters.AddRange(parameters);
                        }
    
                        using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
                        {
                            DataTable dt = new DataTable();
                            sda.Fill(dt);                                 //here give an error
                            return dt;
                        }
                    }
                }
            }
    
            protected void ddlusername_SelectedIndexChanged(object sender, EventArgs e)
            {
                int selectedValue = Convert.ToInt32(ddlusername.SelectedValue);
                string sql = @"Select transactionid,
                                transactiondate, 
                                case WHEN transactionamount >= 0 Then null else transactionamount END As Debit,
                                case WHEN transactionamount< 0 Then null else transactionamount END As Credit,
                                totalbalance,
                                userid
                                from transaction where userid = @userid";
                SqlParameter[] parameters = new SqlParameter[] { new SqlParameter("userid", selectedValue) };
                GridView1.DataSource = SelectFromDatabase(sql, parameters);
                GridView1.DataBind();
            }

    table :

    error is

    I m very near to solve this issue how to solve this issue?

    Thursday, March 19, 2020 5:32 AM
  • User-1330468790 posted

    Hi, raju bhai,

     

    The reason is that you use "transaction" in your sql statement which is a reserved word of SQL server.

    public void BindGridView()
            {
                string sql = @"Select transactionid,
                            transactiondate, 
                            case WHEN transactionamount >= 0 Then null else transactionamount END As Debit,
                            case WHEN transactionamount< 0 Then null else transactionamount END As Credit,
                            totalbalance,
                            userid
                            from transaction";
                GridView1.DataSource = SelectFromDatabase(sql, null);
                GridView1.DataBind();
            }

    You can find it in below document.

    https://docs.microsoft.com/en-us/sql/t-sql/language-elements/reserved-keywords-transact-sql?view=sql-server-ver15

      

    To escape this word, you can use square brackets to enclose "transaction" word like:

    public void BindGridView()
            {
                string sql = @"Select transactionid,
                            transactiondate, 
                            case WHEN transactionamount >= 0 Then null else transactionamount END As Debit,
                            case WHEN transactionamount< 0 Then null else transactionamount END As Credit,
                            totalbalance,
                            userid
                            from [transaction]";
                GridView1.DataSource = SelectFromDatabase(sql, null);
                GridView1.DataBind();
            }

      

    That is the reason why you can always find "[xxx]" in embedded sql script.

    Hope this can help you.

    Best regards,

    Sean

    Thursday, March 19, 2020 8:38 AM