upadate database

ms_lost
ms_lost used Ask the Experts™
on

i have  a problem with updating the database.. using these two parameters:
'"+d_amount+"'  
'"+user+"'

      String bsql;
     bsql="UPDATE Accounts set balance='"+d_amount+"'  where id='"+user+"'";
     statement.executeUpdate(sql);
      msg="record updated";
     

the error msg i'm getting is :
javax.servlet.ServletException: No row count was produced


Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Mick BarryJava Developer
Top Expert 2010

Commented:
Id suggest using a PreparedStatement which will handle quoting and escaping for you.

Without doing that it depends on the type of the columns. If they are ints then you shouldn't be quoting them.

bsql="UPDATE Accounts set balance="+d_amount+"  where id="+user;

Commented:
to exemplify what objects stated (DO NOT AWARD PTS TO ME AS THIS IS JUST SAMPLE CODE for objects suggestion)

try {
  Connection con = DriverManager.getConnection(url);
  String update = "UPDATE Accounts set balance = ? where id = ?";
  PreparedStatement ps = con.prepareStatement(update);
  ResultSet rs = null;

  for(int i=0;i<10;i++) {
//  PreparedStatement Example
// change these to the right data types
    ps.setDouble(1,d_amount);
    ps.setLong(2, user);
    rs = ps.executeQuery();
  }
} catch (SQLException sqle) {sqle.printStackTrace();}

HTH,
CJ
Mick BarryJava Developer
Top Expert 2010

Commented:
CJ,

> (DO NOT AWARD PTS TO ME AS THIS IS
> JUST SAMPLE CODE for objects suggestion)

Your politeness is appreciated :-)

Learn Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

Author

Commented:
try{
Connection connection= DriverManager.getConnection("jdbc:odbc:DSNaccounts");

String update = "UPDATE Accounts set balance = '"+d_amount+"' where id = '"+user+"'";
     
PreparedStatement ps = connection.prepareStatement(update);
ResultSet rrs = null;

      for(int i=0;i<10;i++)
     {
//  PreparedStatement Example
//  change these to the right data types
         ps.setDouble(1,d_amount);
            ps.setString(2, user);
            rrs = ps.executeQuery();
      }

i got this error msg:java.lang.NullPointerException
} catch (SQLException sqle) {sqle.printStackTrace();}

Author

Commented:
try{
Connection connection= DriverManager.getConnection("jdbc:odbc:DSNaccounts");

String update = "UPDATE Accounts set balance = '"+d_amount+"' where id = '"+user+"'";
     
PreparedStatement ps = connection.prepareStatement(update);
ResultSet rrs = null;-----i have another rs in the program that retrieves from database

     for(int i=0;i<10;i++)
    {
//  PreparedStatement Example
//  change these to the right data types
    ps.setDouble(1,d_amount);
    ps.setString(2, user);
    rrs = ps.executeQuery();
     }

} catch (SQLException sqle) {sqle.printStackTrace();}

i got this error msg:java.lang.NullPointerException

Mick BarryJava Developer
Top Expert 2010

Commented:
You call executQuery but it is an update.
You need to call executeUpdate().

If you still get an error then please post the stack trace.

Commented:
objects: np

ms_lost: objects is right.. try this instead:

try{
Connection connection= DriverManager.getConnection("jdbc:odbc:DSNaccounts");

String update = "UPDATE Accounts set balance = ? where id = ?";
   
PreparedStatement ps = connection.prepareStatement(update);
ResultSet rrs = null;-----i have another rs in the program that retrieves from database

    for(int i=0;i<10;i++)
   {
//  PreparedStatement Example
//  change these to the right data types
   ps.setDouble(1,d_amount);
   ps.setString(2, user);
   rrs = ps.executeUpdate();
    }

} catch (SQLException sqle) {sqle.printStackTrace();}

do realize that the above code is running the SAME update 10 times.  is that the desired outcome?

CJ

Author

Commented:
Error :Incompatible type for =. Can't convert int to java.sql.ResultSet.
     rrs = ps.executeUpdate();
                       

Author

Commented:
the whole code:

<%! double deposit_amount=0.0;String msg=""; %>
<%@ page import="java.sql.*"%>
<%
 Object o_user=session.getAttribute("u_session");
 String user = (String)o_user;
 
 String txt_amount=request.getParameter("txtd_amount");
 double d_amount=Double.parseDouble(txt_amount);

 Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
 Connection connection=
 DriverManager.getConnection("jdbc:odbc:DSNaccounts");
 Statement statement=connection.createStatement();

 String sql;

 sql="select balance from Accounts where id='"+user+"'";
 ResultSet rs=statement.executeQuery(sql);
 while (rs.next())
     {
      deposit_amount = rs.getDouble("balance");
     }
     d_amount=d_amount+deposit_amount;

     
     try{
//Connection connection= DriverManager.getConnection("jdbc:odbc:DSNaccounts");

String update = "UPDATE Accounts set balance = '"+d_amount+"' where id = '"+user+"'";
     
PreparedStatement ps = connection.prepareStatement(update);
ResultSet rrs = null;
   for(int i=0;i<10;i++)
  {
//  PreparedStatement Example
//  change these to the right data types
    ps.setDouble(1,d_amount);
    ps.setString(2, user);
    rrs = ps.executeUpdate();
   }

} catch (SQLException sqle) {sqle.printStackTrace();}




%>
Mick BarryJava Developer
Top Expert 2010

Commented:
> rrs = ps.executeUpdate();

updates do *not return result sets.

int rows = ps.executeUpdate();

See your friendly javadoc for more details

Author

Commented:
after the last change..i still get this error:
java.lang.NullPointerException


the whole code as i posted it before
<%! double d_balance=0.0;String msg=""; %>
<%@ page import="java.sql.*"%>
<%
 Object o_user=session.getAttribute("u_session");
 String user = (String)o_user;
 
 String txt_amount=request.getParameter("txtd_amount");
 double d_amount=Double.parseDouble(txt_amount);

 Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
 Connection connection=
 DriverManager.getConnection("jdbc:odbc:DSNaccounts");
 Statement statement=connection.createStatement();

 String sql;

 sql="select balance from Accounts where id='"+user+"'";
 ResultSet rs=statement.executeQuery(sql);
 while (rs.next())
     {
      d_balance = rs.getDouble("balance");
     }
     d_amount=d_amount+d_balance;

     
     try{
//Connection connection= DriverManager.getConnection("jdbc:odbc:DSNaccounts");

String update = "UPDATE Accounts set balance = '"+d_amount+"' where id = '"+user+"'";
     
PreparedStatement ps = connection.prepareStatement(update);
//ResultSet rrs = null;
   for(int i=0;i<10;i++)
  {
//  PreparedStatement Example
//  change these to the right data types
    ps.setDouble(1,d_amount);
    ps.setString(2, user);
   // rrs = ps.executeUpdate();
   int rows = ps.executeUpdate();


   }

} catch (SQLException sqle) {sqle.printStackTrace();}




%>
<%=d_amount%>
<%=msg%>
Mick BarryJava Developer
Top Expert 2010

Commented:
can u post the stack trace.
or at least temm us which line the npe occurs on.

Author

Commented:
java.lang.NullPointerException
     at sun.jdbc.odbc.JdbcOdbcPreparedStatement.clearParameter(JdbcOdbcPreparedStatement.java:1026)

sorry but it's not specified, and what is stack trace :|!!!!
Mick BarryJava Developer
Top Expert 2010

Commented:
I think it is becuase you are running two statements concurrently on the one connection.
Try using two connections.

A stack trace is what is printed from Exception.printStackTrace() showing the call stack at the point the exception occurred.

Author

Commented:
still the same error :(



exception

java.lang.NullPointerException
     at sun.jdbc.odbc.JdbcOdbcPreparedStatement.clearParameter(JdbcOdbcPreparedStatement.java:1026)
     at sun.jdbc.odbc.JdbcOdbcPreparedStatement.setDouble(JdbcOdbcPreparedStatement.java:680)
     at org.apache.jsp.d_0005fverify$jsp._jspService(d_0005fverify$jsp.java:104)
     at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:107)
     at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
     at org.apache.jasper.servlet.JspServlet$JspServletWrapper.service(JspServlet.java:202)
     at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:382)
     at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:474)
     at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
     at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:247)
     at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:193)
     at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:243)
     at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:566)
     at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:472)
     at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:943)
     at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:190)
     at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:566)
     at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:475)
     at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:564)
     at org.apache.catalina.valves.CertificatesValve.invoke(CertificatesValve.java:246)
     at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:564)
     at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:472)
     at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:943)
     at org.apache.catalina.core.StandardContext.invoke(StandardContext.java:2343)
     at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:180)
     at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:566)
     at org.apache.catalina.valves.ErrorDispatcherValve.invoke(ErrorDispatcherValve.java:170)
     at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:564)
     at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:170)
     at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:564)
     at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:468)
     at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:564)
     at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:472)
     at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:943)
     at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:174)
     at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:566)
     at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:472)
     at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:943)
     at org.apache.catalina.connector.http.HttpProcessor.process(HttpProcessor.java:1012)
     at org.apache.catalina.connector.http.HttpProcessor.run(HttpProcessor.java:1107)
     at java.lang.Thread.run(Thread.java:536)

Java Developer
Top Expert 2010
Commented:
You need to change the query to use PreparedStatement to:

"UPDATE Accounts set balance=? where id=?";

Author

Commented:
it's working thanxxxxxxxxxxxxxxxxxxxxxxxxx lol oh i can go to sleep now :)
Mick BarryJava Developer
Top Expert 2010

Commented:
Sweet dreams :-)

http://www.objects.com.au/staff/mick
Brainbench MVP for Java 1
http://www.brainbench.com

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial