Results 1 to 8 of 8
  1. #1
    smacker is offline Member
    Join Date
    Jan 2011
    Posts
    44
    Rep Power
    0

    Default Trouble changing a specific row using Preparedstatment

    been trying to change a specific line using prepared statments

    i did :
    Java Code:
       public void loan(int cid , int bid)
        {
            PreparedStatement pst ;
            PreparedStatement pst2 ;
            int i;
            Date d=new Date();
            String date= d.toString();
                            try{
                             
            Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/mysql","","");
           //con = DriverManager.getConnection("jdbc:sqlserver://localhost;" + "user=sa;password=HerongYang");
            Statement stmt= con.createStatement();  
           ResultSet rs=stmt.executeQuery("SELECT MAX(loan_id) FROM lib.books_out ");
           rs.next();
           if(rs.getInt(1)>0)
              i=rs.getInt(1)+3;
           else
                i=1;
            stmt.executeUpdate("INSERT INTO lib.books_out ( loan_id , book_id , client_id , date1 ) VALUES ("+i+","+bid+","+cid+",'"+date+"')");
            ResultSet rs2=stmt.executeQuery("SELECT ammount FROM lib.books WHERE id = "+bid+"");
            rs2.next();
            pst=con.prepareStatement("UPDATE lib.books SET ammount = ? WHERE id = ?");
            pst.setInt(1, rs2.getInt(1));
            pst.setInt(2, bid);
            pst2=con.prepareStatement("UPDATE lib.clients SET allowed = ? WHERE id = ?");
            pst2.setInt(1, 0);
            pst2.setInt(2, cid);
            pst.executeUpdate();
            pst2.executeUpdate();      
            jLabel3.setText("** Book loan was successful");
                           }
                            catch(Exception e){
                System.out.println("Bad Connection"+e.getStackTrace());
                
            }    
        }
    it works fine with no sql errors everything works fine except
    those
    pst.executeUpdate();
    pst2.executeUpdate();
    something i'm missing ?
    thanks to all the helpers !

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

    Default Re: Trouble changing a specific row using Preparedstatment

    How do you know the INSERT is working?
    How do you know the SELECT is working?
    I see no logging in there to show that.

    If the 'SELECT ammount...' is working, then how can you tell that pst hasn't worked? You are, after all, setting the value to what it already is, so there's nothing there to show it's changed.

    Ditto with the lib.clients change. Is it actually a change?
    Please do not ask for code as refusal often offends.

  3. #3
    smacker is offline Member
    Join Date
    Jan 2011
    Posts
    44
    Rep Power
    0

    Default Re: Trouble changing a specific row using Preparedstatment

    yea i look manualy in mysql to see changes , the INSERT makes a new row but the specific values that i'm tring to alter with the prepared statments won't change

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

    Default Re: Trouble changing a specific row using Preparedstatment

    So how do you know they haven't run?
    If nothing has changed?
    Please do not ask for code as refusal often offends.

  5. #5
    smacker is offline Member
    Join Date
    Jan 2011
    Posts
    44
    Rep Power
    0

    Default Re: Trouble changing a specific row using Preparedstatment

    they ran ,
    there was no exception error but they didn't change anything in the table like they should...

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

    Default Re: Trouble changing a specific row using Preparedstatment

    Did you commit?

    But in any case:
    SELECT ammount FROM lib.books WHERE id = <bid>
    UPDATE lib.books SET ammount = <amount from above> WHERE id = <bid>;
    The first one is your select.
    The second your update.
    The first selects the amount from the table.
    The second sets that amount to the value it's just selected.
    How can you tell whether it has "worked"?
    No value changes.

    Can't tell if the second update has the same problem.
    Please do not ask for code as refusal often offends.

  7. #7
    smacker is offline Member
    Join Date
    Jan 2011
    Posts
    44
    Rep Power
    0

    Default Re: Trouble changing a specific row using Preparedstatment

    hehe yea i found it
    it should have been :
    pst.setInt(1, rs2.getInt(1)+1);
    that's what it was missing
    the second update worked just fine
    tnx for all your help man

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

    Default Re: Trouble changing a specific row using Preparedstatment

    No problem.
    We all get code blindness at times.
    Please do not ask for code as refusal often offends.

Similar Threads

  1. How to allow specific letters only?
    By ctmarco3 in forum New To Java
    Replies: 7
    Last Post: 04-01-2011, 04:41 AM
  2. How to print PreparedStatment object in console
    By sasi.tati in forum AWT / Swing
    Replies: 1
    Last Post: 08-21-2010, 06:16 AM
  3. Replies: 0
    Last Post: 08-14-2010, 10:09 AM
  4. Replies: 0
    Last Post: 01-27-2010, 03:52 PM
  5. Replies: 2
    Last Post: 11-08-2008, 10:40 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
  •