How to solve this SQL problem from input or insert the data at HTML site and make it display at database (phpmyadmin)?

Question

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 17-May-22 21:43pm

aufa 2022

Updated 17-May-22 22:03pm

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

CHill60 · Accepted Answer · 2022-05-17T22:03:00

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 ?)

Richard MacCutchan · Accepted Answer · 2022-05-17T22:03:00

Solution 2

You have an extra comma after the last ? character in the values list.

Posted 17-May-22 22:03pm

Richard MacCutchan

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!