Visual Foxpro query execution error on Visual studio (Trigger failed in table)

Question

I am very new to C# and VFP(Visual fox pro). My first goal is to run and test a simple update query using query builder of MS visual studio. After adding a VFP database successfully(now I can preview data and execute select statements), I executed the following query and I am getting SQL Execution error where error message is: Trigger failed in Inventory.

UPDATE inventory SET location = 'test' WHERE inventoryid = 221

I dont understand what is wrong and Is there any better ways to learn and play with VFP database?

Update

Thanks to everyone. This database came up with a software. Since I was able to connect to the database and export some files after building an external app, I thought I could easily update few information too. Since now I have VFP and I dont have any source code of the whole software,

Is there any possibility that I might not find a way out to find or modify the trigger function and the stored procedure since it is part of a packaged software?

I just have the installed software and the VFP database inside it. The software talks to that VFP database.

Updated 2

I found the stored procedure in a FPT file under a different folder which is a location of an another system database. This is the code:

PROCEDURE InventoryUpdateTrigger(tcAlias,tcSource,tlHis)
    IF VARTYPE(glNoTrigger) = "L"
        IF glNoTrigger
            RETURN .t.
        ENDIF 
    ENDIF 

    IF VARTYPE(goApp) <> "O"
        RETURN .f.
    ENDIF 

    LOCAL laFieldList(1),laDataValues(1,3), laDataFields(1)
    LOCAL lcCriticalFields, lnFields, lcField, llRetVal,lnItemid,lnAlias,;
            lcPtNo, llBBHasChanged, lnField, lcNewVal, lcOldVal, lcSource, lnInventoryid

    lnAlias = SELECT(0)
    llRetVal = .t.

    lcCriticalFields = "ITEMID,ITEMNUMBER,MFGR,SERIALNUMBER,"+;
        "DESCRIPTION,WAREHOUSE,MACHINETYPE,QTY,UNITCOST,STATUS,RECEIVESTATUS,"+;
        "FREIGHTIN,CONDITIONCODE,DATERECEIVED,BREAKDOWNINVENTORYID,"+;
        "DATEALLOCATED,ALLOCATEDBY,DATEUNBOOKED,UNBOOKEDBY,LOCATION"

    llBBHasChanged = .f.

    BEGIN TRANSACTION 
    TRY 
        lnInventoryid = EVALUATE(tcAlias+".inventoryid")
        lnItemid = EVALUATE(tcAlias+".itemid")
        SELECT (tcAlias)
        lnDataFields = AFIELDS(laDataFields,tcAlias)
        DIMENSION laDataValues(lnDataFields,3)

        FOR lnField = 1 TO lnDataFields
            lcField = ALLTRIM(laDataFields(lnField,1))
            lcNewVal = ALLTRIM(TRANSFORM(EVALUATE(tcAlias+"."+lcField)))
            lcOldVal = ALLTRIM(TRANSFORM(OLDVAL(lcField,tcAlias)))
            laDataValues(lnField,1) = lcField
            laDataValues(lnField,2) = lcOldVal
            laDataValues(lnField,3) = lcNewVal
            IF !llBBHasChanged
                IF UPPER(lcField) $ UPPER(lcCriticalFields)
                    IF GETFLDSTATE(lcField,tcAlias) > 1
                        llBBHasChanged = .t.
                    ENDIF 
                ENDIF 
            ENDIF 
        ENDFOR 

        IF goapp.osystem.audit_inventory
            llRetVal = UpdateDatabaseAudit(IIF(tlHis,"INVENTORYHIS","INVENTORY"), lnInventoryid,;
                goapp.nUserid, AUDIT_UPDATE, tcSource, @laDataFields,@laDataValues)
        ENDIF 

        IF llRetVal
            LOCAL llUpdateBrokerBin
            llUpdateBrokerBin = .f.
            IF llBBHasChanged
                IF !tlHis
                    ** need to update brokerbin
                    IF GETFLDSTATE("STATUS",tcAlias) > 1 OR GETFLDSTATE("RECEIVESTATUS",tcAlias) > 1
                        lcOldReceive = OLDVAL("ReceiveStatus",tcAlias)
                        lcNewReceive = EVALUATE(tcAlias+".ReceiveStatus")
                        lcOldStatus = OLDVAL("Status",tcalias)
                        lcNewStatus = EVALUATE(tcAlias+".Status")
                        lnCase = 0
                        DO case
                            CASE lcOldReceive <> STATUS_RECEIVED ;
                                    AND lcNewReceive = STATUS_RECEIVED ;
                                    AND lcNewStatus = STATUS_OPEN
                                llUpdateBrokerBin = .t.
                                lnCase = 1
                            CASE lcOldStatus = STATUS_OPEN ;
                                    AND lcNewStatus = STATUS_ALLOCATED ;
                                    AND lcNewReceive = STATUS_RECEIVED
                                llUPdateBrokerBin = .t.
                                lnCase = 2
                            CASE lcOldReceive = STATUS_RECEIVED ;
                                    AND lcNewReceive <> STATUS_RECEIVED ;
                                    AND lcNewStatus = STATUS_OPEN
                                llUpdateBrokerBin = .t.
                                lnCase = 3
                            CASE lcOldStatus = STATUS_ALLOCATED ;
                                    AND lcNewStatus = STATUS_OPEN ;
                                    AND lcNewReceive = STATUS_RECEIVED
                                llUPdateBrokerBin = .t.
                                lnCase = 4
                            CASE lcOldStatus <> STATUS_BOOKED ;
                                    AND lcNewStatus = STATUS_BOOKED
                                llUpdateBrokerBin = .t.
                                lnCase = 5
                            CASE lcOldStatus = STATUS_BOOKED ;
                                    AND lcNewStatus <> STATUS_BOOKED
                                llUpdateBrokerBin = .t.
                                lnCase = 6
                        ENDCASE 
                    ELSE
                        lcReceive = EVALUATE(tcAlias+".ReceiveStatus")
                        lcStatus = EVALUATE(tcAlias+".Status")
                        IF lcReceive = STATUS_RECEIVED AND lcStatus = STATUS_OPEN
                            llUpdateBrokerBin = .t.
                        ENDIF 
                    ENDIF 

                    IF llUpdateBrokerBin        
                        llRetVal = setbrokerbintoprocess(lnItemid,lnInventoryid,0,tcAlias,"INVENTORY","UPDATE")
                    ENDIF 
                ENDIF 
            ENDIF 
        ENDIF 
    CATCH TO loException
        llRetVal = .f.
        RecordError(loException.ErrorNo, loException.LineNo, loException.Message,loException.Procedure)   
    FINALLY 
        IF llRetVal
            END TRANSACTION 
        ELSE
            ROLLBACK 
        ENDIF 
    ENDTRY

    SELECT (lnAlias)

    RETURN llRetVal
ENDPROC 

It leads to many questions which I think that is part of learning VFP first and it is not about simply running a SQL statement.

Answer 1

Do you even have VFP to work with?

If you do, run it. In the command window, type

CD "path to where the database is located"  [enter]
OPEN DATABASE NameOfYourDatabase [enter]
MODIFY DATABASE [enter]

This will bring up the database and show all the tables and whatever relationships between the tables in question. From there, right-click and popup menu will have "Stored Procedures". The code is embedded in the database container.

Also, for the specific inventory table, you can do a find within the opened database. Then right-click on it and click "Modify...". This will bring up the details about the table... Columns, sizes, indexes, and a "tab" page for the overall table showing what rules to apply and methods to call for insert, update and delete triggers.

This should get you started (provided you HAVE VFP to begin with).

FEEDBACK...

Since you have VFP9, once started, in the command window, type

CD ? [enter]

the "?" will ask you for a folder to change directory to. Pick the directory where your database is located.

Once there, then type

OPEN DATABASE ? [enter]

and it should ask which database to open... pick it.

then

MODIFY DATABASE [enter]

it will open a window showing all the tables and whatever relationships are in the database.

Right-click in the database in any open area and then click "Stored Procedures". This will bring up the code window for the stored procedures.

Now, with respect to the tables that have triggers, back in the modify database window, if you pick a table that you know has triggers (per your example), right-click on the table and modify. it will bring up a tab screen showing the fields, indexes and then table. The "Table" tab has the triggers and any rules for validation and insert/update/delete triggers.

Hopefully this will help you find your issue.

Answer 2

@Robi, If you don't have a need to work with existing VFP databases, then it is unlikely you would want to work with them. I suggest you to use one of the many other databases that have ODBC and/or OLEDB drivers (MS SQL Server, PostgreSQL, MariaDb, MySQL, Oracle, SQLite just to name a few). You can work with such databases using C# or VFP. With C# you not only have access to those with ODBC and/or OLEDB drivers but to almost any database (like the relatively newer NoSQL databases). This is just a suggestion. If you would anyway use VFP database from C#, keep in mind that you need to own VFP to do some tasks at database level, and not all commands are supported through VFPOLEDB driver. Also note that VFPOLEDB driver is 32 bits (don't forget to target x86 from C# IOW). Also the VFPOLEDB driver unfortunately doesn't behave the same way when you use it from C# or VFP.

Having said that, assuming you would anyway use a VFP database and want to debug this case, it is easier to do it from within VFP. For a seasoned developer there would be other ways, but I think this one is the easiest for a newcomer. In command window:

use ('inventory')[enter]
modify structure[enter]

(you may need to write fullpath to yourTable if it is not in search path, you don't need to set default to that folder)

This would bring up a tabbed dialog. Choose "Table" tab and check the procedure name next to "Update trigger". So now you know which procedure is called for update trigger. Copy the name from there and close that dialog.

Now type this:

Set Database to[spacebar]

as soon as you press spacebar VFP would show you a database list (likely with one element). Choose the one that this table belongs to and press [enter]. Then type:

Modify Procedure [enter]

Now you should see the code window with "Stored Procedure"s for that database. Locate the procedure that you got from update trigger (hint: in standart toolbar there is an icon with a document and magnifier - "Document View Window". If you bring it up and then click back to code window, you would see all the procedure names in that code file. Clicking the procedure name in doc. window would directly take you to the code location).

Now assuming you are good up to this point, and found the procedure that update trigger calls you need to find out why it is failing. To debug, just after "Procedure ..." line (and also after "lparameters ..." or "parameters ..." line if there is one) insert this line of code:

set step on

Press Ctrl+W to save. Now run your update command from command line:

UPDATE inventory SET location = 'test' WHERE inventoryid = 221

If would hit your "set step on" line and the debugger would pop up. Now you can do all sorts of debugging (after all these years and the glory of VS2013, I still seem to miss VFP's debugging capabilities compared to C# - most of which is discovered in advanced development).

I would bet "location" is coming from a lookup table, which someone created a referential integrity (missing 'test' in the lookup would cause the trigger to fail).