I pave post two query ,plz suggest us. i am learner
ex.
Sr.....Debit.......Credit.....Balance.
1......100.........0..........100.....
2......100.........0..........200.....
3......0...........50.........150.....
4......100.........0..........250.....
<%@ page language="java" import="java.util.*" pageEncoding="ISO-8859-1"%>
<%@page import="java.sql.*"%>
<%@ page import="java.util.Calendar" %>
<%
//java Code
String date = (new java.util.Date()).toString();
String UserName = request.getParameter("UserName");
String CusId= request.getParameter("CusId");
String AccountNo = request.getParameter("AccountNo");
String Debit = request.getParameter("Debit");
String Credit=request.getParameter("Credit");
String Balance=request.getParameter("Balance");
String sDate=request.getParameter("sDate");
try
{
String s="jdbc:odbc:Database1";
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= "SELECT sDate, (SUM(Debit)*-1) + SUM(Credit)Balance FROM
abcbank GROUP BY sDate";
ResultSet result = smt.executeQuery(sql);
int count = 0;
while (result.next())
{
result.getString(1,"Debit");
result.getString(2,"Credit");
result.getString(3,"Balance");
result.getDate(4,startDate);
}
String update= "UPDATE abcbank SET Balance =Debit + Balance WHERE
AccountNo="+AccountNo+" ";
PreparedStatement statement = conn.prepareStatement(update);
//statement.setString(1,"AccountNo");
int rowsUpdated = statement.executeUpdate();
if (rowsUpdated ==0)
{
out.println("This Emp does not Exists!");
}
else if(rowsUpdated ==1)
{
out.println("An existing user was updated successfully!");
}
conn.close();
}
catch(Exception ex)
{
System.err.println(ex.getMessage());
}
%>
or
Sr.....Accountno.......Amount...Atype .Balance.
1 44444 50 Deposit 50
2 44444 100 Deposit 150
3 44444 20 Withdraw 130
4 44444 40 withdraw 90
like this
<%@ 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
try
{
String date = (new java.util.Date()).toString();
String Username = request.getParameter("Username");
int Account = Integer.parseInt(request.getParameter("Account"));
int Amount = Integer.parseInt(request.getParameter("Amount"));
int Balance = Integer.parseInt(request.getParameter("Balance"));
String AType = request.getParameter("AType");
String sDate = request.getParameter("sDate");
//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+" ' ";
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 WHERE Username="+Username+" And
Account ="+Account+" ";
update = st.executeUpdate(sql_up);
con.close();
}
}
String sql = "INSERT INTO Tata (Username, Account, Amount, AType,
Balance,sDate ) VALUES ('"+Username+"',"+ Account+","+
Amount+",'DEPOSITE',"+Balance+", '"+date+"' )";
PreparedStatement ps = con.prepareStatement(sql);
ps.setString(1,Username);
ps.setString(2,Account);
ps.setString(3,Amount);
ps.setString(4,"DEPOSITE");
ps.setString(5,Balance);
ps.setDate(6,startDate);
update = ps.executeUpdate(sql);
}
catch(Exception ex)
{
System.err.println(ex.getMessage());
}
%>
|  0 |
