Inserting values from JTable to MySQL

Question

I am new here and need your help ...

I'm just a beginner so bear with me for any stupidity in the code.

I have made a project on railway management system and want to insert values from text fields and java table to MySQL database. How can I get rid of this error? "column index out of range,3>1 "

Here's the code I wrote:

      try{

  Class.forName("java.sql.Driver");
  con=DriverManager.getConnection("jdbc:mysql://localhost/railway","root","ridhi");
  st =con.createStatement();

  String sourcee =(String) cb1.getSelectedItem();
  String desti =(String) cb2.getSelectedItem();
  String art =(String) cb3.getSelectedItem();
  String tic= tick.getText();
  String rdate=frm.getText();
  String rdatde=to.getText();
  String rdagte=bp.getText();
  String rdagtge=ru.getText();
  String ddate=date.getText();
  String pnr=l1.getText();

  String q="INSERT INTO pnr values("+l1.getText()+","+cb1.getSelectedItem()+","+"'"+cb2.getSelectedItem()+"'"+","+"'"+frm.getText()+"'"+","+"'"+to.getText()+"'"+","+"'"+date.getText()+"'"+","+"'"+cb3.getSelectedItem()+"'"+","+"'"+ru.getText()+"'"+","+"'"+bp.getText()+"'"+","+tick.getText()+");";

  st.executeUpdate(q);
  while(rs.next()){
     model.getRow(new Object[]{
        rs.getInt(11),rs.getString(12),rs.getInt(13), rs.getString(14),
        rs.getString(15), rs.getString(16) 
               }  );   }
         }

  catch(Exception e){
    JOptionPane.showMessageDialog(null,e.getMessage());

        }


     error: 

    java.sql.SQLException: Column count doesn't match value count at row 1
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1055)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3491)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3423)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1936)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2060)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2536)
    at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1564)
    at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1485)
    at reservation.reservationActionPerformed(reservation.java:348)
    at reservation.access$100(reservation.java:21)
    at reservation$2.actionPerformed(reservation.java:244)
    at javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:1995)
    at javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.java:2318)
    at javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:387)
    at javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:242)
    at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(BasicButtonListener.java:236)
    at java.awt.Component.processMouseEvent(Component.java:6216)
    at javax.swing.JComponent.processMouseEvent(JComponent.java:3265)
    at java.awt.Component.processEvent(Component.java:5981)
    at java.awt.Container.processEvent(Container.java:2041)
    at java.awt.Component.dispatchEventImpl(Component.java:4583)
    at java.awt.Container.dispatchEventImpl(Container.java:2099)
    at java.awt.Component.dispatchEvent(Component.java:4413)
    at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4556)
    at java.awt.LightweightDispatcher.processMouseEvent(Container.java:4220)
    at java.awt.LightweightDispatcher.dispatchEvent(Container.java:4150)
    at java.awt.Container.dispatchEventImpl(Container.java:2085)
    at java.awt.Window.dispatchEventImpl(Window.java:2475)
    at java.awt.Component.dispatchEvent(Component.java:4413)
    at java.awt.EventQueue.dispatchEvent(EventQueue.java:599)
    at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:269)
    at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:184)
    at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:174)
    at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:169)
    at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:161)
    at java.awt.EventDispatchThread.run(EventDispatchThread.java:122)

What is the error you're getting? If there's no error, what isn't working? — Nic, Dec 20 '14 at 13:11
Put that in your post, and ask us a question, please. Something like "How can I get rid of this error?" Providing the full stack trace would also be nice, in [PasteBin](http://pastebin.com/) or something similar if it's too long. — Nic, Dec 20 '14 at 13:24
i'm sorry but can you please tell me what to mean by full stack trace. — ridhi, Dec 20 '14 at 13:33
The error it gives you. The whole thing. When it spits back at you, copy/paste all of it, not just the first line. Try adding "e.printStackTrace()" to the catch block, and giving us everything that appears. — Nic, Dec 20 '14 at 13:45
thanks a lot. also I have edited the code. previous 1 was just a sample .I was trying different code on it . — ridhi, Dec 20 '14 at 13:56
Please, please, please, indent your code. It's impossible to scan through as-is and therefore much, much harder to debug. That a look at the [official Java Code Conventions](http://www.oracle.com/technetwork/java/codeconventions-150003.pdf) and when you write code, follow them. — Nic, Dec 20 '14 at 13:59
Please don't write "Solved" into the title. If your question got solved, then accept the correct answer (like you already did) and that's it. Nothing else to do. — Tom, Dec 21 '14 at 17:22

score 1 · Accepted Answer · edited May 23 '17 at 10:33

1

According to this post this type of error occurs when you are inserting into a table which has more columns then you provide values for; if that happens, you must reference them in parentheses by name:

INSERT INTO tablen_name (column1_name, column2_name, (more column names), column16_name) 
VALUES(value1, value2, (more values), value16);

I assume maybe you also have an id which you don't insert and is not automatically generated? Or some other columns?

Also, as a suggestion, in the insert statement you didn't need to write all those variables by hand, you could have used the references you made above, like for example replace l1.getText() with pnr and so on. (This is not causing the error, but since you just wrote them, it would be better to use them).

The code would look something like:

 String q="INSERT INTO pnr(source, destination, art, tic, rdate, rdatde, dragte, rdagtge,ddate,pnr)
  values("+pnr+","+sourcee+",'"+      desti+"','"+rdate+"','"+rdatde+"','"+
  ddate+"','"+art+"','"+rdagtge+"','"+rdagte+"',"+tic+");";

This, considering that the table's columns are called the way you called the variables earlier.

It is important that you put all the information in the format code i wrote earlier, and that should do the trick, i don't know exactly what your column names and variables containing the values are.

edited May 23 '17 at 10:33

Community

1
1

answered Dec 20 '14 at 18:52

SummerCode

1,403
1
14
28

corrections made.thankx.but what should be the code for inserting values from jtable to MySQL?? same as I wrote?? – ridhi Dec 20 '14 at 20:43
the code u wrote contains the variables of text field. – ridhi Dec 20 '14 at 20:56
MySQL table structure contains 16 columns . 10 I'm inserting through textfeilds and 6 from jtable. – ridhi Dec 20 '14 at 20:58
i assumed those variables are also the names of the columns of your table, if not, then you just need to replace them to each corresponding column...if you dont understand, please edit post to be containing your columns' names – SummerCode Dec 20 '14 at 20:59
shall I post the scrnshot – ridhi Dec 20 '14 at 21:00
you can do that, i still don't think i understand your error...i don't see where you get the information from the jtable? – SummerCode Dec 20 '14 at 21:02
10 reputation needed for uploading image :( – ridhi Dec 20 '14 at 21:06
jtable by default is editable... it has user input. just as in textfeilds. – ridhi Dec 20 '14 at 21:08
so when submit button is clicked it should take the values from text feilds as well as from jtable. – ridhi Dec 20 '14 at 21:10
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/67409/discussion-between-summercode-and-ridhi). – SummerCode Dec 21 '14 at 10:10

Inserting values from JTable to MySQL

1 Answers1