i need help on java database arithemetic

#1
mutago

Posted 22 February 2011 - 02:44 PM

Programmer

Members
102 posts

Good day experts, this code was aimed at performing calculation and inserting the total to database. however, it can only add the first and second amount hence


 total = amount +  rs.getInt(1);

for example amount = first or existing amount in the database=300
rs.getInt(1) = second amount to be inputed =200
total =500.
Now when I want to input the third amount as per rs.getInt eg 400
the total amount will still remain 500 instead of 900 that is 300+200+900
My question is how do solve the problem so that the total column will be on the increase whenever new amount as per rs.getInt(1) is added
amount = first table column and data type is int hence getInt(1)
total = second column
student= third column
below is the code



<%@ page language="java" import="java.util.*,java.sql.*,java.text.*;"%>

int amount = Integer.parseInt(request.getParameter("amount").toString());

int total=0;

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


    Connection conn = null;

    String url = "jdbc:mysql://localhost:3306/";

	 String dbName = "test";

    String driver = "com.mysql.jdbc.Driver";

    String username = "root"; 

    String userPassword = "root";

    try {

      Class.forName(driver).newInstance();

      conn = DriverManager.getConnection(url+dbName,username,userPassword);

	  Statement st = conn.createStatement();

	  String strQuery = "select * from student"; 

	  ResultSet rs = st.executeQuery(strQuery);

	 

	   if(rs.next())

		{

		    out.println("in select");

		   if(studentname.equals(""))

			{


              total = amount +  rs.getInt(1);   

			}

}

strQuery = "insert into student set amount='"+amount+"',total='"+total+"',studentname='"+studentname+"'";

             int i = st.executeUpdate(strQuery);

			 if(i>0)

			{

				  response.sendRedirect("success.jsp");

			}

			}

		else

		{

			amount = amount;


	strQuery = "insert into student set amount='"+amount+"',total='"+total+"',studentname='"+studentname+"'";		

			out.println("2 "+strQuery);

			 int i = st.executeUpdate(strQuery);

			 if(i>0)

			{

				  response.sendRedirect("success.jsp");

			}

		}

      System.out.println("Connected to the database");

      conn.close();

      System.out.println("Disconnected from database");

    } catch (Exception e) {

      e.printStackTrace();

    }

 %>

Edited by ZekeDragon, 22 February 2011 - 05:10 PM.
Code = Java does *not* work in the [code] tags.

#2
mutago

Posted 22 February 2011 - 05:08 PM

Programmer

Members
102 posts

Okay now i have done some modifications, but when the student score is added from the input form with respect to a particular student id,
the total will be updating anything it likes. assuming in the first place at test1.jsp i added
student id=2011
student name=mutago
student score=80
function = addition and click submit, the value will be submitted and my ntotal will display 80.
I now go back to test1.jsp and add again

student id=2011
student name=mutago
student score=70
function=addition.
when i click submit, my total is supposed to be 150 ie 80+70 but rather it will update and display any value it likes

If try the third time assuming the score now is 50 , my total is supposed to be 200 ie 80+70+50 but it will keep on displaying any thing it like below is the full code thanks
please i need help, how do i proceed



test1.jsp


<html>

<head>

</head>


<body>


<form  method="post" action="test2.jsp">

<br><br>

<table width="400px" align="center" border=0 ">

<tr>

		<td></td>

		

	</tr>

<tr>

		<td align="center" colspan=2> </td>

		

	</tr>

	<tr>

		<td>Enter Student Id</td>

		<td><input type="text" name="studentid"></td>

            <tr>

		<td>Enter Student Name</td>

		<td><input type="text" name="studentname"></td>

	</tr>


        <tr>

		<td>Student Score</td>

		<td><input type="text" name="score"></td>

	</tr>

	

	

	<tr>

		<td>Select function to perform</td>

		<td>

		<select name="function">

		<option value="-1">---Select function to Perform---</option>

		<option value="Add">Addition</option>

		</select>

		</td>

	</tr>


	

		<td><input type="submit" name="Submit" value="Press Enter"></td>

	</tr>


<tr>

		<td colspan="2"> </td>

		

	</tr>

	

</table>

</form>


</body>

</html>



test2.jsp



<%@ page language="java" import="java.util.*,java.sql.*,java.text.*;"%>

<%

  

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

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

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


   int score = Integer.parseInt(request.getParameter("score").toString());

   int total=0;




    Connection conn = null;

    String url = "jdbc:mysql://localhost:3306/";

	 String dbName = "test";

    String driver = "com.mysql.jdbc.Driver";

    String username = "root"; 

    String userPassword = "root";

    try {

      Class.forName(driver).newInstance();

      conn = DriverManager.getConnection(url+dbName,username,userPassword);

	  Statement st = conn.createStatement();

	  String strQuery = "select * from student"; 

	  //out.println("1" +strQuery);

	  ResultSet rs = st.executeQuery(strQuery);

	 

	   if(rs.next())

		{

		    out.println("in select");

// we are trying to add scores

		   if(function.equals("Add"))

			{

              total = score +  rs.getInt(3); 

              total += score;   

			}

			

			strQuery = "insert into student set studentid='"+studentid+"',studentname='"+studentname+"',score='"+score+"',function='"+function+"',total='"+total+"'";

             int i = st.executeUpdate(strQuery);

			 if(i>0)

			{

				  response.sendRedirect("success.jsp");

			}

			

		}

		

		else{

			total = amount;


			strQuery = "insert into student set studentid='"+studentid+"',studentname='"+studentname+"',score='"+score+"',function='"+function+"',total='"+total+"'";

		  	

			out.println("2 "+strQuery);

			 int i = st.executeUpdate(strQuery);

			 if(i>0)

			{

				  response.sendRedirect("success.jsp");

			}

		}

      System.out.println("Connected to the database");

      conn.close();

      System.out.println("Disconnected from database");

    } catch (Exception e) {

      e.printStackTrace();

    }

 %>

Edited by ZekeDragon, 22 February 2011 - 05:11 PM.
Code = Java does *not* work in the [code] tags.

#3
wim DC

Posted 23 February 2011 - 12:36 AM

Writes binary right handed and hex left handed

Members
2,084 posts

Programming Language:Java, JavaScript, PL/SQL
Learning:Java

Where does the amount variable (total = amount; in the else-branch) on test2.jsp?
It may be a bit weird to insert all your int values as Strings. (with the leading and ending single quote).
This HAS to be done if these columns are declared as varchar tho.
Do you get the "Connected to the database", "disconnected from database" messages? (I'm not sure if the code continues after redirecting)

Why do you do +score 2 times?


total = score +  rs.getInt(3); 

total += score;

I might understand that the 3th, 4th, 5th,.. added end up with wrong total scores, but the 2nd should still be correct.
total = score + rs.getInt(3);
Here you add the new score to the score from the first person. The exact total however, should be stored in the last person. So maybe do rs.last(), instead of rs.next().

#4
mutago

Posted 23 February 2011 - 01:05 PM

Programmer

Members
102 posts

thanks, I have used rs.last(), but just as said only the first and second score is correct but 3rd, 4th and there about is give different total of score. Is there no any work around to ensure that whenever score is added,the total will be updated, i have tried using loops variable but no way. I think where the problem lies is here
total = score + rs.getInt(3);
thanks.