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:Susidzaimah Pg Sulaiman (spgs@cis.strath.ac.uk)
Date:02/02/2004 11:40:42 PM
List:com.mysql.lists.java

It still doesn't work.

I will be moving to mySQL soon. I was told that it will only involve minor changes to get it work with mySQL. I haven't reconstruct my code because I need to make each function (such as delete, insert, update, select..) working first. Only delete function still got problem.

regards susi sulaiman

----- Original Message ----- From: "Mike Markovich" <mi@amyskitchen.net> To: "Susidzaimah Pg Sulaiman" <spgs@cis.strath.ac.uk>; <ja@lists.mysql.com> Sent: Monday, February 02, 2004 3:49 PM Subject: RE: deleting records from database using prepareStatement

Hi Susi,

The line should go after the delete statement and before the executeQuery

statement. It should look like the snippet below...Your records aren't deleted until the executeQuery statement is executed, so the first two lines are really just setting up the sql statement that will be executed. Also, I'm don't think you can delete records with the "executeQuery()" statement so that might give you problems. If it does try "executeUpdate()".

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ +++

statement = connection.prepareStatement("DELETE FROM Bibliography WHERE ID = ?"); statement.setString(1, inRequest.getParameter("bibliography")); rs = statement.executeQuery();

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ +++

I would also like to suggest that you restructure your code so that it is

clear where your deletes, selects, updates, etc. occur. Following the flow of nested "if" statements can be very difficult and I suspect the structure of your code is causing confusion also. Finally, this list is for java/mysql/connectorj topics so you might want to consider switching from Access to MySql so that your questions are more relevant to this forum in the future.

Best Wishes, Mike

-----Original Message----- From: Susidzaimah Pg Sulaiman [mailto:spgs@cis.strath.ac.uk] Sent: Saturday, January 31, 2004 5:28 PM To: Mike Markovich; ja@lists.mysql.com Subject: Re: deleting records from database using prepareStatement

Hi Mike,

do I need that line --- statement.setString(1, inRequest.getParameter("bibliography")); --- after the delete command? just like the update command?

----- Original Message ----- From: "Mike Markovich" <mi@amyskitchen.net> To: "Susidzaimah Pg Sulaiman" <spgs@cis.strath.ac.uk>; <ja@lists.mysql.com> Sent: Friday, January 30, 2004 3:13 PM Subject: RE: deleting records from database using prepareStatement

Hi Susi,

It looks like you are not setting the ID field. You need a line similar

to the one you used for your select statement for you delete statement. Something like,

statement.setString(1, inRequest.getParameter("bibliography"));

Regards, Mike

-----Original Message----- From: Susidzaimah Pg Sulaiman [mailto:spgs@cis.strath.ac.uk] Sent: Friday, January 30, 2004 12: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); } }

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

------

-- MySQL Java Mailing List For list archives: http://lists.mysql.com/java To unsubscribe: http://lists.mysql.com/java?unsub=spgs@cis.strath.ac.uk