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"
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());
%>
</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
-------
HTML PAGE FOR
<tr><td>Enter the Amount
tr><td>Enter the Amount FOR DEPOSIT TYPE [DEPOSIT][WITHDREW]
</td><td><input type="text" name="TYPE"></td></tr>
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.*" %>
//java Code 23/11/2016
String Username = request.getParameter("Username");
//String Account = request.getParameter("Account");
//String Balance = request.getParameter("Balance");
String AType = request.getParameter("AType");
String sDate = request.getParameter("sDate");
int Amount = Integer.parseInt(request.getParameter("Amount"));
int update = 0;
String s="jdbc:odbc:Database1";
Connection con=DriverManager.getConnection(s);
Statement st = con.createStatement();
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())
if(Balance == 0)
System.out.println("Current Balance : "+Balance);
Balance = Balance+ Amount;
else
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.setInt(3,Amount);
pstmt.setString(4,"DEPOSIT");
pstmt.executeUpdate();
con.close();
H