Click here to Skip to main content
15,348,280 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
This is the root cause view at web:

org.apache.jasper.JasperException: An exception occurred processing [/admin/Attendance_OffDayOT.jsp] at line [150]

147:         pst.setString(62, Mealin6smin);
148:         pst.setString(63, out6s);
149:         pst.setString(64, out6smin);      
150:         pst.executeUpdate();    
151: %> 
152: <script>
153:     alert("Data Off Day OT have been input at history Off Day OT. <br> You can go to history Off Day OT and update the data as you want.");


javax.servlet.ServletException: java.sql.SQLSyntaxErrorException: 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 ')' at line 1


What I have tried:

<%@page import="java.sql.*"%>
<%
    if (request.getParameter("submit") != null) {
        String idEmploy = request.getParameter("idEmploy");
        String nameE = request.getParameter("nameE");
        String positionw = request.getParameter("positionw");
        String rmS = request.getParameter("rmS");
        String year1S = request.getParameter("year1S");
        String Sunday1 = request.getParameter("Sunday1");
        String In1s = request.getParameter("In1s");
        String In1smin = request.getParameter("In1smin");
        String Mealout1s = request.getParameter("Mealout1s");
        String Mealout1smin = request.getParameter("Mealout1smin");
        String Mealin1s = request.getParameter("Mealin1s");
        String Mealin1smin = request.getParameter("Mealin1smin");
        String out1s = request.getParameter("out1s");
        String out1smin = request.getParameter("out1smin");
        String year2S = request.getParameter("year2S");
        String Sunday2 = request.getParameter("Sunday2");
        String In2s = request.getParameter("In2s");
        String In2smin = request.getParameter("In2smin");
        String Mealout2s = request.getParameter("Mealout2s");
        String Mealout2smin = request.getParameter("Mealout2smin");
        String Mealin2s = request.getParameter("Mealin2s");
        String Mealin2smin = request.getParameter("Mealin2smin");
        String out2s = request.getParameter("out2s");
        String out2smin = request.getParameter("out2smin");
        String year3S = request.getParameter("year3S");
        String Sunday3 = request.getParameter("Sunday3");
        String In3s = request.getParameter("In3s");
        String In3smin = request.getParameter("In3smin");
        String Mealout3s = request.getParameter("Mealout3s");
        String Mealout3smin = request.getParameter("Mealout3smin");
        String Mealin3s = request.getParameter("Mealin3s");
        String Mealin3smin = request.getParameter("Mealin3smin");
        String out3s = request.getParameter("out3s");
        String out3smin = request.getParameter("out3smin");
        String year4S = request.getParameter("year4S");
        String Sunday4 = request.getParameter("Sunday4");
        String In4s = request.getParameter("In4s");
        String In4smin = request.getParameter("In4smin");
        String Mealout4s = request.getParameter("Mealout4s");
        String Mealout4smin = request.getParameter("Mealout4smin");
        String Mealin4s = request.getParameter("Mealin4s");
        String Mealin4smin = request.getParameter("Mealin4smin");
        String out4s = request.getParameter("out4s");
        String out4smin = request.getParameter("out4smin");
        String year5S = request.getParameter("year5S");
        String Sunday5 = request.getParameter("Sunday5");
        String In5s = request.getParameter("In5s");
        String In5smin = request.getParameter("In5smin");
        String Mealout5s = request.getParameter("Mealout5s");
        String Mealout5smin = request.getParameter("Mealout5smin");
        String Mealin5s = request.getParameter("Mealin5s");
        String Mealin5smin = request.getParameter("Mealin5smin");
        String out5s = request.getParameter("out5s");
        String out5smin = request.getParameter("out5smin");
        String year6S = request.getParameter("year6S");
        String Sunday6 = request.getParameter("Sunday6");
        String In6s = request.getParameter("In6s");
        String In6smin = request.getParameter("In6smin");
        String Mealout6s = request.getParameter("Mealout6s");
        String Mealout6smin = request.getParameter("Mealout6smin");
        String Mealin6s = request.getParameter("Mealin6s");
        String Mealin6smin = request.getParameter("Mealin6smin");
        String out6s = request.getParameter("out6s");
        String out6smin = request.getParameter("out6smin");

        Connection con;
        PreparedStatement pst;
        ResultSet rs;

        Class.forName("com.mysql.cj.jdbc.Driver");
        con = DriverManager.getConnection("jdbc:mysql://localhost:3306/attendance?zeroDateTimeBehavior=CONVERT_TO_NULL", "root", "");
        pst = con.prepareStatement("insert into tbl_sunday(idEmploy,nameE,positionw,rmS,year1S,Sunday1,In1s,In1smin,Mealout1s,Mealout1smin,Mealin1s,Mealin1smin,out1s,out1smin,year2S,Sunday2,In2s,In2smin,Mealout2s,Mealout2smin,Mealin2s,Mealin2smin,out2s,out2smin,year3S,Sunday3,In3s,In3smin,Mealout3s,Mealout3smin,Mealin3s,Mealin3smin,out3s,out3smin,year4S,Sunday4,In4s,In4smin,Mealout4s,Mealout4smin,Mealin4s,Mealin4smin,out4s,out4smin,year5S,Sunday5,In5s,In5smin,Mealout5s,Mealout5smin,Mealin5s,Mealin5smin,out5s,out5smin,year6S,Sunday6,In6s,In6smin,Mealout6s,Mealout6smin,Mealin6s,Mealin6smin,out6s,out6smin)values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,)");
                
        pst.setString(1, idEmploy);
        pst.setString(2, nameE);
        pst.setString(3, positionw);
        pst.setString(4, rmS);
        pst.setString(5, year1S);
        pst.setString(6, Sunday1);
        pst.setString(7, In1s);
        pst.setString(8, In1smin);
        pst.setString(9, Mealout1s);
        pst.setString(10, Mealout1smin);
        pst.setString(11, Mealin1s);
        pst.setString(12, Mealin1smin);
        pst.setString(13, out1s);
        pst.setString(14, out1smin);
        pst.setString(15, year2S);
        pst.setString(16, Sunday2);
        pst.setString(17, In2s);
        pst.setString(18, In2smin);
        pst.setString(19, Mealout2s);
        pst.setString(20, Mealout2smin);
        pst.setString(21, Mealin2s);
        pst.setString(22, Mealin2smin);
        pst.setString(23, out2s);
        pst.setString(24, out2smin);
        pst.setString(25, year3S);
        pst.setString(26, Sunday3);
        pst.setString(27, In3s);
        pst.setString(28, In3smin);
        pst.setString(29, Mealout3s);
        pst.setString(30, Mealout3smin);
        pst.setString(31, Mealin3s);
        pst.setString(32, Mealin3smin);
        pst.setString(33, out3s);
        pst.setString(34, out3smin);
        pst.setString(35, year4S);
        pst.setString(36, Sunday4);
        pst.setString(37, In4s);
        pst.setString(38, In4smin);
        pst.setString(39, Mealout4s);
        pst.setString(40, Mealout4smin);
        pst.setString(41, Mealin4s);
        pst.setString(42, Mealin4smin);
        pst.setString(43, out4s);
        pst.setString(44, out4smin);
        pst.setString(45, year5S);
        pst.setString(46, Sunday5);
        pst.setString(47, In5s);
        pst.setString(48, In5smin);
        pst.setString(49, Mealout5s);
        pst.setString(50, Mealout5smin);
        pst.setString(51, Mealin5s);
        pst.setString(52, Mealin5smin);
        pst.setString(53, out5s);
        pst.setString(54, out5smin);
        pst.setString(55, year6S);
        pst.setString(56, Sunday6);
        pst.setString(57, In6s);
        pst.setString(58, In6smin);
        pst.setString(59, Mealout6s);
        pst.setString(60, Mealout6smin);
        pst.setString(61, Mealin6s);
        pst.setString(62, Mealin6smin);
        pst.setString(63, out6s);
        pst.setString(64, out6smin);      
        pst.executeUpdate();    
%> 

...
....<html>...
<td><input type="date" name="year1S" id="year1S" value=""></td>
                                                    <td><label id="Sunday1" name="Sunday1" value="Sunday">Sunday</label></td>
                                                    <td><input type="text" name="In1s" id="In1s" value="" required size="3"> : <input type="text" name="In1smin" id="In1smin" value="" required size="3"></td>
                                                    <td><input type="text" name="Mealout1s" id="Mealout1s" value="" required size="3"> : <input type="text" name="Mealout1smin" id="Mealout1smin" value="" required size="3"></td>
                                                    <td><input type="text" name="Mealin1s" id="Mealin1s" value="" required size="3"> : <input type="text" name="Mealin1smin" id="Mealin1smin" value="" required size="3"></td>
                                                    <td><input type="text" name  ="out1s" id="out1s" value="" required size="3"> : <input type="text" name="out1smin" id="out1smin" value="" required size="3"> </td> ....</html>
.....
SQL:
CREATE TABLE IF NOT EXISTS tbl_sunday(
    id INT NOT NULL AUTO_INCREMENT,
    idEmploy INT(10),
    nameE VARCHAR(100),
    positionw VARCHAR(60),
    rmS VARCHAR(10),
    year1S DATE,
    Sunday1 VARCHAR(10),
    In1s VARCHAR(10),
    In1smin VARCHAR(10),
    Mealout1s VARCHAR(10),
    Mealout1smin VARCHAR(10),
    Mealin1s VARCHAR(10),
    Mealin1smin VARCHAR(10),
    out1s VARCHAR(10),
    out1smin VARCHAR(10),
    year2S DATE,
    Sunday2 VARCHAR(10),
    In2s VARCHAR(10),
    In2smin VARCHAR(10),
    Mealout2s VARCHAR(10),
    Mealout2smin VARCHAR(10),
    Mealin2s VARCHAR(10),
    Mealin2smin VARCHAR(10),
    out2s VARCHAR(10),
    out2smin VARCHAR(10),
    year3S DATE,
    Sunday3 VARCHAR(10),
    In3s VARCHAR(10),
    In3smin VARCHAR(10),
    Mealout3s VARCHAR(10),
    Mealout3smin VARCHAR(10),
    Mealin3s VARCHAR(10),
    Mealin3smin VARCHAR(10),
    out3s VARCHAR(10),
    out3smin VARCHAR(10),
    year4S DATE,
    Sunday4 VARCHAR(10),
    In4s VARCHAR(10),
    In4smin VARCHAR(10),
    Mealout4s VARCHAR(10),
    Mealout4smin VARCHAR(10),
    Mealin4s VARCHAR(10),
    Mealin4smin VARCHAR(10),
    out4s VARCHAR(10),
    out4smin VARCHAR(10),
    year5S DATE,
    Sunday5 VARCHAR(10),
    In5s VARCHAR(10),
    In5smin VARCHAR(10),
    Mealout5s VARCHAR(10),
    Mealout5smin VARCHAR(10),
    Mealin5s VARCHAR(10),
    Mealin5smin VARCHAR(10),
    out5s VARCHAR(10),
    out5smin VARCHAR(10),
    year6S DATE,
    Sunday6 VARCHAR(10),
    In6s VARCHAR(10),
    In6smin VARCHAR(10),
    Mealout6s VARCHAR(10),
    Mealout6smin VARCHAR(10),
    Mealin6s VARCHAR(10),
    Mealin6smin VARCHAR(10),
    out6s VARCHAR(10),
    out6smin VARCHAR(10),
    PRIMARY KEY(id)
);
Posted
Updated 17-May-22 22:03pm

Look at the line
JavaScript
pst = con.prepareStatement("insert into tbl_sunday(idEmploy,nameE,positionw,rmS,year1S,Sunday1,In1s,In1smin,Mealout1s,Mealout1smin,Mealin1s,Mealin1smin,out1s,out1smin,year2S,Sunday2,In2s,In2smin,Mealout2s,Mealout2smin,Mealin2s,Mealin2smin,out2s,out2smin,year3S,Sunday3,In3s,In3smin,Mealout3s,Mealout3smin,Mealin3s,Mealin3smin,out3s,out3smin,year4S,Sunday4,In4s,In4smin,Mealout4s,Mealout4smin,Mealin4s,Mealin4smin,out4s,out4smin,year5S,Sunday5,In5s,In5smin,Mealout5s,Mealout5smin,Mealin5s,Mealin5smin,out5s,out5smin,year6S,Sunday6,In6s,In6smin,Mealout6s,Mealout6smin,Mealin6s,Mealin6smin,out6s,out6smin)values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,)");
Right at the end you have
JavaScript
?,)");
That extra comma before the close bracket is what is causing your problem - remove it and the problem should go away (you have 64 columns to insert and 64 question marks so you don't need to insert a ?)
   
Comments
aufa 2022 18-May-22 4:25am
   
Thank you for your comment, I have edit and delete the comma, and it works, Thanks a lot!
You have an extra comma after the last ? character in the values list.
   
Comments
aufa 2022 18-May-22 4:26am
   
Thank you, I have edit and delete the comma, and it doesn't have error, Thanks a lot!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900