java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Number of query values and destination fields are not the same

Question

I'm trying to create a JAVA code in which the code will check the database for any existing data and if none is found data entered by the user will be inserted into the database.

try {

        username1=request.getParameter("txt1");
        password1=request.getParameter("p1");
        nickname1=request.getParameter("nick_name1");
        email1=request.getParameter("email_1");
        phone_no1=request.getParameter("phone_no_1");
        date1=request.getParameter("date");
        month1=request.getParameter("month");
        year1=request.getParameter("year");
        school1=request.getParameter("school_1");
        class1=request.getParameter("class_1");
        section1=request.getParameter("section_1");

        // username1, password1, nickname1, email1, phone_no1 - should be unique //

        Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
        con=DriverManager.getConnection("jdbc:odbc:harshan_web");

        if (con != null)
        {

            st=con.createStatement();

           ResultSet rs=st.executeQuery("select * from account_registration where username='"+username1+"' or password='"+password1+"' or nickname='"+nickname1+"' or email='"+email1+"' or phone='"+phone_no1+"'");


                if (rs.next())
                {

                u1 = rs.getString("username");
                p1 = rs.getString("password");
                n1 = rs.getString("nickname");
                e1 = rs.getString("email");
                ph1 = rs.getString("phone");
                invalid_data = "";


            if (username1.equals(u1))
            { u2="Username "; }
            else
            { u2=""; }
            if (password1.equals(p1))
            { p2="Password "; }
            else
            { p2=""; }
            if (nickname1.equals(n1))
            { n2="Nickname "; }
            else
            { n2=""; }
            if (email1.equals(e1))
            { e2="Email-ID "; }
            else
            { e2=""; }
            if (phone_no1.equals(ph1))
            { ph2="Phone-Number "; }
            else
            { ph2=""; }

            invalid_data=""+u2+""+p2+""+n2+""+e2+""+ph2+" has/have already been used! Try Again.";
            response.sendRedirect("index.jsp?invalid_data="+invalid_data+""); 

            }                   
                else
                {

                st.executeUpdate("insert into account_registration values('"+username1+"','"+password1+"','"+nickname1+"','"+date1+"','"+month1+"','"+year1+"','"+school1+"','"+class1+"','"+section1+"','"+email1+"','"+phone_no1+"')");
                response.sendRedirect("reg_complete.html"); 

                }
        }

        else
        {                
            response.sendRedirect("error.html");                
        }

        /* TODO output your page here. You may use following sample code. */

    } catch (Exception e) {} 
      finally {            
        out.close();
    }

It shows the exception as follows.

java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Number of query values and destination fields are not the same.

I don't know what does that mean. Is it a problem with SQL statement or JAVA?

Actually, I have added an AutoNumber field in my database. If I add an extra ,'' just before the first closing bracket in the insert SQL Statement, it gives an another Exception saying Field Type error in SQL Syntax. I mostly think that the error is for that AutoNumber field because the field cannot contain any value except what is generated by the database.

Answer 1

The exception you're getting appears to be because of your insert statement:

st.executeUpdate("insert into account_registration values('"+username1+"','"+password1+"','"+nickname1+"','"+date1+"','"+month1+"','"+year1+"','"+school1+"','"+class1+"','"+section1+"','"+email1+"','"+phone_no1+"')");

Check to see if you have any single quote characters in any of the data fields you're trying to insert into the table and escape them if you do. This might be confusing how your query is being parsed.

Answer 2

Introduce a try catch block and get the exception to confirm, if it is rs.next , the cursor has to be one before the last and it will return false when there is no data in it, so should be safe.