How to read single record with BOI (C#)

SOLVED

Hello All,

Can anyone help me in getting a single record from Sage BOI script. I am using the following code but no data is returned.

int taskId = (int)oSs.InvokeMethod("nLookupTask", "AR_Customer_ui");
oSs.InvokeMethod("nSetProgram", taskId);

using (var arCustSvc = new DispatchObject(pvx.InvokeMethod("NewObject", "AR_Customer_svc", oSs.GetObject())))
{
    arCustSvc.InvokeMethod("nSetKeyValue", "ARDivisionNo$", "00");
    arCustSvc.InvokeMethod("nSetKeyValue", "CustomerNo$", "0000007");

    int returnValue = (int)arCustSvc.InvokeMethod("nFind");
    if (returnValue > 0)
    {
        int retVal = 0;

        string str1 = "", str2 = "";
        retVal = (int)arCustSvc.InvokeMethod("nGetRecord", str1, str2);
    }
}

here retVal is 1, but str1 and str2 is empty.

Is there anything i am doing wrong here?

Parents
  • FormerMember
    0 FormerMember

    I gave your C# program a try in ScriptBasic.

    IMPORT com.sbi
    
    oscript = COM::CREATE(:SET, "ProvideX.Script")
    COM::CBN(oscript, "Init", :CALL, "C:\\Sage\\Sage 100 Standard\\MAS90\\Home")
    osession = COM::CBN(oscript, "NewObject", :SET, "SY_Session")
    COM::CBN(osession, "nSetUser", :CALL, "UserID", "Password")
    COM::CBN(osession, "nsetcompany", :CALL, "ABC")
    COM::CBN(osession, "nSetDate", :CALL, "G/L", "20211124")
    
    COM::CBN(osession, "nSetProgram", :CALL, COM::CBN(osession, "nLookupTask", :CALL, "GL_Account_ui"))
    oacct = COM::CBN(oscript, "NewObject", :SET, "GL_Account_svc", osession)
    COM::CBN(osession, "nSetProgram", :CALL, COM::CBN(osession, "nLookupTask", :CALL, "GL_Allocation_ui"))
    oalloc = COM::CBN(oscript, "NewObject", :SET, "GL_Allocation_svc", osession)
    
    FOR i = 0 TO 4
      COM::CBN(oacct, "nMoveNext", :CALL)
      AccountKey = COM::CBN(oacct, "sAccountKey", :GET)
      Account = COM::CBN(oacct, "sAccount", :GET)
      AccountDesc = COM::CBN(oacct, "sAccountDesc", :GET)
      AccountValue = 0
      COM::CBN(oalloc, "nGetAccountBalance", :CALL, AccountKey, "2025", "05", AccountValue)
      PRINT Account & " - " & AccountDesc & ": " & FORMAT("%~###,##0.00-~" & AccountValue) & "\n"
    NEXT
    
    COM::CBN(oalloc, "DropObject", :CALL)
    COM::CBN(oacct, "DropObject", :CALL)
    COM::CBN(osession, "nCleanup", :CALL)
    COM::CBN(osession, "DropObject", :CALL)
    COM::RELEASE(oscript)

    C:\OpenSage\test>sbc glbal.sb
    100-00-00 - Cash on hand:   1,408.00
    100-00-A - Cash in Trust Fund:       0.00
    101-01-00 - Cash in bank - Reg. checking: 191,565.00-
    101-02-00 - Cash in bank - payroll:  16,154.00
    101-03-00 - Cash in bank - savings: 497,324.00

  • FormerMember
    0 FormerMember in reply to FormerMember

    I updated to ScriptBasic example to remove the unneeded SetProgram / LookupTask calls. The example displays all the accounts and their balances.

    IMPORT com.sbi
    
    oscript = COM::CREATE(:SET, "ProvideX.Script")
    COM::CBN(oscript, "Init", :CALL, "C:\\Sage\\Sage 100 Standard\\MAS90\\Home")
    osession = COM::CBN(oscript, "NewObject", :SET, "SY_Session")
    COM::CBN(osession, "nSetUser", :CALL, "UserID", "Password")
    COM::CBN(osession, "nsetcompany", :CALL, "ABC")
    COM::CBN(osession, "nSetDate", :CALL, "G/L", "20211124")
    
    oacct = COM::CBN(oscript, "NewObject", :SET, "GL_Account_svc", osession)
    oalloc = COM::CBN(oscript, "NewObject", :SET, "GL_Allocation_svc", osession)
    
    COM::CBN(oacct, "nMoveFirst", :CALL)
    DO UNTIL COM::CBN(oacct, "nEOF", :GET)
      AccountKey = COM::CBN(oacct, "sAccountKey", :GET)
      Account = COM::CBN(oacct, "sAccount", :GET)
      AccountDesc = COM::CBN(oacct, "sAccountDesc", :GET)
      AccountValue = 0
      COM::CBN(oalloc, "nGetAccountBalance", :CALL, AccountKey, "2025", "05", AccountValue)
      PRINT Account & " - " & AccountDesc & STRING(40 - LEN(AccountDesc), " ") & FORMAT("%~$#,###,##0.00-~", AccountValue) & "\n"
      COM::CBN(oacct, "nMoveNext", :CALL)
    LOOP
    
    COM::CBN(oalloc, "DropObject", :CALL)
    COM::CBN(oacct, "DropObject", :CALL)
    COM::CBN(osession, "nCleanup", :CALL)
    COM::CBN(osession, "DropObject", :CALL)
    COM::RELEASE(oscript)

    C:\OpenSage\test>sbc glbal.sb
    100-00-00 - Cash on hand                            $    1,408.00
    100-00-A - Cash in Trust Fund                      $        0.00
    101-01-00 - Cash in bank - Reg. checking            $  191,565.00-
    101-02-00 - Cash in bank - payroll                  $   16,154.00
    101-03-00 - Cash in bank - savings                  $  497,324.00
    105-00-01 - Accts. receiv. - East Warehse           $  548,651.00
    105-00-02 - Accts. receiv. - West Warehse           $  145,481.00
    110-01-00 - Note receivable                         $  203,236.00
    111-00-00 - Other Receivables                       $        0.00
    115-00-01 - Inventory - East Warehouse              $   96,029.00-
    115-00-02 - Inventory - West Warehouse              $  109,577.00
    115-00-03 - Inventory - Central Warehouse           $  448,267.00
    115-01-00 - Inventory-Miscellaneous                 $        0.00
    116-00-00 - Inventory-Scrap                         $       34.00
    117-00-00 - Inventory-Repairs in Process            $        0.00
    118-00-00 - Inventory-Repairs Clearing              $        0.00
    120-00-00 - Prepaid insurance                       $   30,000.00-
    125-00-00 - Prepaid advertising                     $    3,050.00
    130-00-00 - Prepaid income taxes                    $   27,750.00
    150-00-00 - Land                                    $   95,000.00
    155-00-00 - Buildings                               $  282,500.00
    155-01-00 - Accum. depr. - buildings                $   44,305.00-
    160-00-00 - Furniture                               $   23,680.00
    160-01-00 - Accum. depr. - furniture                $    7,456.00-
    165-00-00 - Office and computer equipment           $   44,800.00
    165-01-00 - Accum. depr. - off & cmp equip          $   20,541.00-
    170-00-00 - Warehouse equipment                     $   17,530.00
    170-01-00 - Accum. depr. - warehse equip            $    5,959.00-
    175-00-00 - Trucks                                  $   45,600.00
    175-01-00 - Accum. depr. - trucks                   $   36,734.00-
    180-00-00 - Rent deposits                           $    2,000.00
    185-00-00 - Workmans' comp. deposit                 $      500.00
    195-00-00 - Software costs (net)                    $    4,844.00
    200-01-00 - Accounts payable - trade                $  130,602.00-
    200-02-00 - Accounts payable - other                $    5,484.00
    200-03-00 - Purchases clearing account              $  174,980.00-
    201-00-00 - Customer Deposits                       $        0.00
    205-00-00 - Notes payable                           $   13,226.00-
    210-00-00 - Accrued payroll                         $        0.00
    220-00-00 - Other accrued expenses                  $        0.00
    225-01-00 - F.I.C.A. taxes payable                  $   50,543.00-
    225-02-00 - Federal taxes withheld                  $   37,501.00-
    225-03-00 - State tax withheld                      $   13,672.00-
    225-04-00 - City tax withheld                       $    1,243.00-
    230-01-00 - Accrued federal employment              $    7,901.00-
    230-02-00 - Accrued state unemployment              $    5,562.00-
    230-03-00 - Accrued workmans' comp                  $    1,085.00-
    235-01-00 - Sales tax payable - East Cnty           $   33,348.00-
    235-02-00 - Sales tax payable - West Cnty           $   20,131.00-
    245-00-00 - Withheld credit union                   $      900.00-
    250-00-00 - Current portion of l-t debt             $   20,000.00-
    255-00-00 - Income taxes payable                    $   67,050.00-
    275-01-00 - Loan payable to Bank 1                  $   36,666.00-
    275-02-00 - Loan payable to Bank 2                  $    6,666.00
    280-00-00 - Loan payable to shareholders            $   90,000.00-
    300-00-00 - Common stock                            $    2,000.00-
    310-00-00 - Paid-In capital                         $    8,000.00-
    320-00-00 - Retained earnings                       $1,105,531.00-
    400-01-00 - Distribution sales (history)            $  291,142.00-
    400-01-01 - Distribution sales - East               $1,182,772.00-
    400-01-02 - Distribution sales - West               $  611,389.00-
    400-02-00 - Service fees                            $    3,000.00-
    400-02-01 - Service fees - East                     $   44,362.00-
    400-02-02 - Service fees - West                     $   20,853.00-
    400-03-00 - Freight charges                         $        0.00
    400-03-01 - Freight charges - East                  $   24,752.00-
    400-03-02 - Freight charges - West                  $   12,487.00-
    425-00-00 - Returns & allowances                    $       81.00
    425-00-01 - Returns & allowances - East             $   19,017.00
    425-00-02 - Returns & allowances - West             $    6,538.00
    450-01-00 - Purchases                               $  163,177.00
    450-01-01 - Purchases - East                        $  308,322.00
    450-01-02 - Purchases - West                        $  294,984.00
    450-02-00 - Freight                                 $      555.00
    450-02-01 - Freight - East                          $   13,324.00
    450-02-02 - Freight - West                          $    5,927.00
    450-03-00 - Warehouseman payroll                    $        0.00
    450-03-01 - Warehouseman payroll - East             $  157,836.00
    450-03-02 - Warehouseman payroll - West             $   37,100.00
    450-04-00 - Serviceman payroll                      $        0.00
    450-04-01 - Serviceman payroll - East               $    9,646.00
    450-04-02 - Serviceman payroll - West               $    5,489.00
    450-10-00 - Cost of Goods Sold                      $      945.00
    500-00-00 - Other expenses (history)                $    2,573.00
    500-00-03 - Warehouse payroll                       $    6,930.00
    505-00-03 - Clerical salaries                       $    6,545.00
    507-00-03 - Sick pay                                $      195.00
    508-00-03 - Holiday pay                             $      263.00
    509-00-03 - Vacation pay                            $        0.00
    510-00-03 - Payroll taxes                           $   18,905.00
    515-00-03 - Building maintenance                    $    9,192.00
    518-00-00 - Accrued Credit Card Expense             $        0.00
    520-00-03 - Depreciation expense                    $    2,453.00
    525-00-03 - Equipment maintenance                   $    1,872.00
    530-00-03 - Insurance expense                       $      480.00
    530-10-03 - Insurance expense: SA&MK-CENTR          $      568.00
    530-20-03 - Insurance expense: ACCTG-CENTR          $      284.00
    530-30-03 - Insurance expense: CSERV-CENTR          $      426.00
    530-60-03 - Insurance expense: SH&RC-CENTR          $      640.00
    535-00-03 - Warehouse supplies                      $    3,406.00
    540-00-03 - Telephone expense                       $    1,154.00
    540-10-03 - Telephone expense: SA&MK-CENTR          $      936.00
    540-20-03 - Telephone expense: ACCTG-CENTR          $      312.00
    540-30-03 - Telephone expense: CSERV-CENTR          $      936.00
    540-60-03 - Telephone expense: SH&RC-CENTR          $      624.00
    545-00-03 - Utilities                               $    2,715.00
    545-10-03 - Utilities: SA&MK-CENTR                  $      288.00
    545-20-03 - Utilities: ACCTG-CENTR                  $       96.00
    545-30-03 - Utilities: CSERV-CENTR                  $      288.00
    545-60-03 - Utilities: SH&RC-CENTR                  $      192.00
    555-00-03 - Rent                                    $   11,077.00
    555-10-03 - Rent: SA&MK-CENTR                       $    9,186.00
    555-20-03 - Rent: ACCTG-CENTR                       $    1,941.00
    555-30-03 - Rent: CSERV-CENTR                       $    5,824.00
    555-60-03 - Rent: SH&RC-CENTR                       $    5,320.00
    560-00-03 - Truck expenses                          $    9,922.00
    560-10-01 - Truck expenses: SA&MK-EAST              $        0.00
    560-10-02 - Truck expenses: SA&MK-WEST              $        0.00
    560-10-03 - Truck expenses: SA&MK-CENTR             $        0.00
    560-20-01 - Truck expenses: ACCTG-EAST              $        0.00
    560-20-02 - Truck expenses: ACCTG-WEST              $        0.00
    560-20-03 - Truck expenses: ACCTG-CENTR             $        0.00
    560-30-01 - Truck expenses: CSERV-EAST              $        0.00
    560-30-02 - Truck expenses: CSERV-WEST              $        0.00
    560-30-03 - Truck expenses: CSERV-CENTR             $        0.00
    560-40-01 - Truck expenses: HR-EAST                 $        0.00
    560-40-02 - Truck expenses: HR-WEST                 $        0.00
    560-40-03 - Truck expenses: HR-CENTR                $        0.00
    560-50-01 - Truck expenses: ENGR-EAST               $        0.00
    560-50-02 - Truck expenses: ENGR-WEST               $        0.00
    560-50-03 - Truck expenses: ENGR-CENTR              $        0.00
    560-60-01 - Truck expenses: SH&RC-EAST              $        0.00
    560-60-02 - Truck expenses: SH&RC-WEST              $        0.00
    560-60-03 - Truck expenses: SH&RC-CENTR             $        0.00
    565-00-03 - Miscellaneous expense                   $      688.00
    600-01-01 - Driver payroll - East                   $  132,035.00
    600-01-02 - Driver payroll - West                   $   49,882.00
    605-01-00 - Clerical salaries                       $        0.00
    605-01-01 - Clerical salaries - East                $   28,263.00
    605-01-02 - Clerical salaries - West                $   25,781.00
    607-01-00 - Sick pay                                $        0.00
    607-01-01 - Sick pay - East                         $    1,008.00
    607-01-02 - Sick pay - West                         $        0.00
    608-01-00 - Holiday pay                             $        0.00
    608-01-01 - Holiday pay - East                      $    5,884.00
    608-01-02 - Holiday pay - West                      $    2,214.00
    609-01-00 - Vacation pay                            $        0.00
    609-01-01 - Vacation pay - East                     $      854.00
    609-01-02 - Vacation pay - West                     $      540.00
    610-01-00 - Payroll taxes                           $        0.00
    610-01-01 - Payroll taxes - East                    $   66,271.00
    610-01-02 - Payroll taxes - West                    $   18,027.00
    620-01-00 - Depreciation expense                    $        0.00
    620-01-01 - Depreciation expense - East             $    4,564.00
    620-01-02 - Depreciation expense - West             $      342.00
    625-01-00 - Equipment maintenance                   $        0.00
    625-01-01 - Equipment maintenance - East            $    7,138.00
    625-01-02 - Equipment maintenance - West            $    6,671.00
    630-01-00 - Insurance                               $        0.00
    630-01-01 - Insurance - East                        $   15,947.00
    630-01-02 - Insurance - West                        $    6,526.00
    635-01-00 - Warehouse supplies                      $        0.00
    635-01-01 - Warehouse supplies - East               $   18,853.00
    635-01-02 - Warehouse supplies - West               $    8,369.00
    640-01-00 - Telephone expense                       $        0.00
    640-01-01 - Telephone expense - East                $   25,884.00
    640-01-02 - Telephone expense - West                $   19,416.00
    645-01-00 - Utilities                               $        0.00
    645-01-01 - Utilities - East                        $   21,910.00
    645-01-02 - Utilities - West                        $    8,975.00
    660-01-00 - Truck expenses                          $      375.00
    660-01-01 - Truck expenses - East                   $   17,399.00
    660-01-02 - Truck expenses - West                   $    7,674.00
    665-01-00 - Miscellaneous expense                   $       79.00
    665-01-01 - Miscellaneous expense - East            $    6,186.00
    665-01-02 - Miscellaneous expense - West            $    2,686.00
    700-00-04 - Officer salaries                        $   74,562.00
    705-00-04 - Clerical salaries                       $   54,391.00
    706-00-04 - Bonus pay                               $        0.00
    707-00-04 - Sick pay                                $      184.00
    708-00-04 - Holiday pay                             $      909.00
    709-00-04 - Vacation pay                            $      854.00
    710-00-04 - Payroll taxes                           $   43,081.00
    715-00-04 - Hospitalization                         $   13,812.00
    720-00-04 - Depreciation                            $   11,980.00
    722-00-04 - Amortization                            $      297.00
    725-00-04 - Professional fees                       $   10,606.00
    730-00-04 - Insurance expense                       $   12,088.00
    735-00-04 - Advertising expenses                    $    7,971.00
    740-00-04 - Telephone expenses                      $    9,638.00
    745-00-04 - Utilities                               $    6,356.00
    750-00-04 - Interest expense                        $    3,410.00
    755-00-04 - Travel and entertainment                $   22,792.00
    760-00-04 - Office supplies                         $    6,507.00
    765-00-04 - Miscellaneous expense                   $      522.00
    770-00-04 - Postage & other freight                 $    4,523.00
    950-01-04 - Discounts earned                        $    8,260.00-
    950-02-04 - Discounts allowed                       $    2,391.00
    955-00-04 - Interest income                         $   35,242.00-
    960-00-04 - Miscellaneous                           $   35,636.00-
    990-00-00 - Provision for income taxes              $   67,050.00
    105-00-00 - Accts. receiv.                          $        0.00
    235-00-00 - Sales tax payable                       $        0.00
    950-03-04 - Nonrecoverable                          $        0.00
    240-01-00 - Use tax - East Cnty                     $        0.00
    240-00-00 - Use tax                                 $        0.00
    240-02-00 - Use tax - West Cnty                     $        0.00
    200-00-00 - Accounts payable                        $        0.00
    
    C:\OpenSage\test>

  • 0 in reply to FormerMember

    SetProgram and LookupTask are not unneeded.  LookupTask returns the task id that SetProgram expects based on the class you pass to it.  SetProgram does several things, it sets the program name you see in Master Console for the process' session and it checks security to make sure the currently set user has appropriate role permissions assigned to access a task.  If you are always using a user with an "admin" role then you won't see the impact but for user defined scripts that operate in multi-company and multi-user environments, it is important to check to avoid potential hard errors.

  • 0 in reply to David Speck

    I've also found that the value returned form SetProgram will return 0 when the user is not authorized, but the program will continue and return data even though the user is not authorized.

    For me, I've determined the best practice is to test the returned value from nSetUser, nSetDate, and nSetProgram. If the returned value is 0, then throw an exception to indicate the user is not authorized to prevent the execution of later code.

  • FormerMember
    0 FormerMember in reply to David Speck

    Thanks David for the reminder of role permissions. Running as admin gives you super powers not everyone has.

  • FormerMember
    0 FormerMember in reply to David Speck

    David,

    I removed the SetUser call and was able to list the G/L account data. This is totally anonymous access.

  • 0 in reply to FormerMember

    If you have unified authentication enabled, it uses the session's Windows account for access.

  • FormerMember
    0 FormerMember in reply to Kevin M

    Kevin,

    Sage 100 isn't even running. I have no Windows unified login set up. This is a simple ScriptBasic external BOI example.

    No one should get upset as any ProvideX programmer could do the same in BASIC.

    It's the BOI programmer's responsibility to maintain user security with their enhancements.

  • 0 in reply to FormerMember

    In a simplified VBScript using ProvideX.Script to create a new session using SY_Session, I have observed the following.

    Without SetUser but with SetProgram, trying to get the object handle fails because SetProgram fails.

    Without SetUser and without SetProgram, both NewObject and GetObject fail to return an object handle to GL_Account_Svc.

    So I don't know what is going on with your environment or code but it isn't normal.

  • FormerMember
    0 FormerMember in reply to David Speck

    David,

    This is the ScriptBasic external BOI program I'm using, Try to do the same externally in the language of your choice.

    IMPORT com.sbi
    
    oscript = COM::CREATE(:SET, "ProvideX.Script")
    COM::CBN(oscript, "Init", :CALL, "C:\\Sage\\Sage 100 Standard\\MAS90\\Home")
    osession = COM::CBN(oscript, "NewObject", :SET, "SY_Session")
    COM::CBN(osession, "nsetcompany", :CALL, "ABC")
    COM::CBN(osession, "nSetDate", :CALL, "G/L", "20211124")
    
    oacct = COM::CBN(oscript, "NewObject", :SET, "GL_Account_svc", osession)
    oalloc = COM::CBN(oscript, "NewObject", :SET, "GL_Allocation_svc", osession)
    
    COM::CBN(oacct, "nMoveFirst", :CALL)
    DO UNTIL COM::CBN(oacct, "nEOF", :GET)
      AccountKey = COM::CBN(oacct, "sAccountKey", :GET)
      Account = COM::CBN(oacct, "sAccount", :GET)
      AccountDesc = COM::CBN(oacct, "sAccountDesc", :GET)
      AccountValue = 0
      COM::CBN(oalloc, "nGetAccountBalance", :CALL, AccountKey, "2025", "05", AccountValue)
      PRINT Account & " - " & AccountDesc & STRING(40 - LEN(AccountDesc), " ") & FORMAT("%~$#,###,##0.00-~", AccountValue) & "\n"
      COM::CBN(oacct, "nMoveNext", :CALL)
    LOOP
    
    COM::CBN(oalloc, "DropObject", :CALL)
    COM::CBN(oacct, "DropObject", :CALL)
    COM::CBN(osession, "nCleanup", :CALL)
    COM::CBN(osession, "DropObject", :CALL)
    COM::RELEASE(oscript)

  • 0 in reply to FormerMember

    Found the difference.  Although my code also included SetModule.  The code that made the difference is the fact that you are using the ProvideX.Script object's NewObject whereas I was using the SY_Session object's NewObject method.  

    Personally, I always use SY_Session's method because it usually does a good job of reporting errors in LastErrorMsg for the reason a method failed, you don't get this when using ProvideX.Script.  

    Certain methods may appear to work when using the ProvideX.Script method but I would imagine it will produce unexpected behavior when a class is expecting certain variables and/or properties to be set that are normally handled when using SetUser, SetModule, and SetProgram.  I know I have seen a few cases first hand where if the module and program are not set the created class produces all kinds of errors when calling methods because it expects certain things to be set.

  • FormerMember
    0 FormerMember in reply to David Speck

    David,

    I wouldn't use this method for anything other than table data access. It's a non-bloated BOI alternative to ODBC.

    The ScriptBasic example took 0.8273508 of a second to return 208 accounts and their balances.

Reply Children
No Data