Get "jdbc.SQLServerException: Incorrect syntax near ','" error when exececute PreparedStatement
46,384
You are missing a single quote after VALUES (
- this should fix the problem:
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();
Author by
Dexter Moregan
Updated on July 21, 2022Comments
-
Dexter Moregan almost 2 years
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:
com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near ','.
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?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(); } }
-
Elliott Frisch over 10 yearsSo it was (almost) valid T-SQL?
-
Sergey Kalinichenko over 10 years@ElliottFrisch Apart from being open to SQL injection attacks, yes. T-SQL uses square brackets as "quotation marks" for names.