I tried to insert following data to the database. but it gives me the above this error. you have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ' ' in Line 1.
i tried several things but it is not fixing.
try{
Statement stat = Database.getStatement();
stat.executeUpdate("INSERT INTO"+" admission"+
" (a_id,sickness,recPhysicianDetails,
admittedDate,Patient_ID,Doctor_id,Doctor_id1)"+"
VALUES
('"+txt1.getText()+"',
'"+txt7.getText()+"','"+txt8.getText()+"', '"+txt6.getText()+"',
( SELECT ID FROM patient WHERE Patient_name='"+txt3.getText()+"' ),
( SELECT ID FROM employee WHERE Name='"+txt9.getText()+"' ),
( SELECT ID FROM employee WHERE Name='"+txt10.getText()+"')" );
}
catch(Exception e){
e.printStackTrace();
}
In your solution, quotation marks (') are not rightly placed, plus there is a missing closing bracket in the end. Using PreparedStatement will make sure you don't need to worry about quotation marks, try this:
Connection conn = //get connection
PreparedStatement stat = conn.prepareStatement("INSERT INTO admission (a_id,sickness,recPhysicianDetails,admittedDate,Patient_ID,Doctor_id,Doctor_id1)"
+" VALUES(?, ?, ?, ?, (SELECT ID FROM patient WHERE Patient_name = ?),(SELECT ID FROM employee WHERE Name = ?),(SELECT ID FROM employee WHERE Name = ?))");
stat.setString(1, txt1.getText());
stat.setString(2, txt7.getText());
stat.setString(3, txt8.getText());
stat.setString(4, txt6.getText());
stat.setString(5, txt3.getText());
stat.setString(6, txt9.getText());
stat.setString(7, txt10.getText());
stat.executeUpdate();
I will suggest to use prepared statement instead directly calling the value from the textbox.
try{
Statement stat = Database.getStatement();
stat.executeUpdate("INSERT INTO admission"+
"(a_id,sickness,recPhysicianDetails,admittedDate,Patient_ID,Doctor_id,Doctor_id1)"+
" VALUES('"+txt1.getText()+"','"+txt7.getText()+"','"+txt8.getText()+"','"+txt6.getText()+"',"+
"(SELECT ID FROM patient WHERE Patient_name='"+txt3.getText()+"'),"+
"(SELECT ID FROM employee WHERE Name='"+txt9.getText()+"'),"+
"(SELECT ID FROM employee WHERE Name='"+txt10.getText()+"')");
}
catch(Exception e){
e.printStackTrace();
}
You here inserted some double quotation marks in wrong places, please check it, it should work.