• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Tim Cooke
  • paul wheaton
  • Paul Clapham
  • Ron McLeod
Sheriffs:
  • Jeanne Boyarsky
  • Liutauras Vilda
Saloon Keepers:
  • Tim Holloway
  • Carey Brown
  • Roland Mueller
  • Piet Souris
Bartenders:

Delete Records from MS Access Database

 
Greenhorn
Posts: 21
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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();
}




}
 
Ranch Hand
Posts: 387
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 21
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi,
Thank you for your reply.... it worked!! ;) :p ...
 
Rancher
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
reply
    Bookmark Topic Watch Topic
  • New Topic