Results 1 to 19 of 19
  1. #1
    javaguy345 is offline Member
    Join Date
    May 2011
    Posts
    15
    Rep Power
    0

    Default Problem with deleting record.

    Hi guys I'm having trouble deleting a record from my database, I'm able to insert a record but can't delete any. This is my code for my delete button. Basically there supposed to put the number of the question into the text field and click the delete button then it takes the number the user has inputted and uses it in a query to delete it. I'm not 100% sure if this is how you do it so any help would be great.

    String selquestion1 = selquesttxt.getText();
    int selquestion = Integer.parseInt(selquestion1);

    try
    {


    Connection con = DriverManager.getConnection(databaseURL, "user", "password");
    Statement stmt = con.createStatement();


    String sql = "DELETE * FROM APP.QUESTIONS WHERE QUESTIONS.QUESTIONNO = " + selquestion;
    stmt.executeUpdate(sql);


    JOptionPane.showMessageDialog(null, "Question number " + selquestion + "has been deleted from the database.");
    }catch(SQLException e)
    {
    JOptionPane.showMessageDialog(null,e);
    }

    // DELETE QUESTION BUTTON
    }

  2. #2
    Junky's Avatar
    Junky is offline Grand Poobah
    Join Date
    Jan 2011
    Location
    Dystopia
    Posts
    3,755
    Rep Power
    7

    Default

    Have you run your SQL directly on the database to make sure it is correct?

    Also, you should use a PreparedStatement instead.

  3. #3
    Junky's Avatar
    Junky is offline Grand Poobah
    Join Date
    Jan 2011
    Location
    Dystopia
    Posts
    3,755
    Rep Power
    7

    Default

    Quote Originally Posted by javaguy345 View Post
    but can't delete any.
    So what is happening instead?
    Which JOptionPane message is being displayed?
    Is neither being displayed?

  4. #4
    javaguy345 is offline Member
    Join Date
    May 2011
    Posts
    15
    Rep Power
    0

    Default

    Error code -1, SQL state 42X01: Syntax error: Encountered "*" at line 1, column 8. This is the sql error I'm getting, I think it has something to do with the *.

  5. #5
    Junky's Avatar
    Junky is offline Grand Poobah
    Join Date
    Jan 2011
    Location
    Dystopia
    Posts
    3,755
    Rep Power
    7

    Default

    The * is not valid sql in that delete statement.

  6. #6
    javaguy345 is offline Member
    Join Date
    May 2011
    Posts
    15
    Rep Power
    0

    Default

    I'm passing an Integer but it still says I'm passing a CHAR

  7. #7
    hitesh is offline Member
    Join Date
    Jul 2010
    Posts
    28
    Rep Power
    0

    Default

    Try this

    String sql = "DELETE FROM APP.QUESTIONS WHERE QUESTIONS.QUESTIONNO = '"+selquestion+"'";

  8. #8
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,755
    Rep Power
    19

    Default

    Use a PreparedStatement, not a Statement, then use the correct setXXXX() method for the data type that column QUESTIONNO has.
    Then, if you still have errors, post the error and full stack trace here.
    Don't try and interpret the error...copy and paste it.

  9. #9
    mine0926 is offline Senior Member
    Join Date
    Apr 2010
    Location
    Philippines
    Posts
    580
    Rep Power
    5

    Default

    it is more appropriate to use PreparedStatement than concatenated strings.
    Here is a tutorial from java about PreparedStatement Using Prepared Statements (The Java™ Tutorials > JDBC(TM) Database Access > JDBC Basics)
    And here is a example of using it PreparedStatement delete,JDBC Delete,Deleting Records using the Prepared Statement

  10. #10
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,755
    Rep Power
    19

    Default

    Do you think after three of us mentions PSs that it might sink in?
    :)

    ETA: Though I will say that generally Rose India is a poor choice of site for examples.
    In the one you posted the connection is not closed in a finally block, for example. Something that is a frequent cause of bugs.
    Last edited by Tolls; 05-20-2011 at 10:14 AM.

  11. #11
    mine0926 is offline Senior Member
    Join Date
    Apr 2010
    Location
    Philippines
    Posts
    580
    Rep Power
    5

    Default

    @Tolls - Did not see your reply, probably my browser.
    @OP - use code tags when posting codes.

  12. #12
    javaguy345 is offline Member
    Join Date
    May 2011
    Posts
    15
    Rep Power
    0

    Default

    What is the advantage of using a prepared statement I got it working without it so what would be the reason.

  13. #13
    JosAH's Avatar
    JosAH is offline Moderator
    Join Date
    Sep 2008
    Location
    Voorschoten, the Netherlands
    Posts
    13,309
    Blog Entries
    7
    Rep Power
    20

    Default

    Quote Originally Posted by hitesh View Post
    Try this

    String sql = "DELETE FROM APP.QUESTIONS WHERE QUESTIONS.QUESTIONNO = '"+selquestion+"'";
    Why?

    kind regards,

    Jos
    cenosillicaphobia: the fear for an empty beer glass

  14. #14
    JosAH's Avatar
    JosAH is offline Moderator
    Join Date
    Sep 2008
    Location
    Voorschoten, the Netherlands
    Posts
    13,309
    Blog Entries
    7
    Rep Power
    20

    Default

    Quote Originally Posted by javaguy345 View Post
    What is the advantage of using a prepared statement I got it working without it so what would be the reason.
    That's why.

    kind regards,

    Jos
    cenosillicaphobia: the fear for an empty beer glass

  15. #15
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,755
    Rep Power
    19

    Default

    Getting it to work and getting it to work properly are not the same thing.
    Anytime you are applying variables (with a few minor exceptions) to a query you should use a PreparedStatement.
    Jos has given one reason (SQL injection), but there are others. Like less overhead on the db server for starters.
    Oh, and not having to faff with escaping in Strings.

  16. #16
    B.vijayanand is offline Member
    Join Date
    May 2011
    Posts
    2
    Rep Power
    0

    Default

    hi guys
    try this code and useful to you...
    and check your database url name

    String sql = "DELETE * FROM APP.QUESTIONS WHERE QUESTIONS.QUESTIONNO = " + selquestion+"";
    PreparedStatement ps=con.PrepareStatement(query);
    ps.executeUpdate();


    JOptionPane.showMessageDialog(null, "Question number " + selquestion + "has been deleted from the database.");
    }catch(SQLException e)
    {
    JOptionPane.showMessageDialog(null,e);
    }

  17. #17
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,755
    Rep Power
    19

    Default

    Oh right.
    So you prepare a statement but don't actually use the PS the way it should be.
    Indeed, all you've done is change Statement to PreparedStatement...pointless.

    If you don't know how to use these things then don't you think it might be an idea not to respond?

  18. #18
    JosAH's Avatar
    JosAH is offline Moderator
    Join Date
    Sep 2008
    Location
    Voorschoten, the Netherlands
    Posts
    13,309
    Blog Entries
    7
    Rep Power
    20

    Default

    Quote Originally Posted by B.vijayanand View Post
    hi guys
    try this code and useful to you...
    As an example of absolute crap it is. Why do people who don't know zilch about the subject open their mouth and spread all this rubbish around?

    Jos
    cenosillicaphobia: the fear for an empty beer glass

  19. #19
    mine0926 is offline Senior Member
    Join Date
    Apr 2010
    Location
    Philippines
    Posts
    580
    Rep Power
    5

    Default

    Quote Originally Posted by javaguy345 View Post
    What is the advantage of using a prepared statement I got it working without it so what would be the reason.
    I am not good in english so I may not explain it well to you. Here are some links that explain usage of preparedStatements
    Easy String Concatenation Considered Harmful? | Java.net
    MySQL :: Prepared Statements

Similar Threads

  1. String problem: deleting
    By BlueBird in forum New To Java
    Replies: 3
    Last Post: 03-18-2011, 05:21 AM
  2. problem with inserting new record
    By gerard kowara in forum JDBC
    Replies: 1
    Last Post: 10-26-2010, 11:33 AM
  3. Vector/Table Deleting Row Problem.
    By ocean in forum New To Java
    Replies: 8
    Last Post: 12-09-2009, 06:39 PM
  4. Some problem in fetching record
    By Shivraj in forum NetBeans
    Replies: 1
    Last Post: 03-27-2009, 11:19 AM
  5. check record dependency before deleting
    By dennisome in forum JDBC
    Replies: 9
    Last Post: 11-07-2008, 11:57 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •