在INSERT INTO语句中出现MS Access语法错误

Question

我有一个" INSERT INTO语句语法错误"。这里是一个代码副本(代码在我使用GUI构建器创建的JFrame中，供参考):

private void jButtonSaveActionPerformed(java.awt.event.ActionEvent evt) {                                            
    String InputTeam1, InputTeam2;
    Integer InputTeam1Goals, InputTeam2Goals;
    FixtureNumber = Integer.parseInt(jLabel3.getText());
    InputTeam1 = jComboBox1.getSelectedItem() + "";
    InputTeam2 = jComboBox2.getSelectedItem() + "";
    InputTeam1Goals = Integer.parseInt(jTextField2.getText());
    InputTeam2Goals = Integer.parseInt(jTextField3.getText());
    try
    {
        String filename = "Database.mdb";
        String database = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=";
        database += filename.trim() + ";DriverID=22;READONLY=false";
        conn = DriverManager.getConnection(database, "", "");
        Statement sta = conn.createStatement();
      //Line below is the INSERT statement
        int s = sta.executeUpdate("INSERT INTO tblLeagueFixtures VALUES '" + InputTeam1 + "', '" + InputTeam2 + "', '" + InputTeam1Goals + "', '" + InputTeam2Goals + "' WHERE FixtureNumber = '"+ FixtureNumber +"'");
        System.out.println("Inserted into database.");
    }
    catch (Exception e3)
    {
        System.out.println("Exception ReadPlayerStats:" + e3);
    }
}

代码的目的是从用户获取数据(Team1等)，然后将其插入数据库。我试过改变撇号，把WHERE放在VALUES等之前，但结果是一样的。无论插入新数据还是更新现有数据，错误都会持续存在。

(解决)

int s = sta.executeUpdate("UPDATE tblLeagueFixtures SET [Team1] = '"+ InputTeam1 +"', [Team2] = '"+ InputTeam2 +"', [Team1Goals] = "+ InputTeam1Goals +", [Team2Goals] = "+ InputTeam2Goals +" WHERE [FixtureNumber] = "+ FixtureNumber);

Answer 1

您的INSERT命令中至少有两个问题。

1) INSERT INTO VALUES语句中不能使用WHERE子句

2)在INSERT INTO VALUES语句中，值必须出现在括号中。

int s = sta.executeUpdate("INSERT INTO tblLeagueFixtures VALUES ('" + InputTeam1 + "', '" + InputTeam2 + "', '" + InputTeam1Goals + "', '" + InputTeam2Goals + "')");

更新数据必须使用正确的语法。