java.sql.SQLSyntaxErrorException: ORA-01722: invalid number while trying to delete checked values using jquery ajax jsp

Question

I'm trying to delete multiple checked values using jquery ajax and JSP. However while deleting, it successfully deletes any single checked value, but when multiple check boxes are selected it throws java.sql.SQLSyntaxErrorException: ORA-01722 error. Please let me know whats wrong with this code.

<!--SAMPLE CODE OF HOME PAGE-->

<html>
<body>


<%

    String s1 = "select * from student_signup order by rollno asc   ";
    pst = con.prepareStatement(s1);

    ResultSet resultSet = pst.executeQuery();


%>

<section id="main-content">
    <div class="container pre-scrollable container2" id="table-container">
        <div class="row">
            <div class="col">
                <table class="table table-hover" id="table1">
                    <thead>
                    <tr>
                        <th scope="col">Action</th>
                        <th scope="col">Roll No</th>
                        <th scope="col">ID</th>
                        <th scope="col">First Name</th>
                        <th scope="col">Last Name</th>
                        <th scope="col">Course</th>

                    </tr>
                    </thead>
                    <tbody>

                    <%
                        while (resultSet.next())
                        {
                    %>


                    <tr>
                        <th>
                            <div class="pretty p-icon p-curve p-has-indeterminate">
                                <input type="checkbox" class="checkitem" value="<%= resultSet.getInt("STUDENT_ID") %>" name="check"/>
                                <div class="state">
                                    <i class="icon mdi mdi-check"></i>
                                    <label>Check</label>
                                </div>
                                <div class="state p-is-indeterminate">
                                    <i class="icon mdi mdi-minus"></i>
                                    <label>Indeterminate</label>
                                </div>
                            </div>
                        </th>
                        <th scope="row"><%= resultSet.getInt("ROLLNO") %></th>
                        <th><%= resultSet.getInt("STUDENT_ID") %></th>
                        <td><%= resultSet.getString("STUDENT_FIRSTNAME") %></td>
                        <td><%= resultSet.getString("STUDENT_LASTNAME") %></td>
                        <td><%= resultSet.getString("COURSE_NAME") %></td>

                    </tr>

                    <%
                        }
                    %>




                    </tbody>
                </table>


            </div>
        </div>
    </div>
</section>
<br><br>
</body>
</html>

JQUERY/JAVASCRIPT AJAX PAGE

$(document).ready(function () {
    $('#trash').click(function (event) {
        event.preventDefault();

        var id =$('.checkitem:checked').map(function () {
            return $(this).val();
        }).get().join();

        if(id)
        {
            if (confirm("Are you sure you want to delete this?"))
            {
                $.ajax({
                    url: 'TeacherStudentDeletion.jsp',
                    type: 'POST',
                    data:{id: id},

                    success: function (result) {
                        $('tbody').html(result);
                    },
                    error: function () {
                        alert('Error while deletion. Please try again.');
                    }
                })

            }
            else
            {
                return false;
            }

        }
        else
        {
            alert("Please select the user first.");
        }



    })


});

SERVER SIDE JSP PAGE FOR DELETION

<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@ page import="java.sql.*" %>

<%
    boolean success = false;

    Connection con;
    PreparedStatement pst;
    ResultSet rs;


    try {


        String[] checkedid = request.getParameterValues("id");


        Class.forName("oracle.jdbc.driver.OracleDriver");
        con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "SUNILREDDY", "oracle");

        if(checkedid.length > 0)
        {

            String s1 = "DELETE FROM student_signup WHERE (STUDENT_ID = ?)";



            for (int i = 0; i < checkedid.length; i++)
            {


                String check = checkedid[i];
                    pst = con.prepareStatement(s1);
                    pst.setString(1, check);
                    int n = pst.executeUpdate();

                    if (n != 0) {
                        success = true;
                    }



            }


            if (success == true)
            {
                String s2 = "select * from student_signup";

                pst = con.prepareStatement(s2);
                rs = pst.executeQuery();


                while (rs.next())
                {
%>

<tr>
    <th>
        <div class="pretty p-icon p-curve p-has-indeterminate">
            <input type="checkbox" class="checkitem" value="<%= rs.getInt("STUDENT_ID") %>" name="<%= rs.getInt("STUDENT_ID") %>"/>
            <div class="state">
                <i class="icon mdi mdi-check"></i>
                <label>Check</label>
            </div>
            <div class="state p-is-indeterminate">
                <i class="icon mdi mdi-minus"></i>
                <label>Indeterminate</label>
            </div>
        </div>
    </th>
    <th scope="row"><%= rs.getInt("ROLLNO") %></th>
    <th><%= rs.getInt("STUDENT_ID") %></th>
    <td><%= rs.getString("STUDENT_FIRSTNAME") %></td>
    <td><%= rs.getString("STUDENT_LASTNAME") %></td>
    <td><%= rs.getString("COURSE_NAME") %></td>

</tr>
<%
                }

            }
            else
            {
                response.sendRedirect("TeacherDeleteFail.jsp");
            }


        }


    } catch (Exception e) {
        e.printStackTrace();
    }



%>

It is very bad to have java code in your JSP, but anyway have a look at https://stackoverflow.com/questions/2467125/reusing-a-preparedstatement-multiple-times — Scary Wombat, May 23 '18 at 07:15
Why not add debug output to the deletion process. And is the STUDENT_ID defined as INT or or STRING type in the database table. And you should prepare the statement only one time before the loop. — Konrad, May 23 '18 at 07:16
Thanks for the responses @Konrad STUDENT_ID is defined as Number in table, I made changes with preparedsteatement , not luck — Sunil Reddy, May 23 '18 at 07:24
Also, when I try to print the data received from ajax it prints the ids of the checkboxes seperated by comma — Sunil Reddy, May 23 '18 at 07:27
@ScaryWombat I'm pretty new to programming. Thanks for letting me know that. However, what could be the best approach, if thats not the way it should be? — Sunil Reddy, May 23 '18 at 07:31
If your code was in a servlet, you could easily debug it. However looking at the link I gave, it could be a case of not calling `pst.close();` at bottom of each loop — Scary Wombat, May 23 '18 at 07:36
@ScaryWombat tried pst.close, no go. I think its number and String conflict, but what I'm not getting is that it deletes any single selection of checkbox, but not multiple selection. And no matter, if its single or multiple selection, the error of 'java.sql.SQLSyntaxErrorException: ORA-01722: invalid number' is still there in both cases — Sunil Reddy, May 23 '18 at 07:48
If it is supposed to be a number then why are you doing `setString` ? — Scary Wombat, May 23 '18 at 07:53
@ScaryWombat you are right. But, when I add setInt as per the table.....delete operation fails for multiple selection and throws this error "java.lang.NumberFormatException: For input string: "11,31" . Here, 11 & 31 are the student_id. However, setInt works perfectly fine for single selection with no error. Thanks — Sunil Reddy, May 23 '18 at 08:04
change your code to `String[] checkedid = request.getParameterValues("id").split (",");` — Scary Wombat, May 23 '18 at 08:05
@ScaryWombat Thanks a lot, Scary!!!! split() did work but not in the JSP page but on the JS file. I modified 'id' to id.split(",") and it worked. But, to let you know, adding split(",") as you suggested was giving error 'Cannot resolve method'. Anyway, was a great exp discussing this with you. And thanks again for resolving this. — Sunil Reddy, May 23 '18 at 08:33

java.sql.SQLSyntaxErrorException: ORA-01722: invalid number while trying to delete checked values using jquery ajax jsp

0 Answers0