jsp coding for access connectivity

Mirunalini's Avatar
Light Poster
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 shabbir; 10Mar2011 at 14:45.. Reason: Code blocks
0
krazytechno's Avatar, Join Date: Mar 2010
Go4Expert Member
i dont find any mistake out here.
0
krazytechno's Avatar, Join Date: Mar 2010
Go4Expert Member
please try the following code

HTML Code:
 <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 shabbir; 13Mar2011 at 09:13.. Reason: Code blocks
0
Mirunalini's Avatar
Light Poster
thank u for ur reply,
that code got executed,after i deleted the database and recreated it again and trying