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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
chaitu chaituCommented:
st.executeUpdate("INSERT INTO SOHEAD " +
	    		"(sodate, buyerno, buyer) " +
	    		"VALUES ('"+sodate+"', '"+custno+"', '"+mimporter+"', keycolumn)");

Open in new window

0
CompTIA Security+

Learn the essential functions of CompTIA Security+, which establishes the core knowledge required of any cybersecurity role and leads professionals into intermediate-level cybersecurity jobs.

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
chaitu chaituCommented:

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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 chaituCommented:
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 chaituCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Programming Languages-Other

From novice to tech pro — start learning today.