the error goes like this:
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'order (custNo, dateOrder, totalPayable) VALUES ('', '2012/Feb/19 16:09:02', '0' at line 1
// HELP T_T. this project is to be passed this tuesday and im workin with this since friday. sigh :((
import java.awt.*;
import java.awt.event.*;
import javax.swing.*;
import java.util.logging.Level;
import java.util.logging.Logger;
import com.mysql.jdbc.Connection;
import java.sql.*;
import java.util.Calendar;
import java.text.SimpleDateFormat;
public class order extends JFrame {
JLabel c;
JLabel prodna1;
JLabel qtty;
JTextField tf_string;
JTextField gqty;
JButton verify;
//JLabel date1;
JTextField cusno;
JComboBox prodna;
JComboBox combobox_3;
JLabel label_7;
JButton ok;
JButton cancel;
String url = "jdbc:mysql://localhost/group1a";
Connection conn = null;
String username = "root", password = "";
String query = "SELECT * FROM customer";
String query2 = "SELECT * FROM products";
Statement stmt = null;
ResultSet rs = null;
public order() {
Calendar currentDate = Calendar.getInstance();
SimpleDateFormat formatter = new SimpleDateFormat("yyyy/MMM/dd HH:mm:ss");
final String dateNow = formatter.format(currentDate.getTime());
orderLayout customLayout = new orderLayout();
getContentPane().setFont(new Font("Helvetica", Font.PLAIN, 12));
getContentPane().setLayout(customLayout);
c = new JLabel("Customer No:"); //label
getContentPane().add(c);
prodna1 = new JLabel("Product Name:"); //label
getContentPane().add(prodna1);
qtty = new JLabel("Quantity:"); //label
getContentPane().add(qtty);
tf_string = new JTextField(""); //textfield
getContentPane().add(tf_string);
verify = new JButton("→"); //button
getContentPane().add(verify);
cusno = new JTextField(""); //label
getContentPane().add(cusno);
try
{
Class.forName("com.mysql.jdbc.Driver").newInstance();
conn = (Connection) DriverManager.getConnection(url,username,password);
//System.out.println("DBMS Connection Established");
stmt = (Statement) conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
rs = stmt.executeQuery(query2);
}
catch(Exception e)
{
System.out.println("DBMS Connection Error");
e.printStackTrace();
}
try{
//combobox sa products
rs.last();
int i = rs.getRow();
String [] v = new String [i];
rs.beforeFirst();
i = 0;
while(rs.next())
{
v[i] = rs.getString(2);
i++;
}
prodna = new JComboBox(v);
}
catch(Exception e)
{
}
getContentPane().add(prodna);
gqty = new JTextField("");
getContentPane().add(gqty);
label_7 = new JLabel("Customer Search:"); //label
getContentPane().add(label_7);
ok = new JButton("OK"); //button
getContentPane().add(ok);
cancel = new JButton("Cancel"); //button
getContentPane().add(cancel);
setSize(getPreferredSize());
try{
//Class.forName("com.mysql.jdbc.Driver");
//final Connection con = (Connection) DriverManager.getConnection(url,username,password);
//final Statement stat = (Statement) con.createStatement();
final PreparedStatement s = null;
final int b = 0;
//s = con.prepareStatement("INSERT INTO order (orderNo,custNo, dateOrder, totalPayable) VALUES (?,?,?,?)");
final String cNo = cusno.getText();
final String proNa = (String) prodna.getSelectedItem();
//String qnty = dateNow;
ok.addActionListener(new ActionListener( ) { @Override
public void actionPerformed(ActionEvent ae) {
try {
try {
//stat.executeUpdate("INSERT INTO customer "+"(custNa,contact,email,address) "+" VALUES('"+cusName.getText()+"','"+contact.getText()+"','"+email.getText()+"','"+addr.getText()+"')");
//s.executeUpdate("INSERT INTO order " + "(custNo,dateOrder,totalPayable) " + " VALUES('" + cNo + "','" + dateNow + "','" + b + "')");
//ResultSet rsp = stat.executeQuery("SELECT prodNo, qty, unitPrice FROM products WHERE prodNa = '"+prodna.getSelectedItem()+"'");
//s.setString(1, null);
//s.setString(3, dateNow);
//s.setString(2, cNo);
//s.setInt(4, b);
//s.executeUpdate();
//s.close();
//s.executeQuery("SELECT prodNo, qty, unitPrice FROM products WHERE prodNa = 'proNa'");
Class.forName("com.mysql.jdbc.Driver");
Connection conn = (Connection) DriverManager.getConnection(url,username,password);
Statement st = conn.createStatement();
st.executeUpdate("INSERT INTO order " + " (custNo, dateOrder, totalPayable) " + " VALUES ('"+cNo+"', '"+dateNow+"', '"+b+"')");
conn.close();
} catch (ClassNotFoundException ex) {
Logger.getLogger(order.class.getName()).log(Level.SEVERE, null, ex);
}
} catch (SQLException ex) {
Logger.getLogger(Main.class.getName()).log(Level.SEVERE, null, ex);
}
JOptionPane.showMessageDialog(null,"" + prodna.getSelectedItem() + ", " + gqty.getText(), "Records has been ADDED", JOptionPane.INFORMATION_MESSAGE);
setVisible(false);
newOrder n = new newOrder();
n.setVisible(true);
n.setLocation(500, 200);
}
});
}catch(Exception e){
System.out.println("DBMS Connection ERROR");
e.printStackTrace();
} finally{
if(conn != null){
try{
System.out.println("DBMS Connection Terminated");
}catch(Exception e){
}
}
}
cancel.addActionListener(new ActionListener( )
{
public void actionPerformed(ActionEvent e)
{
setVisible(false);
newOrder n = new newOrder();
n.setVisible(true);
n.setLocation(500, 200);
}
});
verify.addActionListener(new ActionListener( )
{
public void actionPerformed(ActionEvent ae)
{
try
{
Class.forName("com.mysql.jdbc.Driver").newInstance();
conn = (Connection) DriverManager.getConnection(url,username,password);
System.out.println("DBMS Connection Established");
//stmt = conn.createStatement();
stmt = (Statement) conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
rs = stmt.executeQuery(query);
}
catch(Exception e)
{
System.out.println("DBMS Connection Error");
e.printStackTrace();
}
boolean flag = false;
try
{
rs.beforeFirst();
while (rs.next())
{
String no = rs.getString(1);
//String no = rs.getString("custNo");
String name = rs.getString("custNa");
String email = rs.getString("email");
String contact = rs.getString("contact");
if (name.equalsIgnoreCase(tf_string.getText().trim()))
{
JOptionPane.showMessageDialog(null, no+"\n"+name+"\n"+email+"\n"+contact, "Search Result", JOptionPane.INFORMATION_MESSAGE);
flag = true;
break;
}
}
if(flag==false)
JOptionPane.showMessageDialog(null, tf_string.getText()+" not found", "Search Result", JOptionPane.INFORMATION_MESSAGE);
}
catch (Exception e)
{
e.printStackTrace();
}
}
});
addWindowListener(new WindowAdapter( )
{
public void windowClosing(WindowEvent we)
{
if(conn != null)
{
try
{
rs.close();
stmt.close();
conn.close();
System.out.println("DBMS Connection Terminated");
//System.exit(0);
}
catch(Exception e)
{
e.printStackTrace();
}
}
setVisible(false);
newOrder n = new newOrder();
n.setVisible(true);
n.setLocation(500, 200);
}
});
cancel.addActionListener(new ActionListener( )
{
public void actionPerformed(ActionEvent ae)
{
if(conn != null)
{
try
{
rs.close();
stmt.close();
conn.close();
System.out.println("DBMS Connection Terminated");
}
catch(Exception e)
{
e.printStackTrace();
}
}
//System.exit(0);
setVisible(false);
newOrder n = new newOrder();
n.setVisible(true);
n.setLocation(500, 200);
}
});
}
