1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

jsp coding for access connectivity

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

  1. Mirunalini

    Mirunalini New Member

    Joined:
    Mar 9, 2011
    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    0
    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

    Joined:
    Mar 7, 2010
    Messages:
    38
    Likes Received:
    1
    Trophy Points:
    0
    Occupation:
    knowing the computer world as deep as possible
    Location:
    kolkata,west bengal
    i dont find any mistake out here.
     
  3. krazytechno

    krazytechno New Member

    Joined:
    Mar 7, 2010
    Messages:
    38
    Likes Received:
    1
    Trophy Points:
    0
    Occupation:
    knowing the computer world as deep as possible
    Location:
    kolkata,west bengal
    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

    Joined:
    Mar 9, 2011
    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    0
    thank u for ur reply,
    that code got executed,after i deleted the database and recreated it again and trying
     

Share This Page