Yousaf Yousaf - 21 days ago 12
Java Question

Inserting into Access database from NetBeans

I have created a GUI based Hotel Management System in java netbeans which is connected with Ms Access Database. In database, i have a table named "RoomInfo".

When i try to add a new room record in to the database using insert query, i get two kinds of errors.

First error is

net.ucanaccess.jdbc.UcanaccessSQLException: UCAExc:::3.0.7 unexpected token: ,
I get this error when i leave all the Jtextfields empty and try to insert new record in to the database.

Second Error is
net.ucanaccess.jdbc.UcanaccessSQLException: UCAExc:::3.0.7 user lacks privilege or object not found: Economy
(Economy is the entry for room category) I get this error when i enter data in to the Jtextfields and try to save it in to the database.

Need help identifying the problem.

Room Class

public class Room {

String roomno;
String reserved;
String category;
String AirConditioned;
String bedtype;
String rent;

public Room()
{
roomno = "";
reserved = "";
category = "";
AirConditioned = "";
bedtype = "";
rent = "";
}
}


AddRoom Class

public class AddRoom extends javax.swing.JFrame {

Connection con;
ResultSet rs;
PreparedStatement ps;
Statement stmt;

Room objr = new Room();

public AddRoom() {
initComponents();
make_connection();
}

public void make_connection()
{
try{
String driver = "net.ucanaccess.jdbc.UcanaccessDriver";
Class.forName(driver);
String login = "jdbc:ucanaccess://C:\\MsDatabase\\EmployeeDB.accdb";
con = DriverManager.getConnection(login);

}catch(Exception ex){ System.out.println(ex);}
}

public void addRoom()
{
try{
String sql2 = "SELECT * FROM RoomInfo";
ps = con.prepareStatement(sql2);
rs = ps.executeQuery();

objr.roomno = jTextField1.getText();
objr.reserved = jTextField2.getText();
objr.category = jTextField3.getText();
objr.AirConditioned = jTextField4.getText();
objr.bedtype = jTextField5.getText();
objr.rent = jTextField6.getText();

String sql = "INSERT INTO RoomInfo(RoomNumber,Reserved,RoomCategory,AirConditioned,BedType,RentPerDay)"
+ "VALUES("+objr.roomno+","+objr.reserved+","+objr.category+","
+objr.AirConditioned+","+objr.bedtype+","+objr.rent+")";
ps = con.prepareStatement(sql);

ps.setString(1, objr.roomno);
ps.setString(2, objr.reserved);
ps.setString(3, objr.category);
ps.setString(4, objr.AirConditioned);
ps.setString(5, objr.bedtype);
ps.setString(6, objr.rent);

ps.executeUpdate();

JOptionPane.showMessageDialog(null, "Room Added Successfully");

if("".equals(jTextField1.getText())||"".equals(jTextField2.getText())||
"".equals(jTextField3.getText())||"".equals(jTextField4.getText())||
"".equals(jTextField5.getText())||"".equals(jTextField6.getText()))
{
JOptionPane.showMessageDialog(null, "None of the fields can be left empty");
}

}catch(Exception ex){
System.out.println(ex);
ex.printStackTrace();
//JOptionPane.showMessageDialog(null, "Input in Room Number and "
// + "Rent Per Day should be a number");
}
}

/* Create and display the form */
java.awt.EventQueue.invokeLater(new Runnable() {
public void run() {
new AddRoom().setVisible(true);
}
});
}

// Variables declaration - do not modify
private javax.swing.JButton jButton1;
private javax.swing.JButton jButton2;
private javax.swing.JLabel jLabel1;
private javax.swing.JLabel jLabel2;
private javax.swing.JLabel jLabel3;
private javax.swing.JLabel jLabel4;
private javax.swing.JLabel jLabel5;
private javax.swing.JLabel jLabel6;
private javax.swing.JLabel jLabel7;
private javax.swing.JPanel jPanel1;
private javax.swing.JTextField jTextField1;
private javax.swing.JTextField jTextField2;
private javax.swing.JTextField jTextField3;
private javax.swing.JTextField jTextField4;
private javax.swing.JTextField jTextField5;
private javax.swing.JTextField jTextField6;
// End of variables declaration
}


I printed the stack trace and both errors appear to be at line
ps = con.prepareStatement(sql);

Answer

Instead of passing the the variable names directly into the sql string and then setting them again using ps.setString(); just use place holders in the sql string. What I mean is

String sql = "INSERT INTO RoomInfo (RoomNumber, Reserved, RoomCategory, AirConditioned, BedType, RentPerDay) 
VALUES (?, ?, ?, ?,?)";
        ps = con.prepareStatement(sql);

        ps.setString(1, objr.roomno);
        ps.setString(2, objr.reserved);
        ps.setString(3, objr.category);
        ps.setString(4, objr.AirConditioned);
        ps.setString(5, objr.bedtype);
        ps.setString(6, objr.rent);