Hi All,
I've an ASP using ODBC to a VFP6 DBC, and we want to change from the SQL Update... syntax (which works) to using AddNew and Update (which doesn't always work).

The ASP gens the following error when trying to do rs.update or updatebatch.
Only happens about 1 out of 3 saves.

Error -2147217864: Row cannot be located for updating. Some values may have been changed since it was last read.

When Refreshed, update goes through OK.

From searching UT, I've tried using clientside cursor, putting in extra column, using adLockBatchOptimistic, rs.updateBatch, adOpenKeyset or adOpenStatic type, etc.

Here is a section of the script:

IF ztask="XEDIT" THEN
set rs = nothing
set conn = Server.CreateObject("ADODB.Connection")
conn.Open session("session_dsn")

sql = "SET ANSI OFF"
conn.Execute sql

set rs = server.CreateObject("ADODB.Recordset")
rs.cursortype = 3
rs.cursorlocation = 3
rs.locktype = 4

'*** Select Staff record ***
sql = "SELECT *,sname as test from staff WHERE not deleted() "
sql=sql & " and substr(ltrim(upper(suserid))+space(8),1,8) == '" & trim(zkey) & string(8-len(zkey)," ") & "'"

rs.Open sql, conn

rs.fields("sname").value=Trim(Request.Form("txtsname"))
rs.fields("saccess").value=Trim(Request.Form("cmbsaccess"))
rs.fields("sagency").value=Trim(Request.Form("txtsagency"))
IF UCASE(TRIM(Request.Form("chksall")))="TRUE" THEN
rs.fields("sall").value="True"
ELSE
rs.fields("sall").value="False"
END IF
IF UCASE(TRIM(Request.Form("chkssite")))="TRUE" THEN
rs.fields("ssite").value="1"
ELSE
rs.fields("ssite").value="0"
END IF
IF UCASE(TRIM(Request.Form("chksxport")))="TRUE" THEN
rs.fields("sxport").value="1"
ELSE
rs.fields("sxport").value="0"
END IF
rs.fields("slastid").value=session("session_userid")
rs.fields("slastdt").value=now()

on error resume next
rs.updatebatch
HandleError("rs.update failed")
on error goto 0

rs.close
set rs = nothing
conn.close
set conn = nothing
END IF

I'm using PWS on NT4.0 workstation for development. I tried it again on the host website (IIS on NT4.0 server) and I can't get it to fail (I thought it failed earlier when we tried it on the host). It also works without fail on a laptop we have here (using PWS), but fails randomly on another workstation using PWS. I'm wondering how much confidence we can have in the host website that appears to be working? Is PWS and running locally causing the problem? Does anyone see a problem in the script?

This problem has everyone on the team stumped. Hope someone can see what I'm missing. TIA