Win a copy of Spring in Action (5th edition) this week in the Spring forum!
  • 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 all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Bear Bibeault
  • Devaka Cooray
  • Liutauras Vilda
  • Jeanne Boyarsky
Sheriffs:
  • Knute Snortum
  • Junilu Lacar
  • paul wheaton
Saloon Keepers:
  • Ganesh Patekar
  • Frits Walraven
  • Tim Moores
  • Ron McLeod
  • Carey Brown
Bartenders:
  • Stephan van Hulst
  • salvin francis
  • Tim Holloway

Delete Records from MS Access Database  RSS feed

 
Greenhorn
Posts: 21
  • Mark post as helpful
  • send pies
  • 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
  • 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
  • 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
  • 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.
 
Don't get me started about those stupid light bulbs.
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!