我不断收到错误[Microsoft] [ODBC Microsoft Access Driver]参数太少。预计2
问题描述:
以下是我的代码:我不断收到错误[Microsoft] [ODBC Microsoft Access Driver]参数太少。预计2
此方法从newButton
动作侦听器调用。该数据库有4个表格(名称,地址,phoneNumbers和emailAddresses),它们都通过查询连接起来。
人员ID是每个表中的主键。我已经尝试了多种重新排列SQL语句的方法,而且什么都没有。我相当确定这是问题出在哪里。
public void newRecord()
{
try{
String driver = ("sun.jdbc.odbc.JdbcOdbcDriver");
Class.forName(driver).newInstance();;//connects to database
con = DriverManager.getConnection("jdbc:odbc:addressbook");
//load database driver class
Statement st = con.createStatement();
//connect to database
//gets the new value entered in the GUI and inputs it into the database
//write to database
st.executeUpdate("INSERT into names (personID, firstName, lastName) values(\"" + id + "\", \"" + firstName + "\" , \"" + lastName + "\")");
st.executeUpdate("INSERT into addresses (personID, address1, address2, city, state, zipcode) values (\"" + id + "\", \"" + address1 + "\", \"" + address2 + "\", \"" + city + "\", \"" + state + "\", \"" + zipcode + "\")");
st.executeUpdate("INSERT into phoneNumbers (personID, phoneNumber) values (\"" + id + "\", \"" + phoneNumber + "\")");
st.executeUpdate("INSERT into emailAddresses (personID, emailAddress) values (\"" + id + "\", \"" + emailAddress + "\")");
//close statement and connection
st.close();
con.close();
}//end try
//detect problems interacting with the database
catch (SQLException | ClassNotFoundException | InstantiationException | IllegalAccessException sqlException){
JOptionPane.showMessageDialog(null,
sqlException.getMessage(), "Database Error",
JOptionPane.ERROR_MESSAGE);
System.exit(1);
}
}//end Database NewPerson constructor
public void btnAction()
{
newButton.addActionListener(new ActionListener(){
public void actionPerformed(ActionEvent e)
{
personID++;
firstName = fNameText.getText();
lastName = lNameText.getText();
address1 = address1Text.getText();
address2 = address2Text.getText();
city = cityText.getText();
state = stateText.getText();
zipcode = zipText.getText();
phoneNumber = phoneText.getText();
emailAddress = emailText.getText();
id = Integer.toString(personID);
if(lastName.equals(""))
{
JOptionPane.showMessageDialog(null, "Please enter a persons name.");
}else
{
newRecord();
JOptionPane.showMessageDialog(null, "Record added.");
clearRecord();
}
}
});
}
答
考虑使用准备好的语句,以避免需要报价为整数/数字字段不应该用引号括起来。并检查personID
是否存储在Access表中作为字符串或整数或甚至自动编号。在Java代码中进行调整以匹配数据类型(忽略autoincremenet字段)。顺便说一下,MS Access可以使用单引号,而不仅仅是需要在Java中转义的双引号。
下面运行names
表追加的参数化查询的示例。完成其他查询。
Statement st = con.createStatement();
String insertSQL = "INSERT into [names] (personID, firstName, lastName) " +
"VALUES (?, ?, ?)";
// prepare statement with parameters
PreparedStatement preparedStatement = con.prepareStatement(insertSQL);
preparedStatement.setString(1, id);
preparedStatement.setString(2, firstName);
preparedStatement.setString(3, lastName);
// execute insert SQL stetement
preparedStatement.executeUpdate();
而且,我不完全了解你的应用程序,但你必须在变量作为参数传递给newRecord()
方法:
...
else {
newRecord(id, firstName, lastName, address1, address2, city, state,
zipcode, phoneNumber, emailAddress);
JOptionPane.showMessageDialog(null, "Record added.");
clearRecord();
}
...
然后在接收参数:
public void newRecord(String id, String firstName, String lastName, String address1,
String address2, String city, String state, String zipcode,
String phoneNumber, String emailAddress) {
...
使用变量保存查询,并检查预期查询是否与正在运行的查询相同。在变量中使用查询并在数据库中执行,并检查它是否成功完成..这将是开始的好方法,如果所有查询都可以运行并且仍然收到错误,请发布。 – Roy
什么是最有效的方法要做到这一点?我会用创建查询的SQL语句创建一个字符串,然后使用该字符串并运行它? –
是的,这是你应该尝试的,保存在一个字符串变量和输出字符串,并直接在数据库中运行查询,并查看它是否执行没有错误。 – Roy