calling CR with a query from VB

[mmarino]

I'm using CR8 and VB6, and I'm using the ocx method.
I have an rpt file that I need to call from my VB application, the report gets its data from a SQL designer query. My problem is that when I try to run the report I get an error 20553 "Invalid parameter field name". I know my connection works because my other reports work, but this is the only one that uses a query instead of a table as the data source. Any suggestions? I appreciate all the help I can get.
Thanks,

Monica

 

[kenhamady]

CD doesn't recommend combining QRY files with applications, because QRY connectivity is hard to control when you are distributing an application.

You might want to create a recordset in the app, and pass it to the report. You will have to recreate the report using the Active Data driver, because you can't convert the driver of a report using QRY files. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[Guest_imported]

Where can I find more information on the web about passing a recordset to the report? Can one do this using automation with cpeaut32.dll (version 7.0.100.8)? Thanks for your help!

 

[kenhamady]

Yes you can do this using the SetPrivateData command. Your report must use the Active Data Driver.

For more details you can search the CD website for their documentation, or you can use a 3rd party guide like mine or the one from George Peck. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[Guest_imported]

Thanks!

 

[mmarino]

We were trying to get to the point where we don't need to recompile the app if we need to add a new report. If I create a recordset in the app, then we wouldn't achieve this. Is there another solution? Where can I find information on how to use SQL stored procedures? maybe that will work for us...
Thanks for your answer.

 

[kenhamady]

That is a good option. If you create a stored procedure that ends with final select, the results will be exposed in CR as an available table. You might have to flip a setting in File-Options to get it to see Stored Procedures.

If the SP has a parameter, you can pass the value to if from the app. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[mmarino]

Thanks! I will try that...

 

[Qtek]

How did u handle the Invalid parameter problem?
How do u pass Parameter fields from VB to Crystal Reports?

 

[Guest_imported]

I dont know if this will be any help to You But I have developed An App. Thats is written in VB Script Using The Embeded Crystal Control (Crviewer1) and an Outlook Form that LInks to Timberline and An SQl Backend database to Propogate All thinformation Temporarily in order to Produce the report It then Exports it to a Pubilc Folder Automaticaly
Heres the code :

Function pdfCreateBackChargePlusCM1()
im ThisJobNumber 'index
Set OrderPage = Item.GetInspector.ModifiedFormPages("Order&quot
Set CrystalReportViewer41 = OrderPage("CRViewer1&quot
Set MyOrderID = Item.GetInspector.ModifiedFormPages("General&quot.Controls("TextBox13&quot
Dim ThisOrder
Dim ThisOrderAmount
Dim ThisTrade
Dim OrderType
Dim ThisSubContractor
dim filesys, filetxt, getname, path
Dim newfolder, newfolderpath

Dim ThisJobName
Dim ThisPONumber
ThisJobNumber = Item.Userproperties("JobNumber&quot
ThisJobName = Item.UserProperties("Project&quot
ThisSubcontractor = Item.UserProperties("Vendor&quot
ThisOrderAmount = Item.UserProperties("OrderAmount&quot
ThisTrade = Item.UserProperties("PoTrades&quot
OrderType = Item.UserProperties("OrderType&quot
ThisPONumber = Item.UserProperties("PONumber&quot
newfolderpath = "I:\" & ThisJobNumber & " - " & ThisJobName & "\Purchase Orders\" 'Name must be constant
Dim TheFilePath
Dim TheFile
Dim TheFullPath
Dim TheHTMLData
Dim OrderID
OrderID = Item.UserProperties("Order ID&quot
TheFile = "po #" & ThisPONumber & " " & ThisSubcontractor & "-$ " & ThisOrderAmount & _
" " & OrderType & "- " & Replace (ThisTrade,"/","-&quot & ".pdf"
TheFullPath = newfolderpath & TheFile

ThisOrder = MyOrderID.Value
Set oApp= CreateObject("CrystalRuntime.Application&quot
Set oReport= oApp.OpenReport("I:\TimberlineSyncronizer\SubContractorBackChargeContractCM.rpt&quot
CrystalReportViewer41.ReportSource = OReport
oReport.SQLQueryString = "SELECT" & chr(10) & _
" Scope.`ScopeID`, Scope.`Item`, Scope.`Description`, Scope.`Trade`, Scope.`TradeCode`, Scope.`JobNumber`, Scope.`Job`, Scope.`Address`, Scope.`City`, Scope.`State`, Scope.`ZipCode`, Scope.`VendorID`, Scope.`SubContractor`, Scope.`SubAddress`, Scope.`SubCity`, Scope.`SubState`, Scope.`SubZipCode`, Scope.`SubPhone`, Scope.`SubFax`, Scope.`SubContact`, Scope.`PONUMBER`, Scope.`ORDERTYPE`, Scope.`DateIssued`" & chr(10) & _
"FROM" & chr(10) & _
"`Scope` Scope" & Chr(10) & _
"WHERE" & Chr(10) & _
"Scope.`ScopeID` = " & ThisOrder & CHR(10) & _
"ORDER BY" & Chr(10) & _
" Scope.`ID` ASC"
CrystalReportViewer41.ViewReport
With CrystalReportViewer41
.Width = 840
.Height = 480
.Left = 6
.Top = 60
.EnableGroupTree = False
.EnableExportButton = True
End With
oReport.ExportOptions.DiskFileName = TheFullPath
oReport.ExportOptions.DestinationType = 1
oReport.ExportOptions.FormatType = 31
oReport.ExportOptions.PdfExportAllPages = True
oReport.Export False
msgBox TheFullPath & " Has Been Published in the shared folder! "
ExportToTimberLine ' Just calling the next Sub.
End Function