ADO recordset With Crystal Reports 1

[meljon12]

Can anyone help me with this one?

I have a test report built against a SQL Server database using a native connection. What I want to do at run time through VB using the RDC component, is to change the datasource to point to an ADO recordset.

I've tried numerous code examples from books and I can step through the code without any errors, but the Report Output appears as if the report is using the datasource /SQl it was originally built on at design time. Does anyone have any working code examples that could help me in my efforts?

Your help would be much appreciated

Thanks

 

[kenhamady]

You can only pass a recordset if the report was designed to run with a recordset, you can't switch it at runtime. You would have to create or convert the report to the active data driver. Not all reports can be converted, some need to be recreated. Ken Hamady

http://www.kenhamady.com/

On-site custom Crystal Reports Training and Consulting.
Quick Reference Guide to using Crystal in VB.

 

[Guest_imported]

Ken, can you post some code samples and/or send to my email address, I too am looking for ways to pass a recordset from vb to cr8. I'm a newbie and am having a hard time finding code.

Thanks!
Tammy@theboyz.net

 

[kenhamady]

which integration method are you using? Ken Hamady

http://www.kenhamady.com/

On-site custom Crystal Reports Training and Consulting.
Quick Reference Guide to using Crystal in VB.

 

[Guest_imported]

I'm not too sure what you mean. I'm using cr8 inside vb6. I'm trying to connect to sql server 7, and I was using some of the sample code from the faq's with my code (which involves an active connection):

Dim i_ReportCR_o As New CrystalReport1
Dim i_ReportDS_o As New DataSet
Dim i_Conn_o As New ConnectionADO

Private Sub Form_Load()

Dim l_Sele_s As String
Dim l_crTables_o As Object
Dim l_crTable_o As Object

Screen.MousePointer = vbHourglass

l_Sele_s = "SELECT " + _
"rgnNam_v, " + _
"tor.ShortDesc_v, " + _
"u.Num_v " + _
"From " + _
"t_univ u, t_orgn o, t_univ_link ul, t_ordr tor, t_orgn_link ol "

Call i_ReportDS_o.Ctor(i_Conn_o, "t_orgn", l_Sele_s)

Dim l_Where_s As String
l_Where_s = "Where " + _
"o.orgn_xk = 'orgn50000000000175' and " + _
"ul.link_x = o.orgn_xk and " + _
"u.univ_ks = ul.univ_s and " + _
"ol.orgn_s = o.orgn_ks and " + _
"ol.relationship_v = 'FeeSchedule' and " + _
"tor.ordr_xk = ol.link_x "

Call i_ReportDS_o.Retrieve(l_Where_s)

Set l_crTables_o = i_ReportCR_o.Database.Tables
Set l_crTable_o = l_crTables_o.Item(1)

l_crTable_o.SetDataSource i_ReportDS_o.i_RecSet_o

CRViewer1.ReportSource = i_ReportCR_o
i_ReportCR_o.Application.LogOnServer "p2ssql.dll", "Enodatio", "FeeSys", "sa", "enodat"

CRViewer1.ViewReport

Screen.MousePointer = vbDefault
'''''''''''''''''''''''''''''''''''''''

but the connection itself was failing with my recordset and crystal reports connected using the code and links in the designer. I'm wondering what I have to do to get the recordset to open, and have crystal reports use the recordset in code? (I do not have a problem with connecting to the database anywhere else in my app, but only when trying to create a report.)

Thanks for your help!
Tammy

 

[kenhamady]

I can't tell you why your recordset connection is failing. Crystal doesn't have anything to do with creating the recordset.

Once you get the recordset into memory, the command you need will be something like:

report.database.setdatasource adodc1.recordset, 3

You have to design the report originally using Active Data Driver in order to pass a recordset to it.

CR8 has 4 different sets of syntax to use in VB, and it appears that you are using the RDC. Make sure that any FAQ you are using is for the RDC. Ken Hamady

http://www.kenhamady.com/

On-site custom Crystal Reports Training and Consulting.
Quick Reference Guide to using Crystal in VB.

 

[Guest_imported]

Well, I use that setdatasource method, and I get the "server not opened yet" error. However, I haven't found any instruction yet on how to set the report so that it uses the active data driver. All I want to do is pass my recordsets to the report with as little use of the designer as possible. (Aren't there people out there who who have done this?) If there is anyone out there that can give me more information on this with sql server 7, I would really really appreciate it.

 

[balves]

People have done it a lot, but have used the standard ADO objects: ADODB.Connection, ADODB.Recordset.

I haven't used Dim X as Dataset or know what library that refers. Perhaps Crystal is expecting an ADO recordset and that's not what it's getting. Also, why Dim things are Object when you know what they are?

Dim adoConn as ADOdb.Connection
Dim adoRS as ADOdb.Recordset
Dim strSQL as String
Dim Report as CrystalReport1

...make the ADO connection however you want...

strSQL = "Your SQL statement"

Set adoRS = New ADOdb.Recordset
With adoRS
.ActiveConnection = adoConn
.CursorLocation = adUseServer
.CursorType = adOpenForwardOnly
.Open strSQL
End With
' the records are now in the ADO recordset
' you could check for EOF before activating Crystal

Set Report = New CrystalReport1
Report.Database.SetDataSource adoRS
CRViewer1.ReportSource = Report
CRViewer1.ViewReport
Brian J. Alves
Terrier Consulting, Inc.
Email: brian.alves@worldnet.att.net
VB / Crystal / SQLServer

 

[kenhamady]

If you haven't designed the report using the Active Data Driver then you will get nowhere.

When you create the report and select a method of connection you should have an option for Active Data in the tree. Ken Hamady

http://www.kenhamady.com/

On-site custom Crystal Reports Training and Consulting.
Quick Reference Guide to using Crystal in VB.

 

[Guest_imported]

The thing is, I'd like to connect in code.

 

[Guest_imported]

Brian, thanks for the help. I didn't realize my code doesn't explicitly say it is ado, but it really is. Its been wrapped. (sorry, my fault) I made the changes in my code to match yours, and I still get the "server not yet opened" error. I'm trying to connect to sql server 7. If anyone has any ideas, pleez let me know.

Thanks!

 

[kenhamady]

You can connect in the code, but the report has to be designed to accept that connection. Most reports can't be passed a recordset. Only those that were designed using the Active Data Driver. Your report is still trying to connect to the database, which is why you are getting the server error. Ken Hamady

http://www.kenhamady.com/

On-site custom Crystal Reports Training and Consulting.
Quick Reference Guide to using Crystal in VB.

 

[Guest_imported]

ok, so is there a property I can set in code so that it uses the active data driver?

 

[kenhamady]

No, this has to be set when the report is designed, at the time the data for the report is selected. In the list of connection options (like ODBC) you will see the Active Data Driver, which will open up options to define a temporary connection to retrieve the needed fields. Ken Hamady

http://www.kenhamady.com/

On-site custom Crystal Reports Training and Consulting.
Quick Reference Guide to using Crystal in VB.

 

[itme]

Ken,
Last night I found the code that passes a recordset, and it does not use the active data driver. In fact, the only time you have to touch the designer is to drop your unbound fields to it. Its beautiful.. Here it is..


Dim Report As New CrystalReport1
Dim ADOrs As ADODB.Recordset
Dim DBLocation As String

Private Sub Form_Load()

'Show the common dialog to select the sample database
CommonDialog1.ShowOpen

'binds the ADO recordset object to ADO recordset variables
Set ADOrs = CreateObject("adodb.recordset&quot

'sets cursor location for recordset
'the CursorLocation, CursorType and LockType in this sample are the recommeneded
'choices when working with the Crystal Reports active data driver.
ADOrs.CursorLocation = adUseClient

'open the recordset
ADOrs.Open "select * from customer", "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & CommonDialog1.FileName & ";Persist Security Info=False", adOpenDynamic, adLockBatchOptimistic

'add the ADO recordset to the report
Report.Database.AddADOCommand ADOrs.ActiveConnection, ADOrs.ActiveCommand

'these variables will be used in the Check method to validate the formula fields
Dim x As Boolean
Dim y As String

'Note: once the ADO recordset is added to the report it is referenced as a table object within
'the report

'pass the first field name from the added ADO recordset to the first formula field in the report
Report.FormulaFields(1).Text = Report.Database.Tables(1).Fields(1).Name
'match the field name from the ADO recordset for a column header
Report.Text1.SetText ADOrs.Fields(0).Name
'validate the formula field
Report.FormulaFields(1).Check x, y

'pass the second field name from the added ADO recordset to the second formula field in the report
Report.FormulaFields(2).Text = Report.Database.Tables(1).Fields(3).Name
'match the field name from the recordset for a column header
Report.Text2.SetText ADOrs.Fields(2).Name
'validate the formula field
Report.FormulaFields(2).Check x, y


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

End Sub

 

[kenhamady]

itme,

I will accept your correction. I guess there are 2 ways to pass ADO, using a report with unbound fields and using a report built with the Active Data Driver.

I find the unbound fields route much more complicated, but that may be my own preferences. Either way, an existing report will have to be recreated. Actually, if an existing report only has one table, it can be converted to the Active Data Driver in the Database menu. It won't work if the report has joined tables. Ken Hamady

http://www.kenhamady.com/

On-site custom Crystal Reports Training and Consulting.
Quick Reference Guide to using Crystal in VB.

 

[Guest_imported]

There are alot more tricks can you do with sql to get around that stuff than you think. Plus, you can program against it, and debug it. There's no debugger for design time controls. Also, this method technically does use the active data driver, according to the help files.

I'm not sure what you mean when you say you have redo an existing report. I found it far easier than going into that tree and reconnecting yourself everytime you do a report. Once you write the code, you have it. You just use it and tweek it. You don't have to do some of the same things over and over and over again. One thing that bothered me is that if you do your joins in sql, you had to do them again in the linking "expert" thing in order for fields to show up. I'm an object oriented programmer, and for awhile I thought I was going back in time. At least this way, I do it once and only once.

Thanks all for the help anyways. I hope this code helps everyone else who has problems accessing your database (It definately took long enough for me to find). I found it 100 times easier than the data access wizard and linking expert. And it definately avoids the "Server not yet been opened" error.

Tammy

 

[Rosalie]

Can someone point me in the right direction...
I am using CrystalReports 8.5, VB6, ADO, CRAXDRT, Active Data Driver, SqlServer7.
I have formatted my report and use the Recordset that I create in VB to populate it. It works fine when I do a 'Select *' but when I select only the fields that I need on the report, the fields do not populate on the report(they are populated in the recordset and I am selecting them in the order that they appear on the report). I am using the 'Report.Database.SetDataSource myrecordset, 3'.

I ultimitly want to join 4 tables and print a few of the fields from each table. If I can get the fields from the first table to map on the report, then hopefully my next step is to add the other tables/fields to my report and then add them to my recordset.
Thank You. The information from this website has been very helpful.

 

[kenhamady]

Rosalie,

Could you post this as a new question rather than a follow-up. Otherwise people will confuse the questions. Ken Hamady

http://www.kenhamady.com/

On-site custom Crystal Reports Training and Consulting.
Quick Reference Guide to using Crystal in VB.

 

[Guest_imported]

hai
iam using recordset created dynamically ( append.fields) without using connection string.i find difficulty in using this recordset in crystal report.
i.e report.database.setdatasource recordset
is not working fine. please help me