0
 public class grantLoan extends HttpServlet {
    private static final long serialVersionUID = 1L;
    Connection con;
     Statement st;

    public grantLoan() {
        super();

    }

    public Connection getCon()
    {
          try
          {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection("jdbc:mysql://localhost:3306/microfinance", "root", "");
          }
          catch (Exception e) {

            e.printStackTrace();
        }


          return con;

    }


    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

         response.setContentType("text/html");
            try
            {

                String category = request.getParameter("category");
                System.out.println(category);
                String addr = request.getParameter("addres");
                System.out.println(addr);
                Integer income = request.getIntHeader("sal");
                System.out.println(income);
                Integer amount = request.getIntHeader("amount");
                System.out.println(amount);
                String tenure = request.getParameter("tenure");
                System.out.println(tenure);
                String assets = request.getParameter("surity");
                System.out.println(assets);
                String type_of_payment = request.getParameter("paymentType");
                System.out.println(type_of_payment);

                HttpSession session = request.getSession();
                int accno = (Integer)session.getAttribute("acno");


               con=getCon();

                st =  con.createStatement();

              PreparedStatement pst = (PreparedStatement) con.prepareStatement("insert into loans(Account_no,category,Present_address,Required_Amount,Duration,Assets,income,Payment_type) values('?','?','?','?','?','?','?','?')");
              pst.setInt(2, accno);
              pst.setString(3, category);
              pst.setString(4, addr);
              pst.setInt(5, amount);
              pst.setString(6, tenure);
              pst.setString(7, assets);
              pst.setInt(8, income);
              pst.setString(9, type_of_payment);

             int i= pst.executeUpdate(); 
              if(i==1)
              {
                System.out.println("loans table updated successfully");
                response.sendRedirect("UserHome.jsp");

               }
            }
            catch(Exception exception) { 

            }


    }
     }

I have kept a field loan_id in the table 'loans' as a primary key which has to be automatically incremented. And then I wrote the above query for the insertion of a new record into the table. I am getting these values from another JSP .But my table is not getting updated. plz do solve this..

user3017098
  • 9
  • 2

2 Answers2

0

These aren't variables you're inserting, they're strings

int i= st.executeUpdate("insert into loans(Account_no,category,Present_address,Required_Amount,Duration,Assets,income,Payment_type) values('+accno+','+category+','+addr+','+amount+','+tenure+','+assets+','+income+','+type_of_payment+')");

Your values are all strings. You probably want the variable name. Your problem seems to be that you're using single quotes instead of double quotes, which separate String from variables. It should be something like this:

+ "values(" + accno + ", " + category + ", " + addr + ", "...

You should use a prepared statement instead though for added security

PreparedStatment pst = connection.prepareStatement("insert into loans(Account_no,category,Present_address,Required_Amount,Duration,Assets,income,Payment_type) values(?, ?, ?, ?, ?, ?, ?, ?)");

pst.setInt(1, accno);
pst.setString(2, category);    // should be setXxxx depending on the type of data
pst.setString(3, address);
pst.setString(4, amount);
pst.setString(5, tenure);
pst.setString(6, assets);
pst.setInt(7, income);
pst.setString(8, type_of_payment);

int i = pst.executeUpdate();

if (i == 1) response.sendRedirect("UserHome.jsp");

- OR -

boolean updated = pst.execute();

if (updated) response.sendRedirect("UserHome.jsp");
Paul Samsotha
  • 205,037
  • 37
  • 486
  • 720
  • What method did you try? The prepared statment or changing the single quotes to double quotes? – Paul Samsotha Nov 21 '13 at 11:38
  • Are you still getting SQLException? Did you make sure to use the correct `setXxxx()`? i.e. `setString()` for Strings and `setInt()` for ints? And make sure your `Connection` variable is `connection` – Paul Samsotha Nov 21 '13 at 12:48
  • Actual problem is..execution is getting stopped at this SQL query. The code above this is perfect. And remaining every thing is fine. It is not even showing any exception, but it is not navigating to the next Page i.e. UserHome.jsp. – user3017098 Nov 22 '13 at 04:10
  • Try changing to it `if (i != 1) response.sendRedirect("UserHome.jsp");`. `executeUpdate()` only returns 1 if there is a resultSet, otherwhise it returns 2. Or even `if(i == 2)` just to make sure. – Paul Samsotha Nov 22 '13 at 05:13
  • Also, with the current code, does it even print `System.out.println("loans table updated successfully");`?> If not, it is a problem with the `if`. See above comment – Paul Samsotha Nov 22 '13 at 05:14
  • Now it is throwing null pointer exception and the control stopping execution at the SQL statement itself – user3017098 Nov 22 '13 at 10:19
  • At what line do you get npe? – Paul Samsotha Nov 22 '13 at 10:22
0

try with statement

int i= st.executeUpdate("insert into loans(Account_no,category,Present_address,Required_Amount,Duration,Assets,income,Payment_type) values("+accno+","+category+","+addr+","+amount+","+tenure+","+assets+","+income+","+type_of_payment+")");
Suganthan Madhavan Pillai
  • 5,495
  • 9
  • 51
  • 84