Hi All,

I'm trying to use ADO.NET (and vb.net) to update a Visual FoxPro Database. Everything works ok until I try to update a table with an update or delete trigger (I imagine I would have the same problem with an insert trigger). After playing around with it a little bit, I found that if there is any table access in the trigger it will cause the trigger to fail. Other statements seem to work fine. I was wondering if anyone else has found this to be true. I'm using the FoxPro OLE-DB provider and I connect using the following string :

cConnectString = "Provider=vfpoledb.1;" & _
"Data Source=C:\bob\row.dbc;" & _
"Exclusive=false;Nulls=false"

The trigger code is in the stored procedures of the dbc and is as follows:

**************************************************************
FUNCTION KeyExists as Boolean
LPARAMETERS cTable, cKey, nKeyValue, cOtherCriteria
LOCAL cCriteria, cKeyValue, nCnt
LOCAL lExists as Boolean

cKeyValue = LTRIM(STR(nKeyValue))
cCriteria = "WHERE " + cKey + " = " + cKeyValue + " " + ;
IIF(EMPTY(cOtherCriteria), "", " AND " + ALLTRIM(cOtherCriteria)) +;
" AND .T."
SELECT &cKey. FROM (cTable) &cCriteria. INTO CURSOR tDBKey

nCnt = RECCOUNT("tDBKEY")
nCnt = 0
IF nCnt > 0
lExists = .T.
ELSE
lExists = .F.
ENDIF
USE IN tDBKey
RETURN lExists
ENDFUNC

***************************************************************

FUNCTION DeleteTrigger as Boolean

LPARAMETERS cTable

PUBLIC gDBErrorTable, gDBErrorIdentifier, gDBErrorType
gDBErrorTable = ""
gDBErrorIdentifier = ""
gDBErrorType = ""

DO CASE
CASE cTable == "Status"
IF KeyExists("Folder", "Status_Id", Status.Status_Id)
gDBErrorIdentifier = "FK_FOLDER_STATUS_ID"
gDBErrorType = "RI_DELETE"
RETURN .F.
ENDIF
ENDCASE

RETURN .T.
ENDFUNC
***************************************************************

The triggers work fine in VFP6 and 7 when called from FoxPro using (for example) DELETE FROM Status WHERE Status_Id = 17.

Status_Id is an integer.

I can email a small copy of the database and the VB.Net solution files that will reproduce the problem if necessary.

If you need any more info I can try to supply it.

Thanks for any suggestions!

Bob