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.

Add e.printStackTrace(); inside your catch block to see if you're suppressing an exception. — Bill the Lizard
– Bill the Lizard, Commented Oct 14, 2013 at 16:03
I have added and debugged. But the error remains. I have now edited my code - I added the exception it displayed : java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Number of query values and destination fields are not the same. — Harshan01
– Harshan01, Commented Oct 14, 2013 at 16:49

Bill the Lizard · Accepted Answer · 2013-10-14 16:58:56Z

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.

answered Oct 14, 2013 at 16:58

Bill the Lizard

407k213 gold badges579 silver badges892 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Harshan01 Over a year ago

Sorry. What do you mean by the word escape. So how should the code look. values("+username+","+password1+", ...

Bill the Lizard Over a year ago

@Harshan01 No, you need the quotes around each piece of data. What are the actual values you're entering in your form? Do any of those have single quotes in them?

Harshan01 Over a year ago

No. All are just simple texts like names, e.g.: John, Gates, etc.

Bill the Lizard Over a year ago

@Harshan01 I'd try the insert statement directly in SQL to make sure it works.

Bill the Lizard Over a year ago

@Harshan01 You can create a query, then go to the SQL view to write/run a query in Access. i.sstatic.net/t2Qq6.png

|

dogrgaut · Accepted Answer · 2013-10-14 16:15:01Z

0

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.

answered Oct 14, 2013 at 16:15

dogrgaut

277 bronze badges

2 Comments

Harshan01 Over a year ago

I have added and debugged. But the error remains. I have now edited my code - I added the exception it displayed : java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Number of query values and destination fields are not the same.

dogrgaut Over a year ago

Are you passing extra ',' in your insert value fields. Also, it will be better to identify the line which is causing the problem - pick up the raw sql first and execute on db client to validate.

Collectives™ on Stack Overflow

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

2 Answers 2

9 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related