SQL UPDATE returns 1 instead of -1 for non existing record. 2

[Kurjo]

I am using VFP9 and try to update SQL 2008 R2 table using SQLEXEC. In my program I use the below code to update the sql table;

lcSqlsting ="UPDATE [cdswms].[dbo].[ITEMS] SET [ORDID]='454879',[SEQ]='2',[QTYORD]= 3024,[QTYRES]= 3024,[QTYALL]= 3024,[QTYSHP]= 3024 WHERE SQLROWID = 14556"

lnRetValue = SQLEXEC(g_conHand,lcSqlString)

In my test I know SQLROWID = 14556 is not in my table and I am expecting lnRetValue -1, but it always returns 1 which indicates the update completed and no record is updated. Of course I can check that the record exist or not before I call the update command. My concern is that why it is not returning error.

Any help or suggestion is appreciated.

Thanks

 

[Olaf Doschke]

You have a misunderstanding on the erroring of SQLEXEC. An update sql, which is syntactically correct and can be executed does not error. In this case, if no record with SQLROWID=14556 exists, it just updates 0 records, but that's no error. lnRetValue is neither a _TALLY replcement nor an error number. Look in the help on SQLExec. If you want to provoke an error change it to SQLROWID='abc'. And then use AERROR() to get info about the error.

Bye, Olaf.

 

[Mike Lewis]

Kurjo,

In SQL Server, the usual way to test to see if your UPDATE has actually updated anything is to query @@ROWCOUNT. That will tell you the number of rows that were affected by the command. Note that you must do this in the same batch as the UPDATE (in other words, send it in the same SQLEXEC() call).

@@ROWCOUNT is specific to Microsoft SQL Server. If you are using a different back end, check the documentation for an equivalent.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Kurjo]

Hi Olaf and Mike - thanks for the response. I did not meant to say 'ERROR' when executing SQLEXEC, but I was under the impression that I should get -1 when there is nom rows are updated. Any way it was kind of misunderstanding. Anyway I applied Mike's suggestion using ROWCOUNT and it is working fine now.

Thanks a lot

Joe

 

[Olaf Doschke]

You only would get -1 as response of SQLEXEC in case of an error. That's why it's synonymous (to us), if you expect -1, you expect an error. 0 is for still eecuting queries in asynchronous mode and 1 for finished execution, which even thoug you have 0 updates is what is to be expected here.

Looking into the help myself theres also the possibility of >1 for multiple result sets.

The help explicitly says -1 is signaling a connection level error. Well, it's also the case if the connection is ok, and sql server reports an error in the sql syntax. What's always the case, when sqlexec returns -1 is, that AERROR() will give you additional info.

So in short: -1 error, 0 still executing, 1 finished >1 number of results, that's when SQLMORERESULTS() comes into play.
But -1 is not to be expected in your case

Also note 1 does not mean 1 resultset, it also is 1 in case of an insert or update without any result set. That's just because 0 already is reserverd for the "still executing" case.

The help is clear on the different result values and I don't see how you would expect -1 for an update with no updates.

Bye, Olaf.