Results 1 to 14 of 14
  1. #1
    Johanis is offline Senior Member
    Join Date
    Oct 2010
    Posts
    139
    Rep Power
    0

    Default INSERT SQL statement problem

    Hey. I Am using a one to many relationship in my database. My insert statement doesn;t want to work though. Please help. Here is the code:
    Java Code:
     private String naam1;
          private String naam2;
          private String date;
          private String dbirth;
          private String pbirth;
          private String member;
          private String rank;
          private String mot;
       
           public SQL (String nm1, String nm2, String dte, String dob, String pob, String mem, String rnk, String mt)throws SQLException
          {
             naam1 = nm1;
             naam2 = nm2;
             date = dte;
             dbirth = dob;
             pbirth = pob;
             member = mem;
             rank = rnk;
             mot = mt;
             updateMthd("INSERT INTO Table1 VALUES ('"+naam1+"','"+naam2+"','"+date+"','"+dbirth+"','"+pbirth+"','"+member+"','"+rank+"','"+mot+"')");
             displayMthd("","SELECT * FROM Table1");
          }

  2. #2
    doWhile is offline Moderator
    Join Date
    Jul 2010
    Location
    California
    Posts
    1,641
    Rep Power
    7

    Default Re: INSERT SQL statement problem

    My insert statement doesn;t want to work though
    The error - which you have not provided - should allude to an answer. And FWIW, use a PreparedStatement

  3. #3
    Johanis is offline Senior Member
    Join Date
    Oct 2010
    Posts
    139
    Rep Power
    0

    Default Re: INSERT SQL statement problem

    Ok thank you. What is a prepared statement?? I got it working. But what I found is that it only inserts the information into the database if all the fields are ar blank: If there is no information in the database. Here is what my code looks like now:
    Java Code:
    private String naam1;
          private String naam2;
          private String date;
          private String dbirth;
          private String pbirth;
          private String member;
          private String rank;
          private String mot;
          private String gender;
       
           public SQL (String nm1, String nm2, String dte, String dob, String pob, String mem, String rnk, String mt, String gen)throws SQLException
          {
             naam1 = nm1;
             naam2 = nm2;
             date = dte;
             dbirth = dob;
             pbirth = pob;
             member = mem;
             rank = rnk;
             mot = mt;
             gender = gen;
             updateMthd("INSERT INTO Table1 VALUES('"+naam1+"','"+naam2+"','"+date+"')");
             updateMthd("INSERT INTO Table2 VALUES ('"+mot+"','"+dbirth+"','"+pbirth+"','"+member+"','"+rank+"','"+naam1+"','"+gender+"')");
          }
    and here is the errors:
    Java Code:
    Connected to database
    Exception in thread "AWT-EventQueue-0" java.lang.RuntimeException: java.sql.SQLException: General error
    	at Userinfo.button1MouseClicked(Userinfo.java:45)
    	at Userinfo.access$100(Userinfo.java:12)
    	at Userinfo$2.mouseClicked(Userinfo.java:213)
    	at java.awt.AWTEventMulticaster.mouseClicked(AWTEventMulticaster.java:270)
    	at java.awt.Component.processMouseEvent(Component.java:6437)
    	at javax.swing.JComponent.processMouseEvent(JComponent.java:3321)
    	at java.awt.Component.processEvent(Component.java:6199)
    	at java.awt.Container.processEvent(Container.java:2203)
    	at java.awt.Component.dispatchEventImpl(Component.java:4790)
    	at java.awt.Container.dispatchEventImpl(Container.java:2261)
    	at java.awt.Component.dispatchEvent(Component.java:4616)
    	at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4803)
    	at java.awt.LightweightDispatcher.processMouseEvent(Container.java:4472)
    	at java.awt.LightweightDispatcher.dispatchEvent(Container.java:4393)
    	at java.awt.Container.dispatchEventImpl(Container.java:2247)
    	at java.awt.Window.dispatchEventImpl(Window.java:2674)
    	at java.awt.Component.dispatchEvent(Component.java:4616)
    	at java.awt.EventQueue.dispatchEvent(EventQueue.java:662)
    	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)
    Caused by: java.sql.SQLException: General error
    	at sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6985)
    	at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:7113)
    	at sun.jdbc.odbc.JdbcOdbc.SQLExecDirect(JdbcOdbc.java:3109)
    	at sun.jdbc.odbc.JdbcOdbcStatement.execute(JdbcOdbcStatement.java:337)
    	at sun.jdbc.odbc.JdbcOdbcStatement.executeUpdate(JdbcOdbcStatement.java:287)
    	at SQL.updateMthd(SQL.java:92)
    	at SQL.<init>(SQL.java:34)
    	at Userinfo.button1MouseClicked(Userinfo.java:41)
    	... 23 more

  4. #4
    jMaster's Avatar
    jMaster is offline Member
    Join Date
    Oct 2011
    Posts
    16
    Rep Power
    0

    Default Re: INSERT SQL statement problem

    Yeah that is a very General Error, your compiler is right. I also hate SQL error messages...

    You have to say which fields you want to insert the values into.
    INSERT INTO tblUsers(Naams) Values("Johanis")

  5. #5
    Johanis is offline Senior Member
    Join Date
    Oct 2010
    Posts
    139
    Rep Power
    0

    Default Re: INSERT SQL statement problem

    OOOOOH ok hahaha! Damn I hate them, they make everything always seems so over coplicated! Thanx for the help.

  6. #6
    doWhile is offline Moderator
    Join Date
    Jul 2010
    Location
    California
    Posts
    1,641
    Rep Power
    7

    Default Re: INSERT SQL statement problem

    Quote Originally Posted by jMaster View Post
    Yeah that is a very General Error, your compiler is right. I also hate SQL error messages...

    You have to say which fields you want to insert the values into.
    INSERT INTO tblUsers(Naams) Values("Johanis")
    General yes. Typically a syntax error will say so. With regards to your SQL syntax suggestion, this may not be necessary - see at the following: SQL INSERT INTO Statement - depending upon how the table is organized.

    Johanis, you mention it works with empty strings, so do your strings have any single quotes in them? Do all your database columns you are updating in this table allow for the String datatype, and if so are they long enough for the info you are passing? Are you sure your table is organized as such so for that type of query syntax (see link above)? Can you insert the same information using a command line utility?
    Last edited by doWhile; 11-08-2011 at 11:04 PM.

  7. #7
    Johanis is offline Senior Member
    Join Date
    Oct 2010
    Posts
    139
    Rep Power
    0

    Default Re: INSERT SQL statement problem

    My problem isn't solved. It still gives me the same errors.
    Java Code:
             updateMthd("INSERT INTO Table1(Player, Opponent, Date) VALUES('"+naam1+"','"+naam2+"','"+date+"')");

  8. #8
    Johanis is offline Senior Member
    Join Date
    Oct 2010
    Posts
    139
    Rep Power
    0

    Default Re: INSERT SQL statement problem

    Quote Originally Posted by doWhile View Post
    General yes. Typically a syntax error will say so. With regards to your SQL syntax suggestion, this may not be necessary - see at the following: SQL INSERT INTO Statement - depending upon how the table is organized.

    Johanis, you mention it works with empty strings, so do your strings have any single quotes in them? Do all your database columns you are updating in this table allow for the String datatype, and if so are they long enough for the info you are passing?
    The answer for the last two questions are yes. I don't understand exactly what you mean by the first.

  9. #9
    Johanis is offline Senior Member
    Join Date
    Oct 2010
    Posts
    139
    Rep Power
    0

    Default Re: INSERT SQL statement problem

    It doesn't have any single quotes in them, it does allow for the String type (text), they are long enough, it is ordered in the correct way. I dont know what a command line utility is...

  10. #10
    doWhile is offline Moderator
    Join Date
    Jul 2010
    Location
    California
    Posts
    1,641
    Rep Power
    7

    Default Re: INSERT SQL statement problem

    I don't understand exactly what you mean by the first.
    Imaging what happens if you try the following:

    Java Code:
    String value = "test'ing"
    statement.execute("INSERT INTO my_table (my_value) VALUES ('"+value+"')");
    //Resulting query: INSERT INTO my_table (my_value) VALUES ('test'ing')
    A PreparedStatement would remove the need to worry about this, which is a wide open hole for a hacker to try and punch through with an SQL injection (whether or not this relates to your problem I don't know).

  11. #11
    Johanis is offline Senior Member
    Join Date
    Oct 2010
    Posts
    139
    Rep Power
    0

    Default Re: INSERT SQL statement problem

    Quote Originally Posted by doWhile View Post
    Imaging what happens if you try the following:

    Java Code:
    String value = "test'ing"
    statement.execute("INSERT INTO my_table (my_value) VALUES ('"+value+"')");
    //Resulting query: INSERT INTO my_table (my_value) VALUES ('test'ing')
    A PreparedStatement would remove the need to worry about this, which is a wide open hole for a hacker to try and punch through with an SQL injection (whether or not this relates to your problem I don't know).
    Why would that resulting query happen?? and how do I fix it. Do I understand you correctly?

  12. #12
    doWhile is offline Moderator
    Join Date
    Jul 2010
    Location
    California
    Posts
    1,641
    Rep Power
    7

    Default Re: INSERT SQL statement problem

    Quote Originally Posted by Johanis View Post
    Why would that resulting query happen?? and how do I fix it. Do I understand you correctly?
    Like I said, you fix it with a PreparedStatement. But like you said, you're text contains no single quotes so this apparently isn't your problem.

  13. #13
    Johanis is offline Senior Member
    Join Date
    Oct 2010
    Posts
    139
    Rep Power
    0

    Default Re: INSERT SQL statement problem

    So what do you think is wrong then?

  14. #14
    Johanis is offline Senior Member
    Join Date
    Oct 2010
    Posts
    139
    Rep Power
    0

    Default Re: INSERT SQL statement problem

    If I make a println of the statement is comes out like this, which is correct. INSERT INTO Table1 VALUES('Ping','Pong','YYYYMMDD')

Similar Threads

  1. Problem with hibernate insert
    By dennisbf in forum New To Java
    Replies: 11
    Last Post: 09-15-2011, 07:25 PM
  2. Insert into statement HELP!
    By sdm in forum JDBC
    Replies: 1
    Last Post: 12-14-2010, 08:49 AM
  3. dynamic insert query statement
    By NandiniGarg in forum JDBC
    Replies: 4
    Last Post: 12-09-2010, 11:11 AM
  4. General Insert statement to DB
    By avisik in forum JDBC
    Replies: 1
    Last Post: 11-29-2010, 09:10 AM
  5. insert statement return problem in ibatis
    By dheerajsea123 in forum JDBC
    Replies: 1
    Last Post: 05-20-2010, 11:22 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
  •