Delete Records from MS Access Database
posted 10 years ago
HI frnds,
I m new to servlets and JSP. I have learnt to connect to a database using the JDBC-ODBC driver. I can display records from a database but dont know how to delete them. Can anyone please tell me how to??...i m doing a getParameter in the servlet to get a number and store it in "id" variable and i want to use this "id" in a query to delete a row from a table. Here is my code. Please note that the deletion process works if i hardcode a number in the query but i want to use this variable.
package com.example.web;
//import com.example.model.*;
import java.sql.*;
import javax.servlet.*;
import javax.servlet.http.*;
import java.io.*;
import java.util.*;
public class DeleteOrder extends HttpServlet{
public void doPost(HttpServletRequest req, HttpServletResponse res)
throws ServletException, IOException {
res.setContentType("text/html");
PrintWriter out = res.getWriter();
out.print("<html><head>");
out.print("</head><body>");
out.print("<code><pre>");
out.print("<font color=green> ");
out.println("Order No\tmm\tdd\tyy\tItem Details\tConsignee\tDestination\tItem Quantity(in tonnes)</font>");
// debugging info
//long time1 = System.currentTimeMillis();
// connecting to database
int id;
try {
id = Integer.parseInt(req.getParameter("order").trim());
} catch (NumberFormatException e) {
throw new ServletException(e);
}
//boolean proceed=true;
Connection con = null;
Statement stmt = null;
ResultSet rs = null;
PreparedStatement ps=null;
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
con = DriverManager.getConnection("jdbc
dbc 
b1");
String sql="DELETE from OrderTable where OrderNo=id";
ps = con.prepareStatement(sql);
stmt = con.createStatement();
ps.executeUpdate();
//rs = stmt.executeQuery("DELETE FROM OrderTable where OrderNo=id");
rs = stmt.executeQuery("SELECT * from OrderTable");
// displaying records
while(rs.next()) {
out.print(rs.getObject(1).toString());
out.print("\t");
out.print(rs.getObject(2).toString());
out.print("\t");
out.print(rs.getObject(3).toString());
out.print("\t");
out.print(rs.getObject(4).toString());
out.print("\t");
out.print(rs.getObject(5).toString());
out.print("\t");
out.print(rs.getObject(6).toString());
out.print("\t");
out.print(rs.getObject(7).toString());
out.print("\t");
out.print(rs.getObject(8).toString());
out.print("\n");
}
} catch (SQLException e) {
throw new
ServletException("Servlet Could not display records.", e);
} catch (ClassNotFoundException e) {
throw new
ServletException("JDBC Driver not found.", e);
} finally {
try {
if(rs != null) {
rs.close();
rs = null;
}
if(stmt != null) {
stmt.close();
stmt = null;
}
if(con != null) {
con.close();
con = null;
}
} catch (SQLException e) {}
}
// debugging info
out.println("<BR><BR><BR><BR><BR><BR><BR><BR><BR>");
//out.print("<code><pre>");
out.print("<FORM METHOD=\"post\" ACTION=\"delete.do\">");
out.print("<INPUT TYPE=\"INPUT\" VALUE=\"enter order NO.\"NAME=\"order\">");
out.print("<BR><BR>");
out.print("<INPUT TYPE=\"SUBMIT\" NAME=\"DELETE\" VALUE=\"DELTABLE\">");
out.print("</FORM>");
out.print("</body></html>");
out.close();
}
}
I m new to servlets and JSP. I have learnt to connect to a database using the JDBC-ODBC driver. I can display records from a database but dont know how to delete them. Can anyone please tell me how to??...i m doing a getParameter in the servlet to get a number and store it in "id" variable and i want to use this "id" in a query to delete a row from a table. Here is my code. Please note that the deletion process works if i hardcode a number in the query but i want to use this variable.
package com.example.web;
//import com.example.model.*;
import java.sql.*;
import javax.servlet.*;
import javax.servlet.http.*;
import java.io.*;
import java.util.*;
public class DeleteOrder extends HttpServlet{
public void doPost(HttpServletRequest req, HttpServletResponse res)
throws ServletException, IOException {
res.setContentType("text/html");
PrintWriter out = res.getWriter();
out.print("<html><head>");
out.print("</head><body>");
out.print("<code><pre>");
out.print("<font color=green> ");
out.println("Order No\tmm\tdd\tyy\tItem Details\tConsignee\tDestination\tItem Quantity(in tonnes)</font>");
// debugging info
//long time1 = System.currentTimeMillis();
// connecting to database
int id;
try {
id = Integer.parseInt(req.getParameter("order").trim());
} catch (NumberFormatException e) {
throw new ServletException(e);
}
//boolean proceed=true;
Connection con = null;
Statement stmt = null;
ResultSet rs = null;
PreparedStatement ps=null;
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
con = DriverManager.getConnection("jdbc
dbc b1");String sql="DELETE from OrderTable where OrderNo=id";
ps = con.prepareStatement(sql);
stmt = con.createStatement();
ps.executeUpdate();
//rs = stmt.executeQuery("DELETE FROM OrderTable where OrderNo=id");
rs = stmt.executeQuery("SELECT * from OrderTable");
// displaying records
while(rs.next()) {
out.print(rs.getObject(1).toString());
out.print("\t");
out.print(rs.getObject(2).toString());
out.print("\t");
out.print(rs.getObject(3).toString());
out.print("\t");
out.print(rs.getObject(4).toString());
out.print("\t");
out.print(rs.getObject(5).toString());
out.print("\t");
out.print(rs.getObject(6).toString());
out.print("\t");
out.print(rs.getObject(7).toString());
out.print("\t");
out.print(rs.getObject(8).toString());
out.print("\n");
}
} catch (SQLException e) {
throw new
ServletException("Servlet Could not display records.", e);
} catch (ClassNotFoundException e) {
throw new
ServletException("JDBC Driver not found.", e);
} finally {
try {
if(rs != null) {
rs.close();
rs = null;
}
if(stmt != null) {
stmt.close();
stmt = null;
}
if(con != null) {
con.close();
con = null;
}
} catch (SQLException e) {}
}
// debugging info
out.println("<BR><BR><BR><BR><BR><BR><BR><BR><BR>");
//out.print("<code><pre>");
out.print("<FORM METHOD=\"post\" ACTION=\"delete.do\">");
out.print("<INPUT TYPE=\"INPUT\" VALUE=\"enter order NO.\"NAME=\"order\">");
out.print("<BR><BR>");
out.print("<INPUT TYPE=\"SUBMIT\" NAME=\"DELETE\" VALUE=\"DELTABLE\">");
out.print("</FORM>");
out.print("</body></html>");
out.close();
}
}
posted 10 years ago
hi,
change:
String sql="DELETE from OrderTable where OrderNo=id";
ps = con.prepareStatement(sql);
stmt = con.createStatement();
into:
String sql="DELETE from OrderTable where OrderNo=?";
ps = con.prepareStatement(sql);
ps.setInt(1, id);
Herman
change:
String sql="DELETE from OrderTable where OrderNo=id";
ps = con.prepareStatement(sql);
stmt = con.createStatement();
into:
String sql="DELETE from OrderTable where OrderNo=?";
ps = con.prepareStatement(sql);
ps.setInt(1, id);
Herman
Abhin Balur
Greenhorn
Posts: 21
posted 10 years ago
Hi,
Thank you for your reply.... it worked!!
;) :p ...
Thank you for your reply.... it worked!!
;) :p ...
posted 10 years ago
Note that there is a bug in the JDBC-ODBC bridge where you should set autocommit to true on the connection, otherwise some insert and update operations get lost.
| Consider Paul's rocket mass heater. |