Update statement gone HeWire

[_-Blackhawk-_]

public void setSettings(int gebruikersNr, String wachtwoord, boolean notRunning)
   {
          Statement stmt = con.createStatement( );
          stmt.executeUpdate 
                  ("UPDATE Werknemer "
                  + "SET password = '" + wachtwoord + "' "
                  + "nietLopend = '" + notRunning + "'"
                  + "WHERE userID = '" + gebruikersNr + "'");
       stmt.close();      
   }

I'm trying to update the database but whatever I change there is alway's 1 or more lines red underlined because of an error. In this version all lines containing stmt give the following error:
Unreported exception java.sql.SQLException; must be caught of
explained to be thrown.

It's probably a + or " in the wrong place but I tryed a LOT of them and couldn't find where I was wrong.

Any help would be appreciated.

[Tolls]

Pretty much everyone of those methods inside your setSettings method can throw a SQLException, so you need to handle that.

Statement stmt = null;
try {
.. your code here (except the close)
} catch (SQLException ex) {
    ex.printStatckTrace();
} finally {
    try {
        if (stmt != null) {
            stmt.close();
        }
    } catch (...etc) {
    }
}

That's the general structure.

You ought to be using a PreparedStatement as well, since concatenating your SQL together is usually a Bad Thing (see SQL Injection, or string escaping).

[_-Blackhawk-_]

   public void setSettings(int gebruikersNr, String wachtwoord, boolean notRunning)
   {
       Connect();
       System.out.println("700");
       try
       {
           System.out.println("701");
           PreparedStatement stmt = con.prepareStatement
                  ( "UPDATE Werknemer "
                  + "SET password = ?,nietLopend = ?"
                  + "WHERE userID = ?" );
           System.out.println("702");
           int[] rows;
                for(int i = 0; i < 35;i++)
                {
                    stmt.setString(1, wachtwoord);
                    stmt.setBoolean(2, notRunning);
                    stmt.setInt(3, gebruikersNr);
                    stmt.addBatch();
                }
                System.out.println("703");
                rows = stmt.executeBatch();
       }
       catch(SQLException ex)
       {
           ex.printStackTrace();
       }
       finally
       {
           try
           {
               if(stmt != null)
               {
                   stmt.close();
               }
           }
           catch(SQLException ex)
           {
               ex.printStackTrace();
           }
       }
   }

700
701
java.sql.SQLException: No operations allowed after connection closed.

Connection was closed explicitly by the application at the following location:

** BEGIN NESTED EXCEPTION ** 

java.lang.Throwable

STACKTRACE:

java.lang.Throwable
	at com.mysql.jdbc.Connection.close(Connection.java:1125)
	at urenreg107.DBConnect.Close(DBConnect.java:174)
	at urenreg107.DBConnect.getSettings(DBConnect.java:218)
	at urenreg107.UrenReg107View.SettingsUButtonButtonActionPerformed(UrenReg107View.java:714)
	at urenreg107.UrenReg107View.access$1400(UrenReg107View.java:23)
	at urenreg107.UrenReg107View$10.actionPerformed(UrenReg107View.java:333)
	at javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:2018)
	at javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.java:2341)
	at javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:402)
	at javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:259)
	at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(BasicButtonListener.java:252)
	at java.awt.Component.processMouseEvent(Component.java:6504)
	at javax.swing.JComponent.processMouseEvent(JComponent.java:3321)
	at java.awt.Component.processEvent(Component.java:6269)
	at java.awt.Container.processEvent(Container.java:2229)
	at java.awt.Component.dispatchEventImpl(Component.java:4860)
	at java.awt.Container.dispatchEventImpl(Container.java:2287)
	at java.awt.Component.dispatchEvent(Component.java:4686)
	at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4832)
	at java.awt.LightweightDispatcher.processMouseEvent(Container.java:4492)
	at java.awt.LightweightDispatcher.dispatchEvent(Container.java:4422)
	at java.awt.Container.dispatchEventImpl(Container.java:2273)
	at java.awt.Window.dispatchEventImpl(Window.java:2713)
	at java.awt.Component.dispatchEvent(Component.java:4686)
	at java.awt.EventQueue.dispatchEventImpl(EventQueue.java:707)
	at java.awt.EventQueue.access$000(EventQueue.java:101)
	at java.awt.EventQueue$3.run(EventQueue.java:666)
	at java.awt.EventQueue$3.run(EventQueue.java:664)
	at java.security.AccessController.doPrivileged(Native Method)
	at java.security.ProtectionDomain$1.doIntersectionPrivilege(ProtectionDomain.java:76)
	at java.security.ProtectionDomain$1.doIntersectionPrivilege(ProtectionDomain.java:87)
	at java.awt.EventQueue$4.run(EventQueue.java:680)
	at java.awt.EventQueue$4.run(EventQueue.java:678)
	at java.security.AccessController.doPrivileged(Native Method)
	at java.security.ProtectionDomain$1.doIntersectionPrivilege(ProtectionDomain.java:76)
	at java.awt.EventQueue.dispatchEvent(EventQueue.java:677)
	at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:211)
	at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:128)
	at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:117)
	at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:113)
	at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:105)
	at java.awt.EventDispatchThread.run(EventDispatchThread.java:90)


** END NESTED EXCEPTION **

I have changed it like you advise and now the program wil run again but when I try to use this part i get the above mentiond Exceptions.
Now I checked the net and some NESTED EXCEPTIONS are because I left the server on for to long but mine didn't work after a restart.

as you can see in the system.out the code reaches PrepareStatement but something about if is wrong because it won't print 702.

If you have a WORKING COMPLETE EXAMPLE where I have to change some stuff to match my own version that is fine but al the examples I fine just show like a couple of lines.

[Tolls]

at urenreg107.DBConnect.Close(DBConnect.java:174)
at urenreg107.DBConnect.getSettings(DBConnect.java:21 8)
at urenreg107.UrenReg107View.SettingsUButtonButtonAct ionPerformed(UrenReg107View.java:714)
at urenreg107.UrenReg107View.access$1400(UrenReg107Vi ew.java:23)
at urenreg107.UrenReg107View$10.actionPerformed(UrenR eg107View.java:333)

In that code there you are closing the connection.
You are then attempting to use that connection, hence the exception.

Not knowing what you are trying to do I cannot say if closing the connection was correct, or whether that is the mistake and you should be getting a new connection each time.

[_-Blackhawk-_]

public void setSettings(int gebruikersNr, String wachtwoord, boolean notRunning)
   {
       Connect();
       System.out.println("700");
       try
       {
           con = DriverManager.getConnection(url, "borg", ""); 
           PreparedStatement stmt = con.prepareStatement
                  ( "UPDATE Werknemer "
                  + "SET password = ? ,nietLopend = ? "
                  + "WHERE userID = ?" );
           System.out.println("702");
           int[] rows;
                for(int i = 0; i < 35;i++)
                {
                    stmt.setString(1, wachtwoord);
                    stmt.setBoolean(2, notRunning);
                    stmt.setInt(3, gebruikersNr);
                    stmt.addBatch();
                }
                System.out.println("703");
                rows = stmt.executeBatch();
       }
       catch( SQLException e )
       {
           e.printStackTrace();
       }
       finally
       {
           try
           {
               if(stmt != null)
               {
                   stmt.close();
               }
           }
           catch(SQLException e)
           {
               e.printStackTrace();
           }
       }
   }

problem solved code above is working with what I want.

main problems: con = DriverManager.getConnection(url, "borg", ""); was missing.
and: changed it to a prepareStatement.

THX for the help again Tolls

[Tolls]

Are you ensuring you close any open connections?
That code (as it stands) will likely result in more and more open connections, until the db finally decides not to allow anymore.

[_-Blackhawk-_]

Would running Close();

public void Close()
    {
              if( con != null ) 
              { 
                  try 
                  { 
                      con.close( ); 
                  } 
                  catch( Exception e ) 
                  { 
                      e.printStackTrace( ); 
                  } 
              } 
         
    }

be sufficiant at the end of every Statement? or should I add something?

[Tolls]

As I said, I don't know your setup.
I don't know what it is you;re writing.
Some systems work best with a single connection you maintain, some with a connection pool, some with opening a connection as needed...

[_-Blackhawk-_]

This problem is Solved!!