Refrsh Report w/ Data from SQL passed Fr VB

[CueMan]

What I need is to create the SQL and DB connection within VB based on Users input
and pass this SQL into an .RPT file and populate the report.

The .RPT file was created using Crystal Designer Version 8.5 outside of VB.
The .Rpt file have all the liked tables defined and there is an SQL statement related to it.
When you go to Database / Show SQL Query you will see that SQL generated by Crystal. As this is
how you will get a Report defined, you have to go through all these.

My problem is when I ran my report, the report will always show that same report
that was ran in Crystal during design time, I used the following code that I have gathered from
books and at Crystals Web site.(See Below)

I thought that with RDC, we could just pass in the ADO Record set and the report should reflect
by what data was brought back from the record set open statement.

Well, the code below is what I used. The SQL statement in the Variable MAINSQL is tested each
time by me within SQL Analyzer and is found to be be correct. And after the ADO recordset is Opened,
I physically dumped the ADO recordset and the data is identical to the result from the SQL Analyzer.

The Open Connection is checked and each time it is opened.


So what am I doing wrong ?? Could it be that :

CrxReport.Database.SetDataSource Adors, 3, 1
CrxReport.ReadRecords

is not the correct way ???


I appreciate any help or direction that you could give.


Thank You



------ Below is Code Used --------



Dim CrxApplication As CRAXDRT.Application
Dim CrxReport As CRAXDRT.Report


MainSQL = < My SQL Statement are linked to 6 tables > Check against SQL Analyzer as Correct
' With Correct Data being returned


Set CrxApplication = CreateObject(&quot;CrystalRuntime.Application&quot

Set CrxReport = CrxApplication.OpenReport(App.Path & &quot;\&quot; & &quot;First.RPT&quot


Dim RptCNN As Connection
Dim DatCMD1 As ADODB.Command


Set RptCNN = New Connection


Dim DB_Name As String
Dim FilePath As String
Dim Server_Name As String
Dim ODBCDriver As String
Dim LocalMachine As String
Dim IndividualRS As ADODB.Recordset

DB_Name = &quot;BenServer&quot;
Server_Name = &quot;Mach4672&quot;
ODBCDriver = &quot;SQL SERVER&quot;
Provider = &quot;SQLOLEDB&quot;

With RptCNN

ODBCDriver = &quot;SQL Server&quot;
.Provider = &quot;SQLOLEDB&quot;
uid = &quot;SA&quot;
pwd = &quot;&quot;
.ConnectionString = &quot;Driver={&quot; & ODBCDriver & &quot;};Server=&quot; & Server_Name & &quot;;Database=&quot; & DB_Name _
& &quot;;UID=&quot; & uid & &quot;;PWD=&quot; & pwd
.Mode = adModeRead
.CursorLocation = adUseClient

.Open
End With

If RptCNN.State <> 1 Then
MsgBox &quot; Connection Open Error &quot;, vbCritical
Exit Sub
End If

CrxReport.DiscardSavedData

Dim Adors As New ADODB.Recordset

Set Adors = New ADODB.Recordset
With Adors
'Use the connection already opened
.ActiveConnection = RptCNN
.CursorType = adOpenDynamic
.Open MainSQL
End With

'' At this point I checked the Recordset, Its got the correct data as specified
in the SQL statement MainSql ....

If Adors.State = 1 Then

CrxReport.Database.SetDataSource Adors, 3, 1
CrxReport.ReadRecords

Else
'Debug.Print Err.Description
MsgBox &quot; Record Set Open Failed &quot;, vbCritical
Exit Sub
End If


Screen.MousePointer = vbHourglass
CRViewer1.ReportSource = CrxReport


CRViewer1.ViewReport


End Sub

 

[balves]

I never use .DiscardSavedData. I just never save the report with data.

Don't know what the last parameter is to .SetDataSource. I just use &quot;3&quot; and that's it.

CrxReport.Database.SetDataSource Adors, 3

I don't see any reason to use .ReadRecords. I would get rid of it.

You created the report with the Active Data Driver?

 

[CueMan]

Thanks Balves for your reply.
I got rid of the 1 at the SetDataSource Statement
I got rid of the .ReadRecords
I retained .DiscardDSavedData

Reran the job, still the same result.
I created the report using Crystal Report 8. (The Application Not with VB) I created it using Report Expert and creating a new report.
Then you have to connect to your data source and select your tables. Link your tables to your key/foriegn keys.
Then Crystal make you a report. I changed the crystal's default generated report by adding subtotals and grids until
it was our desired report.

But I think that each time it runs within VB, it is running the default SQL in the report when I created the report.

 

[balves]

It doesn't sound as if you used the Active Data Driver to create the report. If you did not, you cannot pass an ADO recordset from VB.

Why do need to pass a recordset? Doesn't the report generate the data?

 

[CueMan]

The reason I need to pass a recordset is that I've got several field selection criteria from the users, and have translated/embedded these into the SQL statement during the recordset creation.

In short, I've got FILTERED data that needs to passed into the report. Without going thru parameters passing and so on.

 

[balves]

If you're going to pass an ADO recordset, I really think you need to create the report as follows:

Pick SQL/ODBC as the Data Source. When the Logon Dialog box pops up, pick Active Data (ADO). Then enter either an ODBC connection or OLEDB connect string on the next dialog box.

When you've done all that correctly, you'll get to a &quot;Select Recordset&quot; dialog box. Pick the SQL radio button and paste your SQL in there (same SQL as in VB) and it will generate the report.