1. We have moved from vBulletin to XenForo and you are viewing the site in the middle of the move. Though the functional aspect of everything is working fine, we are still working on other changes including the new design on Xenforo.
    Dismiss Notice

jsp coding for access connectivity

Discussion in 'JSP' started by Mirunalini, Mar 10, 2011.

  1. Mirunalini

    Mirunalini New Member

    hi everyone
    i am trying to develop a web page in jsp with access connectivity,but in my following code insertion is done successfully but still have some issues with update and delete,can anyone help me
    i am using netbeans 6.9 IDE,and tom cat 1.6 as server
    the code is
    Code:
    <%@ page language="java" import="java.sql.*"%>
            <%
                        Connection con = null;
                        Statement st = null;
                        ResultSet rs = null;
                        PreparedStatement pst = null;
                        int no = Integer.parseInt(request.getParameter("NO"));
                        String name = request.getParameter("NAME");
                        String address = request.getParameter("ADDRESS");
                        String btn = request.getParameter("btn");
                        Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
                        con = DriverManager.getConnection("jdbc:odbc:student");
                        st = con.createStatement();
                        rs = st.executeQuery("select  * from student");
                        if (btn != null) {
                            if (btn.equals("Insert")) {
                                int k = 0;
                                while (rs.next()) {
                                    if (rs.getInt(1) == no) {
                                        k = 1;
                                    }
                                }
                                if (k == 0) {
                                    pst = con.prepareStatement("insert into student values (?,?,?)");
                                    pst.setInt(1, no);
                                    pst.setString(2, name);
                                    pst.setString(3, address);
                                    int i = pst.executeUpdate();
            %>
            <%
                                            if (i > 0) {
                                                out.println("record inserted succesfully");
                                            } else {
                                                out.println("record not inserted");
                                            }%>
            <%
                                } else {
                                    out.println("record alredy exist");
                                }
                            }
                            else if (btn.equals("update")) {
                                pst = con.prepareStatement("update student set name=? ,address=? where no=?");
                                pst.setInt(3, no);
                                pst.setString(1, name);
                                pst.setString(2, address);
                                int j = pst.executeUpdate();
                        %>
                           <%     if (j > 0) {
                                    out.println("updated");
                                } else {
                                    out.println("not updated");
                                }%>
                 <%           }
                            else if (btn.equals("delete")) {
                                pst = con.prepareStatement("delete from student where no=?");
                                pst.setInt(1, no);
                                int l = pst.executeUpdate();%>
                                <%if (l > 0) {
                                    out.println("deleted");
                                } else {
                                    out.println("record not found");
                                }%>
                           <% }
                            else
                                {
                                out.println("not valid action");
                            }
                         }
                        else
                            {
                            out.println("Specify the action");
                            }
            %>
            <table border="0" bgcolor="lightgreen"  cellspacing="5" cellpadding="2">
                <tr><th>No</th><th>Name</th><th>Address</th></tr>
                <%
                            st = con.createStatement();
                            rs = st.executeQuery("select  * from student");
                            while (rs.next()) {
                %>
                <tr>
                    <td><%=rs.getInt(1)%></td>
                    <td><%=rs.getString(2)%></td>
                    <td><%=rs.getString(3)%></td>
                </tr>
                <% }
                %>
            </table>
    i cant understand
    why update and delete is not occuring when the insertion s successful,the update and delete just prints the else part even when the inputs are correct.
     
    Last edited by a moderator: Mar 10, 2011
  2. krazytechno

    krazytechno New Member

    i dont find any mistake out here.
     
  3. krazytechno

    krazytechno New Member

    please try the following code

    HTML:
     <html>
     <head>
       <title>JSP MS Access Example</title>
     </head>
     <body>
       <%@ page import="javax.naming.*" %>
       <%@ page import="java.sql.*" %>
       <%@ page import="javax.sql.*" %>
    
       <h1>JSP MS Access Example</h1>
    
       <%
    
       Connection conn = null;
       Statement stmt = null;
       ResultSet rs = null;
    
       try {
    
          
           Context initCtx = new InitialContext();
           Context envCtx = (Context) initCtx.lookup("java:comp/env");
    
          
           DataSource ds = (DataSource) envCtx.lookup("jdbc/MyDB");
    
          
           conn = ds.getConnection();
    
           
           stmt = conn.createStatement();
    
         
           rs = stmt.executeQuery("SELECT CompanyName FROM suppliers");
    
           while (rs.next()) {
               // You need to edit this column name
               String s = rs.getString("CompanyName");
               out.print(s + "<br>");
           }
    
           rs.close();
           rs = null;
           stmt.close();
           stmt = null;
           conn.close(); // Return to connection pool
           conn = null;  // Make sure we do not close it twice
       } catch (SQLException e) {
           out.print("Throw e" + e);
       } finally {
         // Always make sure result sets and statements are closed,
         // and the connection is returned to the pool
         if (rs != null) {
           try { rs.close(); } catch (SQLException e) { ; }
           rs = null;
         }
         if (stmt != null) {
           try { stmt.close(); } catch (SQLException e) { ; }
           stmt = null;
         }
         if (conn != null) {
           try { conn.close(); } catch (SQLException e) { ; }
           conn = null;
         }
       }
    
       %>
    
     </body>
    </html>
     
    Last edited by a moderator: Mar 13, 2011
  4. Mirunalini

    Mirunalini New Member

    thank u for ur reply,
    that code got executed,after i deleted the database and recreated it again and trying
     

Share This Page