aspose file tools
The moose likes JDBC and Relational Databases 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 Badass: Making Users Awesome this week in the Game Development forum!
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
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.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Delete Records from MS Access Database