prepared statements are not inserted

Question

i am new to JDBC and trying following code. Could not find the error in it

try{     
        Connection con = ConnectionProvider.getCon();
        stmt=con.prepareStatement("insert into create_request values((select count(reqno) from create_request)+1,?,?,?,?,?");
        stmt.setString(1,obj_Leaverequest.getUser_name());
        stmt.setString(2,obj_Leaverequest.getLeave_Type());
        stmt.setInt(3,obj_Leaverequest.getLeave_Units());
        stmt.setString(4,obj_Leaverequest.getLeave_Reason());
        stmt.setString(5,"pending");
        count=stmt.executeUpdate();
        if(count>0){
            status=true;
        }
    }

Getters are working fine, i could print their values . i guess i am wrong with the query.Please point out my mistake. Thanks in advance!!

Answer 1

Specify the columns and use SELECT:

"insert into create_request (col1,col2,etc..col6) 
select ?,?,?,?,?, count(reqno) +1 from create_request"

Just make sure the values and columns match.

Answer 2

I guess you have forgotten a ')'.

try this:

stmt=con.prepareStatement("insert into create_request values((select count(reqno) from create_request)+1),?,?,?,?,?");

Answer 3

@Krish: If your Getters are correct, then can you try below options:

a.) Try using

stmt=con.prepareStatement("insert into create_request values((select count(reqno) from create_request)+1,?,?,?,?,?)");

b.) In case the above one does not work, try running your insert query in database directly with the approprate values for the bind variables.

Please let us know if it works.

Thanks

Vishad