ADO/Recordsets with Crystal Reports

[jtrapat1]

Could you give me a little direction?

We're using version 4.6 of crystal reports and my boss is big on ADO from vb6.
We're new to crystal so we don't know how much ADO can be used with crystal.

We'd like to know if we could pass recordsets into crystal.

We looked thru the documentation and found a couple of methods that might do this:
RetrieveSQLQuery and SQLQuery

Can you let me know if this is possible in crystal reports or give me a hint as which way to approach this?

Reply when you can.
Thanks

 

[mmaz]

Hi! If you use SQLQuery property, your report is actually based on the query, not the recordset. To use the recordset, use the following code:

Set oConn = Server.CreateObject("ADODB.Connection&quot

oConn.Open("ADO Demo mdb&quot

set session("oRs&quot = oConn.Execute("Select * FROM ADODemoTable&quot

Set Database = session("oRpt&quot.Database
set Tables = Database.Tables
set Table1 = Tables.Item(1)

session("oRpt&quot.DiscardSavedData

Table1.SetPrivateData 3, session("oRs&quot

The SetPrivateData "tells" the report that its datasource is the recordset.

FYI: you cannot use the recordset if your connection to the db is OLE DB. I learned that the hard way...

Hope that helps!

 

[sharonc]

I'm using Crystal 8.0 with VB 6 and Ole DB. It works great. Here is my code:


Dim Report As New CrystalReport1
Dim rs As New ADODB.Recordset
Private Sub Form_Load()
Dim strSql As String
Dim strCnn As String

Report.Database.Tables(1).SetLogOnInfo "SEIMS", "ProdTraining", txtUser, txtPass
Dim mcstrConnect As ADODB.Connection

Set mcstrConnect = New ADODB.Connection

mcstrConnect.Open _
"Provider=SQLOLEDB.1;Persist Security Info=True;Initial Catalog=ProdTraining;Data Source=SEIMS", txtUser, txtPass

strSql = "Select * from SignUpSheet"

rs.Open strSql, mcstrConnect

Report.Database.SetDataSource rs
CRViewer1.ReportSource = Report

Screen.MousePointer = vbHourglass
CRViewer1.ReportSource = Report
CRViewer1.ViewReport
Screen.MousePointer = vbDefault

Form_Load_EXIT:
'Close the connection
rs.Close
mcstrConnect.Close
Set mcstrConnect = Nothing

End Sub

Private Sub Form_Resize()
CRViewer1.Top = 0
CRViewer1.Left = 0
CRViewer1.Height = ScaleHeight
CRViewer1.Width = ScaleWidth

End Sub