The table access could be failing because the SELECT statement doesn't know the path to where the table is located. Do you currently send in the path? If not, then modify the trigger to include the path and see if that solves the problem.
>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
-----------------------------------------
Cathi Gero, CPA
Prenia Software & Consulting Services
Microsoft C# / .NET MVP
Mere Mortals for .NET MVP
cgero@prenia.comwww.prenia.comWeblog: blogs.prenia.com/cathi