Java 执行 PreparedStatement 时出现“jdbc.SQLServerException: Incorrect syntax near ','”错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21070894/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Get "jdbc.SQLServerException: Incorrect syntax near ','" error when exececute PreparedStatement
提问by Dexter Moregan
I wrote some java code to insert data into SQL Server 2012's Database when the user presses a button. When I run the code, I get this error:
当用户按下按钮时，我编写了一些 java 代码将数据插入到 SQL Server 2012 的数据库中。当我运行代码时，我收到此错误：
com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near ','.
com.microsoft.sqlserver.jdbc.SQLServerException: ',' 附近的语法不正确。
and it says that the sqlstatement.executeUpdate();line caused the error. I know that this line is not a problem. The problem is my sql query but I cannot find how my query is wrong. Would you please help me?
它说该sqlstatement.executeUpdate();行导致了错误。我知道这条线没有问题。问题是我的 sql 查询，但我找不到我的查询是如何错误的。你能帮我吗？
Here the code
这里的代码
count++;
for(int count = 0; count < table_1.getRowCount(); count++){
try { Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
Connection dbconbt8 = DriverManager.getConnection("" +"jdbc:sqlserver://localhost;databaseName=Store;user=sa;password=XXXXXX");
String sqlQ = "INSERT INTO [dbo].[Transaction]([TransactionID],[ProductID]"+
",[TotalPrice]) VALUES ("+count+"','"+table_1.getValueAt(count, 0).toString()+"','"+sumprice+ "') ";
PreparedStatement sqlstatement = dbconbt8.prepareStatement(sqlQ);
sqlstatement.executeUpdate();
sqlstatement.close();
dbconbt8.close();
} catch (SQLException e1) {
e1.printStackTrace();
} catch (ClassNotFoundException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
}
采纳答案by dasblinkenlight
You are missing a single quote after VALUES (- this should fix the problem:
您之后缺少单引号VALUES (- 这应该可以解决问题：
String sqlQ = "INSERT INTO [dbo].[Transaction]([TransactionID],[ProductID]"+
",[TotalPrice]) VALUES ('"+count+"','"+table_1.getValueAt(count, 0).toString()+"','"+sumprice+ "') ";
-- ^
-- Here
However, this is a bad fix: you should rewrite your query with parameters, so that the problem of quoting the data becomes irrelevant altogether:
然而，这是一个糟糕的解决方法：你应该用参数重写你的查询，这样引用数据的问题就完全无关紧要了：
String sqlQ = "INSERT INTO [dbo].[Transaction]([TransactionID],[ProductID],[TotalPrice]) VALUES (?,?,?) ";
PreparedStatement sqlstatement = dbconbt8.prepareStatement(sqlQ);
sqlstatement.setInt(1, count);
sqlstatement.setString(2, table_1.getValueAt(count, 0).toString());
sqlstatement.setInt(3, sumprice);
sqlstatement.executeUpdate();
