Post
Ask Question
Manish kumar

Manish kumar

  • NA
  • 30
  • 9k

HOW Getting Running total to deposit-withdraw -amount,?

Dec 1 2016 1:00 AM


WE ARE POST TWO TYPE OF PAGE IN WHICH TRYING TO GETTING BALANACE JUST LIKE ANY BANK ACCOUNT PASSBOOK,KINDLY ANY BODY HELP TO TO RESOLVE BELOW THOSE PROBLEMS. I AM LEARNER OF JSP-SQL

EXAMPLE FOR MS ACCESS DATABASE

CUSID-USER-ACCOUNT-DEBIT-CREDIT-BALANCE-SDATA

001-MANISH-4544545-5000-0000-5000-21/11/2016

002-MANISH-4544545-0000-3000-3000-23/11/2016

003-KUMAR-9878987-3000-000-3000-21/11/2016

004-KUMAR-9878987-0000-2000-1000-22/11/2016

005-MANISH-4544545-2500-000-5500-25/11/2016

JSP PAGE CODE FOR ABOVE

WHICH SDATE IS AUTOMATICALY FOR String date = (new java.util.Date()).toString();

DEPOSIT HTML PAGE

<html>

<Head> <center>Welcome to the credit page</center></head>

<form action="http://localhost:8080/commerce/account_debit.jsp" method="post">

<body bgcolor="#ccccdd"><center><table>

<tr><td>Enter the Account Holder Name

</td><td><input type="text" name="Username"></td></tr>

<tr><td>Enter the Account Number

</td><td><input type="text" name="Account"></td></tr>

<tr><td>Enter the Amount-Debit

</td><td><input type="text" name="Debit"></td></tr>

<tr><td><input type="submit" value="credit"></tr></td>

<tr><td><input type="reset" value="reset"></tr></td></table></center>

</body>

</form>

</html>THIS IS TRANSFER FOR DEBIT-account_debit.jsp"

WHICH SDATE IS AUTOMATICALY FOR String date = (new java.util.Date()).toString();

THIS IS JSP PAGE FOR APPEND DATAPASE CODE

<%@ page language="java" import="java.util.*" pageEncoding="ISO-8859-1"%>

<%@page import="java.sql.*"%>

<%@ page import="java.util.Calendar" %>

<%

//java Code 25/11/2016

try

{

//String Account= request.getParameter("Account");

//String Amount = request.getParameter("Amount");

//String Debit = request.getParameter("Debit");

//String Credit=request.getParameter("Credit");

//String Balance=request.getParameter("Balance");

String date = (new java.util.Date()).toString();

String sDate=request.getParameter("sDate");

String Username = request.getParameter("UserName");

int Account = Integer.parseInt(request.getParameter("Account"));

int Debit = Integer.parseInt(request.getParameter("Debit"));

int Credit = Integer.parseInt(request.getParameter("Credit"));

int Balance = Integer.parseInt(request.getParameter("Balance"));

String s="jdbc:odbc:Database2";

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

Connection conn=DriverManager.getConnection(s);

Statement smt=conn.createStatement();

Calendar calendar = Calendar.getInstance();

java.sql.Date startDate = new java.sql.Date(calendar.getTime().getTime());

String sql_debit="SELECT (SUM(debit)*-1) + SUM(credit) Balance FROM abcbank WHERE sDate BETWEEN CURDATE() AND ADDDATE(CURDATE() INTERVAL -30 DAY)";

//String sql_debit= "SELECT sDate, (SUM(Debit)*-1) + SUM(Credit) Balance FROM abcbank GROUP BY sDate";

ResultSet result = smt.executeQuery(sql_debit);

//int count = 0;

while (result.next())

{

result.getInt("Debit");

result.getInt("Credit");

result.getInt("Balance");

result.getDate("startDate");

String update= "UPDATE abcbank SET Balance =Debit + Balance WHERE sDate="+sDate+" ";

PreparedStatement state = conn.prepareStatement(update);

//state.setInt(1,"Balance");

Balance = result.getInt(1);

state.executeUpdate();

}

String sql = "INSERT INTO abcbank (Username, Account, Debit, Credit, Balance,sDate ) VALUES (?,?,?,?,?,?)";

PreparedStatement pstmt = conn.prepareStatement(sql);

pstmt.setString(1,Username);

pstmt.setInt(2,Account);

pstmt.setInt(3,Debit);

pstmt.setInt(4,Credit);

pstmt.setInt(5,Balance);

pstmt.setDate(6,startDate);

int Inserted = pstmt.executeUpdate();

if (Inserted == 0)

{

System.out.println("No inserted successfully!");

}

else if(Inserted == 1)

{

System.out.println("A user is inserted successfully!");

}

conn.close();

}

catch(Exception ex)

{

System.err.println(ex.getMessage());

}

%>

</body>

</html>

-----------------------

ANOTHER EXAMPLE FOR

CUSID-USER-ACCOUNT-AMOUNT--TYPE-BALANCE-SDATA

001-MANISH-454545-5000-DEPOSIT-5000-21/11/2016

002-MANISH-454545-3000-WITHDRAW-2000-23/11/2016

003-MANISH-454545-6000-DEPOSIT-8000-24/11/2016

004-MANISH-454545-2000-DEPOSIT-10000-24/11/2016

005-MANISH-454545-7000-WITHDRAW-3000-25/11/2016
to same another accounts------

-------

HTML PAGE FOR

WHICH SDATE IS AUTOMATICALY FOR String date = (new java.util.Date()).toString();

DEPOSIT HTML PAGE

<html>

<Head> <center>Welcome to the credit page</center></head>

<form action="http://localhost:8080/commerce/account_debit.jsp" method="post">

<body bgcolor="#ccccdd"><center><table>

<tr><td>Enter the Account Holder Name

</td><td><input type="text" name="Username"></td></tr>

<tr><td>Enter the Account Number

</td><td><input type="text" name="Account"></td></tr>

<tr><td>Enter the Amount

</td><td><input type="text" name="Debit"></td></tr>

tr><td>Enter the Amount FOR DEPOSIT TYPE [DEPOSIT][WITHDREW]

</td><td><input type="text" name="TYPE"></td></tr>

<tr><td><input type="submit" value="credit"></tr></td>

<tr><td><input type="reset" value="reset"></tr></td></table></center>

</body>

</form>

THIS IS ANOTHER JSP PAGE FOR APPENED DATABASE

<%@ page contentType="text/html; charset=iso-8859-1" language="java" import="java.sql.*,java.util.*" errorPage="" %>

<%@ page autoFlush="true" %>

<%@ page import="java.lang.*" %>

<%@ page import="java.util.Calendar" %>

<%

//java Code 23/11/2016

try

{

String date = (new java.util.Date()).toString();

String Username = request.getParameter("Username");

//String Account = request.getParameter("Account");

//String Amount = request.getParameter("Amount");

//String Balance = request.getParameter("Balance");

String AType = request.getParameter("AType");

String sDate = request.getParameter("sDate");

int Account = Integer.parseInt(request.getParameter("Account"));

int Amount = Integer.parseInt(request.getParameter("Amount"));

int Balance = Integer.parseInt(request.getParameter("Balance"));

int update = 0;

String s="jdbc:odbc:Database1";

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

Connection con=DriverManager.getConnection(s);

Statement st = con.createStatement();

Calendar calendar = Calendar.getInstance();

java.sql.Date startDate = new java.sql.Date(calendar.getTime().getTime());

String bal_sql = "SELECT Balance FROM Tata WHERE Username='"+Username+"' AND Account ='"+ Account +"' ";

//String sql_up = "UPDATE Tata SET Username='"+Username+"' WHERE Account ='"+ Account +"' ";

ResultSet result = st.executeQuery(bal_sql);

while(result.next())

{

Balance = result.getInt(1);

if(Balance == 0)

{

System.out.println("Current Balance : "+Balance);

Balance = Balance+ Amount;

}

else

{

Balance = Balance+ Amount;

String sql_up = "UPDATE Tata SET Username='"+Username+"' WHERE sDate ='"+ sDate +"' ";

st.executeUpdate(sql_up);

}

String sql = "INSERT INTO Tata (Username, Account, Amount, AType, Balance,sDate ) VALUES (?,?,?,?,?,?)";

PreparedStatement pstmt = con.prepareStatement(sql);

pstmt.setString(1,Username);

pstmt.setInt(2,Account);

pstmt.setInt(3,Amount);

pstmt.setString(4,"DEPOSIT");

pstmt.setInt(5,Balance);

pstmt.setDate(6,startDate);

pstmt.executeUpdate();

con.close();

}

}

catch(Exception ex)

{

System.err.println(ex.getMessage());

}

%>
also plz post proper suggestion with jsp or sql code will great help.


Answers (2)