SQL Statement question - retrieve variable

[awarnica]

Thanks in advance, and I apologize if any of you feel this has been posted to the wrong forum, it was originally posted in the delphi forum.

Original post:
thread102-450082 am now working with a SQL statement and code as follows:

strSQL := 'UPDATE [WFSetup]' +
'SET [LastNo] = ' +
'(SELECT [LastNo] AS dLastNo ' +
'FROM [WFSetup]) + 1';

if odbcStmt.ExecDirect(strSQL) then
begin
if odbcStmt.Fetch then //<---
begin
dTemp := odbcStmt.GetDataAsDouble('dLastNo');

I error out on the fetch, the statement has executed so I cannot fetch as I would normally. If the fetch is removed I don't have access to the dLoastNo variable.

I am hoping there is a way to rewrite the SQL statement, any help in this would be appreciated, thanks.

Andrew

 

[JamesLean]

I don't know Delphi so I can't help you with that. The SQL statement looks like you are just increasing the value of the LastNo field by one? If so, use the following:

UPDATE wfsetup
SET lastno = lastno + 1

 

[awarnica]

Yes I had to retrieve the variable, then increment it, without stopping the transaction because we have multiple users over a slow network, I could not use two statements back to back and wasnt sure how to do that remoteely (through another application).

Thanks to all, I fooled around and found a SQL statement that selects and updates all in one:

strSQL := 'SELECT [LastNo] as dLastNo ' +
'FROM [WFSetup] ' +
'UPDATE [WFSetup] ' +
'SET [LastNo] = ' +
'(SELECT [LastNo] ' +
'FROM [WFSetup]) + 1';

if odbcStmt.ExecDirect(strSQL) then
begin
if odbcStmt.Fetch then
begin
dTemp := odbcStmt.GetDataAsDouble('dLastNo');


And even better than that I found it on Friday morning, so I don't have to ponder it all weekend!

I appreciate the help everybody.

Andrew