Update SQL Server

 Hello,

I've the Pro Version 9.5.1 on Windows 10 and use MS SQL Server 2008 R2 Express.

I try to update data with inline edit, but after saving I get the Error:

Exception: Incorrect syntax near the keyword 'where'.

Here the generated update query:

UPDATE dbo.ASS_JISFixdata
SET LastRackId = 16263
WHERE ID = 1

I can't find any error.

The template is default:

UPDATE $$schema$$$$schemaseparator$$$$table$$
SET $$column-values$$
WHERE $$where-columns$$

When I copy the query and run it manually it works fine.

Whats wrong here?

Here the create table query:

CREATE TABLE ASS_JISFixdata
(ID INT NOT NULL IDENTITY,
MaxRacksTruck INT NOT NULL,
MaxRacksLine1 INT NOT NULL,
MaxRacksLine2 INT NOT NULL,
FreeSpaceLimit INT,
TaktL1 INT,
TaktL2 INT,
NextRackId INT,
LastRackId INT,
OfflineMode BIT,
OfflineModeMinutes INT,
CONSTRAINT PK_ASS_JISFixdata PRIMARY KEY (ID));

I would like to use this feature.

I hope you can help.

Thanks and BR,

Erik

Hi Erik,

Please open Tools->Debug Window and enable debugging for DbVisualizer, and click the garbage can button to remove old log output. Perform the Save again and include the debug output in your reply. 

Best Regards,

Hans

That is Log while after press the save button:

2016-10-10 11:45:11.983 FINE  529 [ExecutorRunner-pool-3-thread-3 - P.?] DbConnection='MEZMS02VISUALEXP' Catalog='MEZAuswertungDBTest' Schema='dbo' Table='ASS_JISFixdata' Where='"ID" = ${ID (where)||1||Integer||where pk unformatted ds=11 dt=INTEGER}$'
2016-10-10 11:45:11.983 FINE  529 [ExecutorRunner-pool-3-thread-3 - J.?] Executing...
2016-10-10 11:45:11.984 FINE  529 [pool-4-thread-4 - C.?] DefaultEditor24: JtdsConnection.prepareStatement("SET TRANSACTION ISOLATION LEVEL SNAPSHOT
BEGIN TRAN

SELECT count_big(*)
FROM "dbo"."ASS_JISFixdata"

COMMIT TRAN where "ID" = ?")
2016-10-10 11:45:11.984 FINE  529 [ExecutorRunner-pool-3-thread-3 - Q.?] index=[1] name=[ID (where)] value=[1] type=[Integer] options=[where pk unformatted ds=11 dt=INTEGER]
2016-10-10 11:45:11.984 FINE  529 [ExecutorRunner-pool-3-thread-3 - B.?] DefaultEditor24: JtdsPreparedStatement.setInt(1, 1)
2016-10-10 11:45:11.984 FINE  529 [pool-4-thread-4 - B.?] DefaultEditor24: JtdsPreparedStatement.execute()
2016-10-10 11:45:11.995 FINE  529 [pool-4-thread-4 - B.?] DefaultEditor24: EXCEPTION -> java.sql.SQLException: Incorrect syntax near the keyword 'where'.
java.sql.SQLException: Incorrect syntax near the keyword 'where'.
   at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:372)
   at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2988)
   at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2421)
   at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:671)
   at net.sourceforge.jtds.jdbc.JtdsStatement.processResults(JtdsStatement.java:613)
   at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQL(JtdsStatement.java:572)
   at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.execute(JtdsPreparedStatement.java:784)
   at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
   at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
   at java.lang.reflect.Method.invoke(Unknown Source)
   at com.onseven.dbvis.g.B.B.?(Z:2392)
   at com.onseven.dbvis.g.B.F$A.call(Z:1369)
   at java.util.concurrent.FutureTask.run(Unknown Source)
   at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
   at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
   at java.lang.Thread.run(Unknown Source)
2016-10-10 11:45:11.996 FINE  529 [ExecutorRunner-pool-3-thread-3 - _.?] Creating execution result for failed execution

Here you see, the function call the "SELECT count_big(*) ..." query. I don't know why.
The problem is, that I changed the select querys to snapshot transactions and this blocks the update.
When I remove the snapshot transaction it works fine.

Thanks for the hint with Debug Window.

BR, Erik

 

Hi Erik,

So everything is fine now? If you edit the SQL templates you need to be very careful.

Best Regards,

Hans