Forum:

JDBC and Relational Databases

not able to insert values in ms access database

Punit Jain

Ranch Hand

Posts: 1143

posted 13 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

Form.jsp

<%-- 
    Document   : New1
    Created on : Feb 7, 2008, 7:26:06 PM
    Author     : Students Of MauCom
--%>

<%@page contentType="text/html" pageEncoding="UTF-8"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
   "http://www.w3.org/TR/html4/loose.dtd">

</form>

</body>
</html>

new2.jsp

my_ins : DNS Name
insert : Table Name

here is the code to inser values into ms access database, bt i m getting an exception here, can anyone help me in this...

exception

exception

org.apache.jasper.JasperException: An exception occurred processing JSP page /new2.jsp at line 19

16:
17:
18: String sqlString= "Insert into insert(ID, u_name, u_post) values("+id1+",'"+nm1+"', '"+po1+"')";
19: int i=st.executeUpdate(sqlString);
20: %>
21: Record Add<%= i %><br>
22:

Gopakumar Naryanan

Ranch Hand

Posts: 72

I like...

posted 13 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

Hey,

Include the above mentioned code in try-catch block.
Because, the Class.forName() throws ClassNotFoundException and getConnection() method throws SQLException.
These are checked Exceptions.
So, you must include the above code in try-catch bock.
Writing java code in JSP is not a good practice!!

Thanks & Regards
Gopakumar

Paul Clapham

Sheriff

Posts: 28411

102

I like...

posted 13 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

Right. And when you catch the exception, write the stack trace to the logs so you can see what it is. Just saying "I got an exception" is not very useful. The information in the stack trace is what you need to see, including the text of the exception itself.

Java 8 (verified skill)
Skill verified by Paul Clapham

Punit Jain

Ranch Hand

Posts: 1143

posted 13 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

Thank you all, and i have convert my jsp to servlet and also added the try catch block, it's successfully running but no data is inserted into my access database, i m using ms access 2007...
here is my servlet:

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

import java.io.IOException;
import java.io.PrintWriter;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.sql.*;

/**
 *
 * @author punit
 */
@WebServlet(name = "insertservlet", urlPatterns = {"/insertservlet"})
public class insertservlet extends HttpServlet {

String id1 = request.getParameter("ID");
        String nm1 = request.getParameter("NM");
        String po1 = request.getParameter("PO");

String sqlString= "Insert into insert(ID, u_name, u_post) values(1,'punit','jain')";
   //             + "("+id1+",'"+nm1+"', '"+po1+"')";
              int i=st.executeUpdate(sqlString);
            /* TODO output your page here
            out.println("<html>");
            out.println("<head>");
            out.println("<title>Servlet insertservlet</title>");  
            out.println("</head>");
            out.println("<body>");
            out.println("<h1>Servlet insertservlet at " + request.getContextPath () + "</h1>");
            out.println("</body>");
            out.println("</html>");
             */
        } 
        
        catch(Exception e)
        {
            e.printStackTrace();
        }
        finally {            
            out.close();
        }
    }

/** 
     * Returns a short description of the servlet.
     * @return a String containing servlet description
     */
    @Override
    public String getServletInfo() {
        return "Short description";
    }// </editor-fold>
}

Paul Clapham

Sheriff

Posts: 28411

102

I like...

posted 13 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

This is rather typical of MS Access. Apparently if you don't close the connection, or commit the transaction, you don't see the update happening.

Punit Jain

Ranch Hand

Posts: 1143

posted 13 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

i have close the connections,

st.close();
db.close();

still i m not getting the values into my database..

Paul Clapham

Sheriff

Posts: 28411

102

I like...

posted 13 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

You are looking in the table named "insert", are you not? (I'm surprised that using a table named "insert" doesn't cause an SQL syntax error.)

Also, what is the value of that "i" variable?

Gopakumar Naryanan

Ranch Hand

Posts: 72

I like...

posted 13 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

Yep, sometimes this happens with MS Access.
Just checkout your DSN. Reopen MS Access ,once you execute the program.
(By the way don't use the SQL key words as table name.. it may cause some ambiguity.. rename the table if you don't mind.)
It should work.

Thanks & Regards
Gopakumar

Punit Jain

Ranch Hand

Posts: 1143

posted 13 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

yes i got it, it's probably my database problem, because in my another database values are inserting.

Thank you all...

Punit Jain

Ranch Hand

Posts: 1143

posted 13 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

i more problem i m facing, i have inserted data into database, bt while fetching the same data it displays nothing on the page, here is my servlet to fetch and display data, please help me in this also,

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

import java.io.IOException;
import java.io.PrintWriter;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.sql.*;

/**
 *
 * @author punit
 */
@WebServlet(name = "checklogin", urlPatterns = {"/checklogin"})
public class checklogin extends HttpServlet {

/** 
     * Processes requests for both HTTP <code>GET</code> and <code>POST</code> methods.
     * @param request servlet request
     * @param response servlet response
     * @throws ServletException if a servlet-specific error occurs
     * @throws IOException if an I/O error occurs
     */
    protected void processRequest(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException, ClassNotFoundException, SQLException {
        response.setContentType("text/html;charset=UTF-8");
        PrintWriter out = response.getWriter();
        try {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
        Connection db=DriverManager.getConnection("jdbc:odbc:my_db");
        Statement st=db.createStatement();
        
        String id1 = request.getParameter("userid");
        String nm1 = request.getParameter("pass");
        
        String data,name;    
        ResultSet rs;
        String sqlString= "select * from login where userId="+id1+" && password="+nm1 ;
        
        //String sqlString= "Insert into login values ('"+id1+"', '"+nm1+"')";
  
             rs= st.executeQuery(sqlString);
             
             
             
              while (rs.next())
            {
                    
            data = rs.getString("userId");
            name = rs.getString("Password");

out.println("<TABLE>");
                out.println("<TR>");
                out.println("<TD> <%= data %> </TD>");
                out.println("<TD> <%= name %> </TD>");
                out.println("</TR>");
                out.println("</TABLE>");
             
         
              }
        } finally {            
            out.close();
        }
    }

// <editor-fold defaultstate="collapsed" desc="HttpServlet methods. Click on the + sign on the left to edit the code.">
    /** 
     * Handles the HTTP <code>GET</code> method.
     * @param request servlet request
     * @param response servlet response
     * @throws ServletException if a servlet-specific error occurs
     * @throws IOException if an I/O error occurs
     */
    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        try {
            processRequest(request, response);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(checklogin.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(checklogin.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

/** 
     * Handles the HTTP <code>POST</code> method.
     * @param request servlet request
     * @param response servlet response
     * @throws ServletException if a servlet-specific error occurs
     * @throws IOException if an I/O error occurs
     */
    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        try {
            processRequest(request, response);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(checklogin.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(checklogin.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

my login.jsp page is simply a form having two text fields and one submit....

Gopakumar Naryanan

Ranch Hand

Posts: 72

I like...

posted 13 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

I had noticed the following:

DSN name is different compared to insertion & this one.
Didn't find HTML, BODY tags.
You got the data , userid.. didn't set in any scope.. how can the jsp gets these values?
Again, writing html code in java code is not a good practice.. try practicing EL with JSP.

Thanks & Regards
Gopakumar

Punit Jain

Ranch Hand

Posts: 1143

posted 13 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

/**
 *
 * @author punit
 */
@WebServlet(name = "Login_Check", urlPatterns = {"/Login"})
public class Login_Check extends HttpServlet {

/** 
     * Processes requests for both HTTP <code>GET</code> and <code>POST</code> methods.
     * @param request servlet request
     * @param response servlet response
     * @throws ServletException if a servlet-specific error occurs
     * @throws IOException if an I/O error occurs
     */
    protected void processRequest(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException, ClassNotFoundException, SQLException {
        response.setContentType("text/html;charset=UTF-8");
        PrintWriter out = response.getWriter();
        try {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
        Connection db=DriverManager.getConnection("jdbc:odbc:my_ins_new");
         Statement st=db.createStatement();
         ResultSet rs;
        String userId=request.getParameter("txtusr") ;
        String pass=request.getParameter("txtpass") ;
        
        String MessageType,Subject, Comments, name, from, tel, FAX;
        String sqlString= "SELECT* from  Feedback_Table where user_name=" +userId;

rs=st.executeQuery(sqlString) ;
        while(rs.next()){
            MessageType=rs.getString("koc") ;
            Subject=rs.getString("wwc");
            Comments=rs.getString("comments");
           // name=rs.getString(4);
            from=rs.getString("user_email");
            tel=rs.getString("user_tel");
            FAX=rs.getString("user_fax");
                                
            //TODO output your page here
            out.println("<html>");
            out.println("<head>");
            out.println("<title>Servlet Login_Check</title>");  
            out.println("</head>");
            out.println("<body>");
            out.println("<h4> " + MessageType + "</h4>");
            out.println("<h4> " + Subject + "</h4>");
            out.println("<h4> " + Comments + "</h4>");
            //out.println("<h4> " + name + "</h4>");
            out.println("<h4> " + from + "</h4>");
            out.println("<h4> " + tel + "</h4>");
            out.println("<h4> " + FAX + "</h4>");
            out.println("</body>");
            out.println("</html>");
             
        }
        
        } finally {            
            out.close();
        }
    }

// <editor-fold defaultstate="collapsed" desc="HttpServlet methods. Click on the + sign on the left to edit the code.">
    /** 
     * Handles the HTTP <code>GET</code> method.
     * @param request servlet request
     * @param response servlet response
     * @throws ServletException if a servlet-specific error occurs
     * @throws IOException if an I/O error occurs
     */
    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        try {
            processRequest(request, response);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(Login_Check.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(Login_Check.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

/** 
     * Handles the HTTP <code>POST</code> method.
     * @param request servlet request
     * @param response servlet response
     * @throws ServletException if a servlet-specific error occurs
     * @throws IOException if an I/O error occurs
     */
    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        try {
            processRequest(request, response);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(Login_Check.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(Login_Check.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.