18 messages in com.mysql.lists.javaRE: deleting records from database us...
FromSent OnAttachments
Mofeed Shahin11 Dec 2003 22:44 
Jeff Newmiller12 Dec 2003 10:20 
Chris McGowan12 Dec 2003 10:38 
Mark Matthews12 Dec 2003 10:55 
Chris McGowan12 Dec 2003 11:52 
Mofeed Shahin14 Dec 2003 15:08 
Christian Mack17 Dec 2003 10:02 
Mofeed Shahin17 Dec 2003 14:31 
Susidzaimah Pg Sulaiman30 Jan 2004 00:38 
Scott Queen30 Jan 2004 06:43 
Mike Markovich30 Jan 2004 07:12 
Susidzaimah Pg Sulaiman31 Jan 2004 17:26 
Susidzaimah Pg Sulaiman31 Jan 2004 17:27 
Scott Queen31 Jan 2004 19:07 
Keith Hatton02 Feb 2004 01:26 
Mike Markovich02 Feb 2004 07:49 
Susidzaimah Pg Sulaiman02 Feb 2004 23:40 
Susidzaimah Pg Sulaiman03 Feb 2004 19:01 
Subject:RE: deleting records from database using prepareStatement
From:Scott Queen (sco@dokimosllc.com)
Date:01/30/2004 06:43:24 AM
List:com.mysql.lists.java

Susi, You are not setting the Java String ID equal to any value.... so when your code executes statement.setString(1, ID); there is no value for the where clause of your delete statement. Did you mean to delete the record you just looked up? If so, then it looks like you want to add:

ID = rs.getString("ID");

before

connection.prepareStatement("DELETE FROM Bibliography WHERE ID = ?"); statement.setString(1, ID);

Scott

-----Original Message----- From: Susidzaimah Pg Sulaiman [mailto:spgs@cis.strath.ac.uk] Sent: Friday, January 30, 2004 1:39 AM To: ja@lists.mysql.com Subject: deleting records from database using prepareStatement

Hi,

I'm new to this Java servlet + SQL thingy. I have problem in deleting the records that I stored in Microsoft Access database. No record has been deleted after I run my servlet below.

The output of my code is as the following: SQLException: [Microsoft][ODBC Microsoft Access Driver]COUNT field incorrect

but if I include the statement "statement.setString(1, ID);" after the statement "statement = connection.prepareStatement("DELETE FROM Bibliography WHERE ID = ?");" it also doesnt work. No record has been deleted.

Can someone please assist.

Thank you

Regards susi sulaiman

my source code is as follows:

------------------------------------------------------------------------

--------

import java.io.*; import java.sql.*; import javax.servlet.*; import javax.servlet.http.*;

public class DeleteBib extends HttpServlet { public void doGet(HttpServletRequest inRequest, HttpServletResponse outResponse) throws ServletException, IOException {

PrintWriter out = null; Connection connection = null; PreparedStatement statement = null; ResultSet rs, rs2;

//String ID = "";

try { outResponse.setContentType("text/html"); out = outResponse.getWriter();

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); connection = DriverManager.getConnection("jdbc:odbc:Bib", "anonymous", "guest");

if (connection != null) { if (inRequest.getParameter("submit").equals("submit")) { statement = connection.prepareStatement("SELECT * FROM Bibliography WHERE ID = ?"); if (statement != null) { statement.setString(1, inRequest.getParameter("bibliography")); rs = statement.executeQuery();

if (!rs.next()) { out.println("<HTML>No bibliography found # " + inRequest.getParameter("bibliography") + "</HTML>"); } else { out.println("<HTML><HEAD><TITLE>Bibliography Identification Record</TITLE></HEAD>"); out.println("<BODY>"); out.println("Bibliography Information:<BR>"); out.println("<table>"); out.println("<form method='DeleteBibliography' method='post'>"); out.println("<tr><td>"); out.println("ID: <input name='ID' value='" + rs.getString("ID") + "'><BR>"); out.println("Type: <input name='type' value='" + rs.getString("type") + "'><BR>"); out.println("Author: <input name='author' value='" + rs.getString("author") + "'><BR>"); out.println("Title: <input name='title' value='" + rs.getString("title") + "'><BR>"); out.println("Year: <input name='year' value='" + rs.getString("year") + "'><BR>"); out.println("<input type='submit' value='delete' name='submit'>"); out.println("</form>"); out.println("</td>"); out.println("<tr>"); out.println("</table>"); out.println("</BODY></HTML>"); } } else { out.println("<HTML>Statement is NULL</HTML>"); } } else { //do delete statement = connection.prepareStatement("DELETE FROM Bibliography WHERE ID = ?"); //statement.setString(1, ID); rs = statement.executeQuery();

out.println("<HTML>"); out.println("Record has been deleted " + "<BR>"); out.println("</HTML>"); } } else { out.println("<HTML>Connection is NULL</HTML>"); } } catch(ClassNotFoundException e){ out.println("Driver Error"); } catch(SQLException e) { out.println("<HTML>"); out.println("SQLException: " + e.getMessage()); out.println("</HTML>"); } catch(Exception e) { e.printStackTrace(); } }

public void doPost(HttpServletRequest inRequest, HttpServletResponse outResponse) throws ServletException, IOException { doGet(inRequest, outResponse); } }

------------------------------------------------------------------------

--------