aspose file tools*
The moose likes JDBC and the fly likes Delete Records from MS Access Database Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of The Java EE 7 Tutorial Volume 1 or Volume 2 this week in the Java EE forum
or jQuery UI in Action in the JavaScript forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Delete Records from MS Access Database" Watch "Delete Records from MS Access Database" New topic
Author

Delete Records from MS Access Database

Abhin Balur
Greenhorn

Joined: Feb 18, 2008
Posts: 21
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();
}




}
Herman Schelti
Ranch Hand

Joined: Jul 17, 2006
Posts: 387
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
Abhin Balur
Greenhorn

Joined: Feb 18, 2008
Posts: 21
Hi,
Thank you for your reply.... it worked!! ;) :p ...
David O'Meara
Rancher

Joined: Mar 06, 2001
Posts: 13459

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.
 
Don't get me started about those stupid light bulbs.
 
subject: Delete Records from MS Access Database