sql error: Number of query values and destination fields are not the same.

[cypriot]

Hi. I am developing an application program in java. I use MsAccess for keeping data.

I had a problem with sql insert method.

 
public void AddPatient()
         {
              String no= numberinput.getText();
              String f_name=nameinput.getText();
              String l_name= surnameinput.getText();
              String phone =phoneinput.getText();
              String b_day=dateofbirthinput.getText();
              String text=textpane.getText();
 
 
            try{
 
         Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
 
           con= DriverManager.getConnection(url);
           st= con.createStatement();
sql="INSERT INTO PatientDB (PatientID,Name,Surname,Phoneno,Dateofbirth,Complaint)VALUES(no+f_name+ l_name +phone + b_day+ text)";
 
           st.executeUpdate(sql);
           con.close();
         }
 
      catch(ClassNotFoundException e)
               {}
 
 
        catch(SQLException ex)
        {
        ex.printStackTrace();
        }
 
    }  
 
 

I get this sql error.

[error]

java.sql.SQLExc eption: [Microsoft][ODBC Microsoft Access Driver] Number of query values and destination fields are not the same.
at sun.jdbc.odbc.J dbcOdbc.createS QLException(Jdb cOdbc.java:6958 )
at sun.jdbc.odbc.J dbcOdbc.standar dError(JdbcOdbc .java:7115)
at sun.jdbc.odbc.J dbcOdbc.SQLExec Direct(JdbcOdbc .java:3111)
at sun.jdbc.odbc.J dbcOdbcStatemen t.execute(JdbcO dbcStatement.ja va:338)
at sun.jdbc.odbc.J dbcOdbcStatemen t.executeUpdate (JdbcOdbcStatem ent.java:288)
at Screen.AddPatie nt(Screen.java: 211)
at __SHELL28.run(_ _SHELL28.java:8 )
at sun.reflect.Nat iveMethodAccess orImpl.invoke0( Native Method)
at sun.reflect.Nat iveMethodAccess orImpl.invoke(N ativeMethodAcce ssorImpl.java:3 9)
at sun.reflect.Del egatingMethodAc cessorImpl.invo ke(DelegatingMe thodAccessorImp l.java:25)
at java.lang.refle ct.Method.invok e(Method.java:5 85)
at bluej.runtime.E xecServer$3.run (ExecServer.jav a:858)

[/error]

[FishVal]

Hi, there.

To the best of my knowledge VALUES list should be delimited with commas, not pluses.

[MMcCarthy]

I think + is the concatenation operator. However, you will need the commas and you need to keep the variables outside the quotes. You will also have to use single quotes on those fields which are text and if any are dates such as b_day then you will have to use #. Something like the following:

sql="INSERT INTO PatientDB (PatientID,Name ,Surname,Phonen o,Dateofbirth,C ompla int)VALUES(" + no + ", '" + f_name + "', '" + l_name + "', " + phone + ", #" + b_day + "#, '" + text + "')";