help me with this error
hi everyone,plz help me with this error
java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 6.
my code:
public class test {
public static void main(String[] args)
{
try{
Connection con;
Statement stat;
String First_Name="ealfe";
String Last_Name= "david";
String UserID = "sky";
String Address = "londod";
String Answer = "red";
String Password = "dddfrfr";
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
con = DriverManager.getConnection("jdbc:odbc:test");
stat = con.createStatement();
stat.executeUpdate("INSERT INTO ClientT " +
"VALUES (First_Name, Last_Name, UserID, Address, Answer, Password )");
stat.close();
con.close();
}
catch(ClassNotFoundException e){System.out.println(e);}
catch(SQLException e){System.out.println(e);}
}
}
i want to insert a set of string into my tabel,but the error occure so i need the help .thank you
# 1
Always printout your SQL String to see if you formatted it correctly. In your case, you did not format the String correctly using Java. To avoid this problem and help with a variety of other issues that you will run into, use a PreparedStatement rather than a Statement.
BTW: Your SQL String sent to the database will print out like this:
INSERT INTO ClientT VALUES(First_name,Last_Name,UserID,Address,Answer,Password)
Also please use code formatting when including code in your post (click on formatting tips when you are editing to see how).
# 2
Sorry for not using code formatting because this is the first time i enter and ask for the help, than you for your replay but i not understand you.
i have a method in my program called StoreInDB and i pass a parameters read from textbox and this method should insert to a database tabel.
My Method is :
public static void StoreInDB(String FName,String LName,String DId,String address,String ans,String DPass)
{
try{
Connection con;
Statement stat;
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
con = DriverManager.getConnection("jdbc:odbc:test");
stat = con.createStatement();
stat.executeUpdate("INSERT INTO ClientT " +
"VALUES (FName, LName, DId, address, ans, DPass )");
stat.close();
con.close();
}
catch(ClassNotFoundException e){System.out.println(e);}
catch(SQLException e){System.out.println(e);}
}
This method is occure an error :
java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 6.
please be patient with me and correct my code
thank you very much for your interesting.
# 3
try this:
public static void StoreInDB(String FName,String LName,String DId,String address,String ans,String DPass)
{
try{
Connection con;
Statement stat;
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
con = DriverManager.getConnection("jdbc:odbc:test");
stat = con.createStatement();
stat.executeUpdate("INSERT INTO ClientT " +
"VALUES ("+FName+", "+LName+", "+DId+", "+address+", "+ans+", "+DPass+" )");
stat.close();
con.close();
}
catch(ClassNotFoundException e){System.out.println(e);}
catch(SQLException e){System.out.println(e);}
}
hope that helps
# 4
you can even use a more robust code like this:
INSERT INTO table_name (column1, column2,...)
VALUES (value1, value2,....)
See http://www.w3schools.com/sql/sql_insert.asp
btw, what are the column names corresponding to your StoreInDB function parameters?
anyway, suppose that that they have the same names.
in this case, your code should be:
public static void StoreInDB(String FName,String LName,String DId,String address,String ans,String DPass)
{
try{
Connection con;
Statement stat;
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
con = DriverManager.getConnection("jdbc:odbc:test");
stat = con.createStatement();
stat.executeUpdate("INSERT INTO ClientT " +
" (FName, LName, DId, address, ans, DPass ) VALUES ("+FName+", "+LName+", "+DId+", "+address+", "+ans+", "+DPass+" )");
stat.close();
con.close();
}
catch(ClassNotFoundException e){System.out.println(e);}
catch(SQLException e){System.out.println(e);}
}
# 5
thank you java2006 for your repay,but it does not work.
so i make a trace through bouttom F7 to see what is the problem so
public static void StoreInDB(String FName,String LName,String DId,String address,String ans,String DPass)
{
try{
Connection con;
Statement stat;
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
con = DriverManager.getConnection("jdbc:odbc:test");
stat = con.createStatement();
stat.executeUpdate("INSERT INTO ClientT " +
" (First_Name, Last_Name, UserID, Address, Answer, Password ) VALUES ("+FName+", "+LName+", "+DId+", "+address+", "+ans+", "+DPass+" )");
// stat.executeUpdate("INSERT INTO ClientT " +
//"VALUES (FName, LName, DId, address, ans, DPass )");
stat.close();
con.close();
}
catch(ClassNotFoundException e){System.out.println(e);}
catch(SQLException e){System.out.println(e);}
}
when he came to method StoreInDB the trace enter the first statment and down to the second statment but when i put the mouse on the first statment ( Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
it appeare to me this message :
sun.jdbc = > Unknown type "sun.jdbc"<
and when the trace pass the second statment it appear the message:
sun = >"sun" is not a known variable in current context <
and then also pass the third statment of creat atatment with unkown variable in current context
and skip the statment of
stat.executeUpdate("INSERT INTO ClientT " +
" (First_Name, Last_Name, UserID, Address, Answer, Password ) VALUES ("+FName+", "+LName+", "+DId+", "+address+", "+ans+", "+DPass+" )");
it does not run it and then leave all the rest of statment and out the method.
please if anyone know what is going on tell me because i need it very important. thanks
# 6
try this and post the whole error message plz:
public static void StoreInDB(String FName,String LName,String DId,String address,String ans,String DPass)
{
try{
Connection con;
Statement stat;
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
con = DriverManager.getConnection("jdbc:odbc:test");
stat = con.createStatement();
stat.executeUpdate("INSERT INTO ClientT " +
" (First_Name, Last_Name, UserID, Address, Answer, Password ) VALUES ('"+FName+"', '"+LName+"', '"+DId+"', '"+address+"', '"+ans+"', '"+DPass+"' )");
// stat.executeUpdate("INSERT INTO ClientT " +
//"VALUES (FName, LName, DId, address, ans, DPass )");
stat.close();
con.close();
}
catch(Exception e){e.printStackTrace();}
}
What are the data types of your columns ?
BTW, if you have lot of data to insert into your db, use a preparedstatement instead.
here a typical code:
ry {
// Prepare a statement to insert a record
String sql = "INSERT INTO my_table (col_string) VALUES(?)";
PreparedStatement pstmt = connection.prepareStatement(sql);
// Insert 10 rows
for (int i=0; i<10; i++) {
// Set the value
pstmt.setString(1, "row "+i);
// Insert the row
pstmt.executeUpdate();
}
} catch (SQLException e) {
}
Message was edited by:
java_2006
# 7
hi again java2006 ,the same error is still occure the error is
java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 6.
And the type of all the field in my database tabel is text
and i try to insert the followeing variables that i pass it to my method
StoreInDB as a parameters.
Fname
LName
DId
address
ans
DPass
and the cloulmn name of the tabel is
First_Name
Last_Name
UserID
Address
Answer
Password
but i think there is something wrong in loading a Driver
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
but i am sure of loading and eastablishing a connection.
the following hint is appear when i put the mouse on this statment it display
sun.jdbc = >Unknown type "sun.jdbc"<
thanks
# 8
1- before data insertion, try to make a simple select:
try {
// Register JDBC/ODBC Driver in jdbc DriverManager
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").newInstance();
String url = "jdbc:odbc:test";
java.sql.Connection c = DriverManager.getConnection(url,"", "");
java.sql.Statement st = c.createStatement();
java.sql.ResultSet rs = st.executeQuery("select * from ClientT");
java.sql.ResultSetMetaData md = rs.getMetaData();
while(rs.next()) {
System.out.print("\nTUPLE: | ");
for(int i=1; i<= md.getColumnCount(); i++) {
System.out.print(rs.getString(i) + " | ");
}
}
rs.close();
} catch(Exception e) {
e.printStackTrace();
}
2- use e.printStackTrace();
and post the whole error message plz.
3- try this: Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").newInstance();
4- your code seems to be correct. See http://www.jguru.com/forums/view.jsp?EID=1335892
See the two last posts.
Message was edited by:
java_2006
