Passing ADO to CR9 during Runtime [INFO]

[ARPRINCE]

After trying to workout how to pass my recordset to a Report created by CR9 since last Friday, I decided to call for TECHSUP with Crystal Decisions.

CR9 rocks IMHO!!. Now, you don't need a TTX file or OCX to pass your recordset!!!

Here are the things you need to setup:

(1) As Project Reference, you need to select:
- Crystal Report 9 ActiveX Designer Runtime Library
- Microsoft ActiveX Data Object 2.x Library

(2) As Project Component, you need to select:
- Crystal Report Viewer Control 9

(3) Create a Report outside VB environment (not RDC) and saved the file (file extention = .RPT).
- Remember, when saving the report, unclick the "SAVE DATA WITH REPORT" option on the file menu.


I don't know if this matters but I use:
-VB6 Developer Edition
-CR9 Developer Edition (ver. 9.2.0.448)
-MDAC 2.6

Here's the sample CODE:


Option Explicit
Dim crAPPLICATION As CRAXDRT.Application
Dim crREPORT As CRAXDRT.Report

Dim adoCN As New ADODB.Connection
Dim ADOrs As New ADODB.Recordset


Private Sub Form_Load()
Dim CNSTR As String
CNSTR = "Provider=MSDASQL.1;Persist Security Info=False;Extended Properties="""
CNSTR = CNSTR & "DSN=MQIS;Description=SQL Server;UID=APRINCIP;APP=Visual Basic;WSID=ALAINP;DATABASE=LMS"""
Set adoCN = New ADODB.Connection
Set ADOrs = New ADODB.Recordset

Set crAPPLICATION = New CRAXDRT.Application
Set crREPORT = crAPPLICATION.OpenReport("C:\REPORTS\REPORT1.RPT")

With adoCN
.ConnectionString = CNSTR
.CursorLocation = adUseClient
.Open
End With

With ADOrs
.ActiveConnection = adoCN
.CursorType = adOpenDynamic
.Open "SELECT * FROM ACCOUNT WHERE ACCOUNTID < 10"
End With

crREPORT.Database.SetDataSource ADOrs, 3, 1
CRViewer91.ReportSource = crREPORT
CRViewer91.ViewReport

End Sub

Private Sub Form_Resize()
With CRViewer91
.Top = 0
.Left = 0
.Width = Me.ScaleWidth
.Height = Me.ScaleHeight
End With
End Sub

[NewbieVB2003]

Hello ARPRiNCE,
This is exactly what I need to do but with a resultant from Stored procedure.Can you help me with the code.

I tried to use your code and wanted to see how the report looks like .So, I tried it with a table and it throws an error message :
"Subscript : Out of Range " and highlights "crREPORT.Database.SetDataSource ADOrs "
What needs to be filled in the [Data Tag] and [Table Number]

Also,how will be able to play around with the layout of the blank report.
Thanks

[ARPRINCE]

NewbieVB2003:

This is not a blank report and is not created during runtime. I'm just showing how you can pass an ADO recordset to an existing report.

You can get additional info using this link...

http://support.crystaldecisions.com/...s/apps_rdc.pdf

[NewbieVB2003]

I had been eagerly waiting for your reply. Could you please guide me with my task.

I have been assigned a task and I need your help (if possible).
I need to call a Stored Procedure from VB form inorder to retrieve data from the database and create its ADO recordset from the Output of Stored Procedure . Pass it to a TTX file .Then, display the fields on the report in a desired format based on this TTX file.
Could you please tell me how this can be done.

I would really appreciate it if you could possibly spare sometime and help me accomplish this task .

[ARPRINCE]

Try this:

(1) Create a simple report with your CR9 with any of your table (again make it simple like showing FNAME and LNAME). Use OLE DB for connection. When saving the report, click on FILE then click on SAVE DATA WITH REPORT to remove the check mark.

(2) Create a New Project with MODULE1 and FORM1.

(3) Include as a component the ff:
-Crystal Report Viewer Control 9
-Microsoft ADO Data Control 6.0 (OLEDB)
As Project Reference, you need to select:
- Crystal Report 9 ActiveX Designer Runtime Library
- Microsoft ActiveX Data Object 2.x Library


(4) Inside your FORM1, place a CRVIEWER9 control.

(5) Under Form1, type this in and replace all necessary sections with yours:

Option Explicit
Dim crAPPLICATION As CRAXDRT.Application
Dim crREPORT As CRAXDRT.Report
 
Dim adoCN As New ADODB.Connection
Dim ADOrs As New ADODB.Recordset
 
 
Private Sub form_load()
Dim CNSTR As String
 
CNSTR = "DSN=XXXX;DATABASE=XXXX;UID=XXXX;PWD=XXXX" 'change this with yours
Set adoCN = New ADODB.Connection
Set ADOrs = New ADODB.Recordset
 
Set crAPPLICATION = New CRAXDRT.Application
Set crREPORT = crAPPLICATION.OpenReport("C:\VB\NEWLMS\ACCOUNT_INSTRUCTIONS.RPT") 'change this with yours
 
With crREPORT.Database.Tables(1).ConnectionProperties
   .Item("Provider") = "XXXX" 'change this with yours
   .Item("Data source") = "XXXX"
   .Item("Initial Catalog") = "XXXX"
   .Item("User ID") = "XXXX"
   .Item("Password") = "XXXX"
End With
 
 
 
With adoCN
   .ConnectionString = CNSTR
   .CursorLocation = adUseClient
   .Open
End With
 
With ADOrs
   .ActiveConnection = adoCN
   .CursorType = adOpenDynamic
   .Open "YOUR SQL STATEMENT HERE THE SAME AS WHAT YOU HAVE IN YOUR REPORT"
End With
 
'You can check if you have records by removing the comment below.
'msgbox ADOrs.RecordCount
 
crREPORT.Database.SetDataSource ADOrs, 3, 1
CRViewer91.ReportSource = crREPORT
CRViewer91.ViewReport
 
End Sub
 
Private Sub Form_Resize()
   With CRViewer91
      .Top = 0
      .Left = 0
      .Width = Me.ScaleWidth
      .Height = Me.ScaleHeight
   End With
End Sub

(6) Under Module1, type this in:

Option Explicit
 
Declare Function CREATEFIELDDEFFILE Lib "p2smon.dll" (lpunk As Object, ByVal filename As String, ByVal boverwritexistingfile As Long) As Long

(7) RUN YOUR REPORT!!!!

If everything goes well, you're on your way. You don't even need a TTX file. Then, you just change the way you call your recordset and use SPROCS.

[NewbieVB2003]

Thank you very much ARPRINCE for explaining it so well.

But in my case, I am not supposed to connect my report to the SP/table directly . It should be a blank report ;no connection and at runtime ; should fetch the data from the SP and display it on the report.

What option will you suggest /choose in this scenario.
I need to be completing this task by this weekend and till date, have no clue as to how I should proceed.
Thanks.

[briancps]

There's some good stuff in here !!! I've been playing with this for quite some time - are you referring to the version of Crystal Reports that ships with VB.NET (I thought it was 9) or do I have to buy an upgrade to their full version ??

[NewbieVB2003]

I am really not sure what you means by :

Then, you just change the way you call your recordset and use SPROCS.

I am using Stored Procedures ;but have no clue how I should proceed with the report.

Are you trying to say to create a report based on this Stored Procedure and then pass the values using VB Code??
Please assist me as it is an urgent issue and for past 2 weeks I have been looking out the repl to tackle this issue.

[ARPRINCE]

You could either be using SPROCS or pass a SQL string to your ADO. My main point was no matter how you get your ADO recordset as long as you have one, you can pass this to your report.

[NewbieVB2003]

Thank you ARPRINCE for replying back.

If I try to create a Report format using ADO and select the SP,it prompts for OUTPUT parameter ;but does not allow me to pass it.So,I am not even able to design my report directly using ADO.


Well,http://www.vbforums.com/showthread.p...16#post1454716 was my code used to call SP and return the Output value.
How do you think should I proceed.

Thanks

[NewbieVB2003]

My TTX file shows the new ADO Recordset ;but the data which it displays on the report is not new data
I tried .Refresh and .DiscardSavedData and have disabled "Save data with report"

Any help would be highly appreciated.

Thanks

[NewbieVB2003]

In your case,you have defined the Query String as :

.Open "SELECT * FROM ACCOUNT WHERE ACCOUNTID < 10"

What will the same statement look like in the case of an SP if the values (V1,V2,V3,V4) Integer values need to passed from the VB form?


On viewing CR's SQL Query,it looks like :

{CALL database_name.user_name.SP_Name;1(V1, v2,V3, V4)}

[ARPRINCE]

If you want to create a CR Report using sprocs, just do the following:

(1) Create the SPROCS in the SQL Server.
(2) Open a blank CR Report.
(3) On the Database Expert, choose OLE DB (ADO) and search for your SQL Server or Make a new connection.
(4) Expand your SQLSERVER connection and you will see the DBO and INFORMATION_SCHEMA items.
(5) Expand your DBO and you get TABLES, VIEWS, STORED PROCEDURES.
(6) Now, instead of using TABLES as reference, choose STORED PROCEDURES using the SPROC you created.
(7) Create your report!!

Now when you run this and it has some parameters, CR would always pop-up a generic parameter form asking user to enter the parameters one-by-one.

There you have it!! Now, you also don't need a TTX file for this and it is easily ported to your VB program.


I hope this made it easier for you.

[NewbieVB2003]

Thanks ARPRINCE . Your advices inspired me to try your method and,believe it or not, I have been able to complete the assigned task .

Since I was new to VB, I was struggling with my code and by the time you posted your last reply,I was able to explore this concept and figure out a way.

Thanks again pal. *thumbs up*

[ARPRINCE]

I'm glad that you finished your project. Cheers!!

[FiRe5tOrM]

this is very simular to what i am trying to do but with access reports is this possible please?

[hyme]

Howdy!

Great Post!!!!!!!!
You saved me hours of trying to figure version 9 out.

Thanks

Hyme