How to Fix Insert Statement into SQLite3 DB

I am trying to insert the fields from my JComponents into a table SOHEAD.  I keep getting the error below on line 49 which is the "Insert into SOHEAD" line.  The only JFormattedTextField in that line is SODATE.  The table column is DATE type size 10.  
Snippet ID=8241213 shows how I have that component delcared.
Snippet ID=8241224 shows the insert statement.

ERROR ON THE INSERT LINE -->
Clicked - Save
Table CELL_SOHEAD exists
Connection successful!
java.sql.SQLException: near "'javax.swing.JFormattedTextField[,114,54,80x20,layout=javax.swing.plaf.basic.BasicTextUI$UpdateHandler,alignmentX=0.0,alignmentY=0.0,border=javax.swing.plaf.BorderUIResource$CompoundBorderUIResource@1f0aecc,flags=296,maximumSize=,minimumSize=,preferredSize=,caretColor=sun.swing.PrintColorUIResource[r=51,g=51,b=51],disabledTextColor=javax.swing.plaf.ColorUIResource[r=184,g=207,b=229],editable=true,margin=javax.swing.plaf.InsetsUIResource[top=0,left=0,bottom=0,right=0],selectedTextColor=sun.swing.PrintColorUIResource[r=51,g=51,b=51],selectionColor=javax.swing.plaf.ColorUIResource[r=184,g=207,b=229],columns=0,columnWidth=0,command=,horizontalAlignment=LEADING]'": syntax error
      at org.sqlite.DB.throwex(DB.java:288)
      at org.sqlite.NativeDB.prepare(Native Method)
      at org.sqlite.DB.prepare(DB.java:114)
      at org.sqlite.Stmt.executeUpdate(Stmt.java:102)
      at insertNewMoccaRecord.<init>(insertNewMoccaRecord.java:49)
      at MOCA$ActionPerformer.actionPerformed(MOCA.java:124)
      at javax.swing.AbstractButton.fireActionPerformed(Unknown Source)
      at javax.swing.AbstractButton$Handler.actionPerformed(Unknown Source)
      at javax.swing.DefaultButtonModel.fireActionPerformed(Unknown Source)
      at javax.swing.DefaultButtonModel.setPressed(Unknown Source)
      at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(Unknown Source)
      at java.awt.Component.processMouseEvent(Unknown Source)
      at javax.swing.JComponent.processMouseEvent(Unknown Source)
      at java.awt.Component.processEvent(Unknown Source)
      at java.awt.Container.processEvent(Unknown Source)
      at java.awt.Component.dispatchEventImpl(Unknown Source)
      at java.awt.Container.dispatchEventImpl(Unknown Source)
      at java.awt.Component.dispatchEvent(Unknown Source)
      at java.awt.LightweightDispatcher.retargetMouseEvent(Unknown Source)
      at java.awt.LightweightDispatcher.processMouseEvent(Unknown Source)
      at java.awt.LightweightDispatcher.dispatchEvent(Unknown Source)
      at java.awt.Container.dispatchEventImpl(Unknown Source)
      at java.awt.Window.dispatchEventImpl(Unknown Source)
      at java.awt.Component.dispatchEvent(Unknown Source)
      at java.awt.EventQueue.dispatchEvent(Unknown Source)
      at java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source)
      at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)
      at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
      at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
      at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
      at java.awt.EventDispatchThread.run(Unknown Source)

public static JFormattedTextField sodate, delivdate;
		DateFormat df = DateFormat.getDateInstance(DateFormat.SHORT, Locale.US);
		sodate = new JFormattedTextField(df);
		sodate.setValue(new Date());

Open in new window

// INSERT MEMORY VARIABLES FROM JComponents INTO TABLE
			    int rsInsert = st.executeUpdate("INSERT INTO SOHEAD " +
			    		"(sodate, buyerno, buyer) " +
			    		"VALUES '"+sodate+"', '"+custno+"', '"+mimporter+"', keycolumn");
		    	ResultSet rsKey = st.getGeneratedKeys();
		    	rsKey.next();
		    	System.out.println("Generated key - "+rsKey);

Open in new window

Vanavah EdwardsAsked:
Who is Participating?
 
chaitu chaituConnect With a Mentor Commented:

http://stackoverflow.com/questions/2256504/insert-java-variable-using-java-in-sql
PreparedStatement pst = connection.prepareStatement(
	    		  "INSERT INTO SOHEAD " +
	    		  "(sodate, buyerno, buyer) " +
	    		  "VALUES (?, ?, ?)");

				  pst.setString(1,sodate);
				  pst.setString(2,custno);
				    pst.setString(3,mimporter);

		    	int rsInsert = pst.executeUpdate();

Open in new window

0
 
chaitu chaituCommented:
there is no bracket open before values.

VALUES(

st.executeUpdate("INSERT INTO SOHEAD " +
                      "(sodate, buyerno, buyer) " +
                      "VALUES ('"+sodate+"', '"+custno+"', '"+mimporter+"', keycolumn");
0
 
chaitu chaituCommented:
("INSERT INTO SOHEAD " +
	    		"(sodate, buyerno, buyer) " +
	    		"  VALUES ('"+sodate+"', '"+custno+"', '"+mimporter+"', keycolumn");

Open in new window

0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
chaitu chaituCommented:
st.executeUpdate("INSERT INTO SOHEAD " +
	    		"(sodate, buyerno, buyer) " +
	    		"VALUES ('"+sodate+"', '"+custno+"', '"+mimporter+"', keycolumn)");

Open in new window

0
 
chaitu chaituCommented:
for howmany columsn you want to insert?

what is  keycolumn in VALUES ?Is it a column or value?
0
 
chaitu chaituCommented:
0
 
Vanavah EdwardsAuthor Commented:
You were right.  Howevef, I am now getting a syntax error.  By the way, what is the purpose of the keycolumn in the insert line.

NEW ERROR -->
java.sql.SQLException: near "keycolumn": syntax error
      at org.sqlite.DB.throwex(DB.java:288)
      at org.sqlite.NativeDB.prepare(Native Method)
      at org.sqlite.DB.prepare(DB.java:114)
      at org.sqlite.Stmt.executeUpdate(Stmt.java:102)
      at insertNewMoccaRecord.<init>(insertNewMoccaRecord.java:49)
0
 
Vanavah EdwardsAuthor Commented:
It is the values from the fields in my form that I am inserting into the table.
0
 
Vanavah EdwardsAuthor Commented:
The data from the fields in the form are of different data types.
Sodate is a JFormattedTexField Date; buyer, buyer are text; some are numeric
0
 
chaitu chaituCommented:

add one more column in INSERT statement.
   
        st.executeUpdate("INSERT INTO SOHEAD " +
	    		"(sodate, buyerno, buyer,keycolumn) " +
	    		"VALUES ('"+sodate+"', '"+custno+"', '"+mimporter+"'", keycolumn+")");

Open in new window

0
 
chaitu chaituCommented:
if you are inserting 4 columns use above one;
0
 
chaitu chaituCommented:
as i told you instead of Statement use prepared statement.

st.executeUpdate("INSERT INTO SOHEAD " +
	    		"(sodate, buyerno, buyer,keycolumn) " +
	    		"VALUES ('"+sodate+"', '"+custno+"', '"+mimporter+"'," keycolumn+")");

Open in new window

0
 
Vanavah EdwardsAuthor Commented:
I will take your advice and use prepared statements.  I removed the keycolumn as not in the table.  Below is the new error. The "'SOLAR DYNAMICS LTD.'" is the value in the '"+mimporter+"' section of the insert.

java.sql.SQLException: near "'SOLAR DYNAMICS LTD.'": syntax error
      at org.sqlite.DB.throwex(DB.java:288)
      at org.sqlite.NativeDB.prepare(Native Method)
      at org.sqlite.DB.prepare(DB.java:114)
      at org.sqlite.Stmt.executeUpdate(Stmt.java:102)
      at insertNewMoccaRecord.<init>(insertNewMoccaRecord.java:49)
0
 
chaitu chaituCommented:
paste your code.

 st.executeUpdate("INSERT INTO SOHEAD " +
	    		"(sodate, buyerno, buyer,keycolumn) " +
	    		"VALUES ('"+sodate+"', '"+custno+"', '"+mimporter+"')");

Open in new window

0
 
chaitu chaituCommented:
sorry remove keycolumn
0
 
chaitu chaituCommented:
 pstmt.executeUpdate("INSERT INTO SOHEAD " +
                      "(sodate, buyerno, buyer,keycolumn) VALUES (?,?,?)");
0
 
chaitu chaituCommented:
pstmt.executeUpdate("INSERT INTO SOHEAD " +
                      "(sodate, buyerno, buyer) VALUES (?,?,?)"); 

Open in new window

0
 
Vanavah EdwardsAuthor Commented:
I changed to the prepared statement.  Same error -->

Clicked - Save
Table CELL_SOHEAD exists
Connection successful!
java.sql.SQLException: near "'SOLAR DYNAMICS LTD.'": syntax error
      at org.sqlite.DB.throwex(DB.java:288)
      at org.sqlite.NativeDB.prepare(Native Method)
      at org.sqlite.DB.prepare(DB.java:114)
      at org.sqlite.PrepStmt.<init>(PrepStmt.java:37)
      at org.sqlite.Conn.prepareStatement(Conn.java:231)
      at org.sqlite.Conn.prepareStatement(Conn.java:224)
      at org.sqlite.Conn.prepareStatement(Conn.java:213)
      at insertNewMoccaRecord.<init>(insertNewMoccaRecord.java:49)
PreparedStatement pst = connection.prepareStatement(
		    		  "INSERT INTO SOHEAD " +
		    		  "(sodate, buyerno, buyer) " +
		    		  "VALUES ('"+sodate+"', '"+custno+"', '"+mimporter+"'");
		    	int rsInsert = pst.executeUpdate();
			    ResultSet rsKey = pst.getGeneratedKeys();
		    	rsKey.next();
		    	System.out.println("Generated key - "+rsKey);

Open in new window

0
 
Vanavah EdwardsAuthor Commented:
i dont understand your last post.  You have values and pst.setSTring(1,sodate) etc.  You are saying I have to use both or use the setString syntax.
0
 
Vanavah EdwardsAuthor Commented:
I change to your new code here is the new error -->

Connection successful!
java.sql.SQLException: near "pst": syntax error
      at org.sqlite.DB.throwex(DB.java:288)
      at org.sqlite.NativeDB.prepare(Native Method)
      at org.sqlite.DB.prepare(DB.java:114)
      at org.sqlite.PrepStmt.<init>(PrepStmt.java:37)
      at org.sqlite.Conn.prepareStatement(Conn.java:231)
      at org.sqlite.Conn.prepareStatement(Conn.java:224)
      at org.sqlite.Conn.prepareStatement(Conn.java:213)
      at insertNewMoccaRecord.<init>(insertNewMoccaRecord.java:51)
      at MOCA$ActionPerformer.actionPerformed(MOCA.java:124)
PreparedStatement pst = connection.prepareStatement(
		    		  "INSERT INTO SOHEAD " +
		    		  "pst.setString(1,sodate);" +
		    		  "pst.setString(2,custno);" +
		    		  "pst.setString(3,mimporter);");
		    	int rsInsert = pst.executeUpdate();
			    ResultSet rsKey = pst.getGeneratedKeys();
		    	rsKey.next();
		    	System.out.println("Generated key - "+rsKey);

Open in new window

0
 
chaitu chaituConnect With a Mentor Commented:
do you knw abt PreparedStatement ?if no read this.

http://www.roseindia.net/jdbc/jdbc-mysql/TwicePreparedStatement.shtml

am passing values to respective columns ?,?,? stands for 1st column,2nd column and 3rd column.

instead of this

 PreparedStatement pst = connection.prepareStatement(
		    		  "INSERT INTO SOHEAD " +
		    		  "(sodate, buyerno, buyer) " +
		    		  "VALUES ('"+sodate+"', '"+custno+"', '"+mimporter+"'");

				  use this; 

				  PreparedStatement pst = connection.prepareStatement(
	    		  "INSERT INTO SOHEAD " +
	    		  "(sodate, buyerno, buyer) " +
	    		  "VALUES (?, ?, ?)");

				  pst.setString(1,sodate);
				  pst.setString(2,custno);
				    pst.setString(3,mimporter);

		    	int rsInsert = pst.executeUpdate(); 

Open in new window



0
 
chaitu chaituConnect With a Mentor Commented:
0
 
Vanavah EdwardsAuthor Commented:
I studied the examples and change the code.  But I am getting this error.
ERROR -->>
Clicked - Save
Table CELL_SOHEAD exists
Connection successful!
Exception in thread "AWT-EventQueue-0" java.lang.ArrayIndexOutOfBoundsException: 3
      at org.sqlite.PrepStmt.batch(PrepStmt.java:131)
      at org.sqlite.PrepStmt.setString(PrepStmt.java:186)
      at insertNewMoccaRecord.<init>(insertNewMoccaRecord.java:55)
PreparedStatement pst = connection.prepareStatement(
			    		  "INSERT INTO CELL_SOHEAD " +
			    		  "(sonum, sodate, buyerno, buyer) " +
			    		  "VALUES (?, ?, ?, ?)");

				  		  pst.setString(2,"+sonum+");
						  pst.setString(3,"+sodate+");
						  pst.setString(5,"+custno+");
						  pst.setString(6,"mimporter+");

Open in new window

0
 
Vanavah EdwardsAuthor Commented:
I never heard back from you when you kenw I neded help.  I am new to Java.  However, I tried the code you posted and studied the code on the site you suggested.  By trial and error based upon all your recommendations, I am happy to say I have solved the problem.  I would like to close this session and award your points.  But there is one thing I need your help.  How can I use
 pst.setString(2, sodate.getText()); to store a date.  
Unless I set the date in the database to DATETEXT it wouldn't work.  If I say sodate.getDate() I get a String Error on that line.  Is it possible to set a real date instead of a DATETEXT.
0
 
Vanavah EdwardsAuthor Commented:
Didi not get ever back to me after a while?
0
 
chaitu chaituCommented:
pst.setDate
or
pst.setTimestamp methods are there;check the methods in prepared statements
0
 
Vanavah EdwardsAuthor Commented:
i tried pst.setDate(2, sodate);  and I got this error -->
The method setDate(int, Date) in the type PreparedStatement is not applicable for the arguments (int, JFormattedTextField)
My sodate is a JFormattedTextField
For the timestamp , I got this error --->>
The method setTimestamp(int, Timestamp) in the type PreparedStatement is not applicable for the arguments (int, JFormattedTextField)
Only pst.setString(2, sodate.getText()); works and I must set the DB to DATETEXT
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.