Problems running Crystal Reports in the Infor CRM (formerly Saleslogix) LAN client

We recently had a client that was using the Crystal RDC object in VBScript within the LAN client to run a Crystal Report.  They were able to run the report once but the second time they tried, the report failed with a “Database Vendor Code 7” error.  Digging in I found the problem.  This was a first for me so I thought I would record it should it ever happen again.

Here is the code I was using:

    filename = Application.BasicFunctions.GetPersonalDataPath & “” & radContractType.Text & ” – ” & frmSchoolContract.CurrentID & “.pdf”
    set rdc = Application.BasicFunctions.GetCrystalReport(family & “:” & report)
    With rdc
         .DiscardSavedData
         For i = 1 To .parameterFields.Count
             .ParameterFields.Item(i).ClearCurrentValueAndRange
         Next

         With .ExportOptions
                 .FormatType = 31 ‘crEFTPortableDocFormat
                 .DestinationType = 1 ‘crEDTDiskFile
                 .DiskFileName = filename
         End With
         .RecordSelectionFormula = “{SCHOOLAGREEMENTS.SCHOOLAGREEMENTSID} = ‘” & frmSchoolContract.CurrentID & “‘”       
    msgbox .SQLQueryString
         .Export false
    End With
    Set rdc = Nothing

I added the msgbox shown in bold to see what the report was using as the SQL result set to run with.  This is what it showed the first time:

 SELECT “ACCOUNT”.”ACCOUNT”, “ADDRESS”.”ADDRESS2″, “ADDRESS”.”CITY”, “ADDRESS”.”STATE”, “ADDRESS”.”POSTALCODE”, “CONTACT”.”FIRSTNAME”, “CONTACT”.”LASTNAME”, “ADDRESS”.”ADDRESS1″, “SCHOOLAGREEMENTS”.”SCHOOLAGREEMENTSID”
 FROM   ((“sysdba”.”SCHOOLAGREEMENTS” “SCHOOLAGREEMENTS” INNER JOIN “sysdba”.”CONTACT” “CONTACT” ON “SCHOOLAGREEMENTS”.”CONTACTID”=”CONTACT”.”CONTACTID”) INNER JOIN “sysdba”.”ACCOUNT” “ACCOUNT” ON “CONTACT”.”ACCOUNTID”=”ACCOUNT”.”ACCOUNTID”) LEFT OUTER JOIN “sysdba”.”ADDRESS” “ADDRESS” ON “ACCOUNT”.”ADDRESSID”=”ADDRESS”.”ADDRESSID”
 WHERE  “SCHOOLAGREEMENTS”.”SCHOOLAGREEMENTSID”=’QV28PB71N7PP’
 ORDER BY “ACCOUNT”.”ACCOUNT”

And this is the SQL that was used when running the second time:

 SELECT “ACCOUNT”.”ACCOUNT”, “ADDRESS”.”ADDRESS2″, “ADDRESS”.”CITY”, “ADDRESS”.”STATE”, “ADDRESS”.”POSTALCODE”, “CONTACT”.”FIRSTNAME”, “CONTACT”.”LASTNAME”, “ADDRESS”.”ADDRESS1″, “SCHOOLAGREEMENTS”.”SCHOOLAGREEMENTSID”
 FROM     (“sysdba”.”CONTACT” “CONTACT” INNER JOIN “sysdba”.”ACCOUNT” “ACCOUNT” ON “CONTACT”.”ACCOUNTID”=”ACCOUNT”.”ACCOUNTID”) LEFT OUTER JOIN “sysdba”.”ADDRESS” “ADDRESS” ON “ACCOUNT”.”ADDRESSID”=”ADDRESS”.”ADDRESSID”

 ORDER BY “ACCOUNT”.”ACCOUNT”

What?  Why did the SQL get all messed up?

I found that pressing Cntlr+F5 allowed me to run the report once more, but failed on the second attempt again.

After lots of digging I found  2 things that had to be changed in order to get the SQL to not get corrupted after running the report.

  1. I had to Add at least one field from the SCHOOLAGREEMENTS table to the report layout.  That fixed the disappearing “FROM” portion of the SQL statement.
  2. After exporting (the .Export false line), I had to set the RecordSelectionFormula = empty.  That fixed the disappearing “WHERE” portion of the SQL statement.

Very strange.  I have never run into this before.  The client was on an older version of SLX so I am not sure if that had something to do with this or not. 

    filename = Application.BasicFunctions.GetPersonalDataPath & “” & radContractType.Text & ” – ” & frmSchoolContract.CurrentID & “.pdf”
    set rdc = Application.BasicFunctions.GetCrystalReport(family & “:” & report)
    With rdc
         .DiscardSavedData
         For i = 1 To .parameterFields.Count
             .ParameterFields.Item(i).ClearCurrentValueAndRange
         Next

         With .ExportOptions
                 .FormatType = 31 ‘crEFTPortableDocFormat
                 .DestinationType = 1 ‘crEDTDiskFile
                 .DiskFileName = filename
         End With
         .RecordSelectionFormula = “{SCHOOLAGREEMENTS.SCHOOLAGREEMENTSID} = ‘” & frmSchoolContract.CurrentID & “‘”
         .Export false
         .RecordSelectionFormula = empty
    End With
    Set rdc = Nothing

 

 

ABOUT THE AUTHOR

Kris Halsrud

Kris Halsrud is a Senior Analyst / Developer for Customer FX Corporation.

Submit a Comment

Your email address will not be published. Required fields are marked *

Subscribe To Our Newsletter

Join our mailing list to receive the latest Infor CRM (Saleslogix) news and product updates!

You have Successfully Subscribed!