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

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

Welcome to the credit page

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());

}

%>

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

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

Welcome to the credit page

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.

Hello Sir,

As per your link i have followed above this website tutorial, but my tomcat local server is response successful but no data have occurred with MS-ACCESS DATA TABL,

http://www.dotnettricks.com/learn/sqlserver/calculate-running-total-total-of-a-column-and-row

IS UPDATE SQL QUERY HAVE ALSO TO MENTION WITHIN SAME JSP PAGE?I AM POST OUR ALL JSP PAGE CODE ,PLZ REVIEW AND SUGGEST US WHAT IS ERROR AND EVEN POST TO REAL CODE TO WILL BE GREAT HEALP.

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

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

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

<%

try

{

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

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

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

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

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

String sql_debit="select OrderID,OrderDate,Amount,(select sum(Amount) from CustomerOrders where OrderID <= OrderID)'Running_Total' from CustomerOrders";

String s="jdbc:odbc:Database2";

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

Connection conn=DriverManager.getConnection(s);

Statement pstmt=conn.createStatement();

Calendar calendar = Calendar.getInstance();

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

ResultSet result = pstmt.executeQuery(sql_debit);

while (result.next())

{

//OrderID=result.getInt("OrderID");

//OrderDate=result.getDate("startDate");

//Amount= result.getDate("Amount");

//Running_Total= result.getInt("Running_Total");

result.getInt(1);

result.getDate(2);

result.getInt(3);

result.getInt(4);

System.out.println("Sucessfuly Running_Total");

}

conn.close();

}

catch(Exception ex)

{

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

}

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Manish kumar9y ago
Hello Sir,
As per your link i have followed above this website tutorial, but my tomcat local server is response successful but no data have occurred with MS-ACCESS DATA TABL,
http://www.dotnettricks.com/learn/sqlserver/calculate-running-total-total-of-a-column-and-row

IS UPDATE SQL QUERY HAVE ALSO TO MENTION WITHIN SAME JSP PAGE?I AM POST OUR ALL JSP PAGE CODE ,PLZ REVIEW AND SUGGEST US WHAT IS ERROR AND EVEN POST TO REAL CODE TO WILL BE GREAT HEALP.
<%@ page language="java" import="java.util.*" pageEncoding="ISO-8859-1"%>

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

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

<%

try
{
String date = (new java.util.Date()).toString();
String OrderDate=request.getParameter("OrderDate");
int OrderID = Integer.parseInt(request.getParameter("OrderID"));
int Amount = Integer.parseInt(request.getParameter("Amount"));
int Running_Total=Integer.parseInt(request.getParameter("Running_Total"));

String sql_debit="select OrderID,OrderDate,Amount,(select sum(Amount) from CustomerOrders where OrderID <= OrderID)'Running_Total' from CustomerOrders";
String s="jdbc:odbc:Database2";
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection conn=DriverManager.getConnection(s);
Statement pstmt=conn.createStatement();
Calendar calendar = Calendar.getInstance();
java.sql.Date startDate = new java.sql.Date(calendar.getTime().getTime());
ResultSet result = pstmt.executeQuery(sql_debit);
while (result.next())
{
//OrderID=result.getInt("OrderID");
//OrderDate=result.getDate("startDate");
//Amount= result.getDate("Amount");
//Running_Total= result.getInt("Running_Total");
result.getInt(1);
result.getDate(2);
result.getInt(3);
result.getInt(4);
System.out.println("Sucessfuly Running_Total");
}
conn.close();
}
catch(Exception ex)
{
System.err.println(ex.getMessage());
}
%>
Dilip Gupta9y ago
Reffer this Site

http://www.dotnettricks.com/learn/sqlserver/calculate-running-total-total-of-a-column-and-row

Mark answer accepted if Helpful

Enter the Account Holder Name
Enter the Account Number
Enter the Amount
Enter the Amount FOR DEPOSIT TYPE [DEPOSIT][WITHDREW]

Enter the Account Holder Name
Enter the Account Number
Enter the Amount-Debit