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.
- 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.
- 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
