The second recordset doesn't display! 1

[lotwal01]

Hi,
I have a report with two recordsets. The "parent" recordset shows, however the "child" recordset come up as blank each time, even though the corresponding ADO object contains the data i need. I cannot get both recordsets to display. Crystal is just not reading the data contained in the "child" recordset. I am using two TTX files, one for the parent recordset, and one for the child recordset.

I'm using CR 8.5 and VB 6 and use ADODB to get the data.

Here is a sample of my code:

Dim objConn As New ADODB.Connection
Dim rs As ADODB.Recordset
Dim rs2 as ADODB.Connection
Dim CRXApplication As New CRAXDRT.Application
Dim CRXReport As CRAXDRT.Report

Private Sub Command1_Click()
Dim CRXDatabase As CRAXDRT.Database
Dim intNr As Integer
Dim strSQL As String
Dim strSQL2 AS String

intNr = 9

Set CRXReport = CRXApplication.OpenReport("\sosrapport.rpt", 1)
Set CRXDatabase = CRXReport.Database

strSQL = strSQL & "select DATA from TABLE where ID = intNr"
strConnString = "Provider=SQLOLEDB;Data Source=***;Initial Catalog=***; User Id=***;Password=***"

objConn.Open strConnString
Set rs = objConn.Execute(strSQL)
Set rs2 = objConn.Execute(strSQL2)
CRXDatabase.SetDataSource rs, 3, 1
CRXDatabase.SetDataSource rs2, 3, 2

'CRXReport.PrintOut
CRXReport.Export True

Set rs = Nothing
Set rs2 = Nothing
Set objConn = Nothing

End Sub

 

[MikeTom]

strSQL = strSQL & "select DATA from TABLE where ID = intNr

But what about strSQL2 are you sure you getting a second recordset.

 

[lotwal01]

Oh, sorry, that line seems to have disappeared. Yes, I'm sure I'm getting a second recordset.

/Lotta

 

[MikeTom]

Dim rs2 as ADODB.Connection

should this not be a recordset are you still sure you getting a second recordset.

 

[lotwal01]

Yes, I'm still sure I'm getting the second recordset. It's not the original code I'm putting out here, it's too much and I obviously did something wrong in the copying process. But like I said, I know I'm getting it.

 

[lotwal01]

Here is the code again, hopefully right this time:

Dim objConn As New ADODB.Connection
Dim rs As ADODB.Recordset
Dim rs2 As ADODB.Recordset
Dim CRXApplication As New CRAXDRT.Application
Dim CRXReport As CRAXDRT.Report

Private Sub Command1_Click()
Dim CRXDatabase As CRAXDRT.Database
Dim intNr As Integer
Dim strSQL As String
Dim strSQL2 As String

intNr = 9

Set CRXReport = CRXApplication.OpenReport("\report.rpt", 1)

Set CRXDatabase = CRXReport.Database

strSQL = "select something from anything where ID = '" & intNr & "'"
strSQL2 = strSQL2 & "select data from table where something = anything"
strConnString = "Provider=SQLOLEDB;Data Source=***;Initial Catalog=***; User Id=***;Password=***"
objConn.Open strConnString
Debug.Print strSQL
Set rs = objConn.Execute(strSQL)
Set rs2 = objConn.Execute(strSQL2)

CRXDatabase.SetDataSource rs, 3, 1

CRXDatabase.SetDataSource rs2, 3, 2

'CRXReport.PrintOut
CRXReport.Export True

Set rs = Nothing
Set rs2 = Nothing

Set objConn = Nothing

End Sub

 

[MikeTom]

Does all the fields in the second recordset match all the fields in the report?

 

[MikeTom]

Soryy i see you are using ttx files. how did you create them. and do the fields match.

 

[lotwal01]

Yes, they match. everything works except for that recordset/ttx-file. How I created the ttx-file? How do you mean? I created it in notepad and renamed it like I always do. It's only one field in the ttx-file. Can the problem be that in recordset 1 it's only one post but in recordset 2 it can be up to three posts?

 

[MikeTom]

their are several ways of creating a ttx file the best is using crystal function as it will do away with human error i.e creating them in notepad.
'Create deffile function
Declare Function CreateFieldDefFile _ Lib "p2smon.dll"(lpUnkAs Object, ByVal fileName As String,_ ByVal bOverWriteExistingFile As Long) As Long

CreateFieldDefFile (Rs , X:\YourPath\DefFile.ttx, 1)
last 1 is true for overwrite exsisting. this returns a 1 or a 0 (True or false) if it creates sucessfully.

also try use "rpt.ReadRecords" after you setdataSource
This will force the report to read the Data locally into the report rather than the recordset being an object referenced by the report which is then set to nothing.

 

[lotwal01]

The ttx-file works too....I tried it on a new report. The rpt.readrecords didn't help either.

 

[MikeTom]

their must be something wrong with you rpt. have you got sample data in your ttx files. if not you can add a third column in the ttx file which contains sample data. this will then allow you to prexiew the report in crystal designer. if you can see all the sample data your rpt is ok and it then must be your code.

 

[lotwal01]

I can see the sample data in my report. So, what can be wrong with my code? Everything else works just fine, it's just the second recordset that doesn't show...

 

[kenhamady]

One report can't really use two recordsets. Recordsets can't be effectively linked like tables. You should combine the 2 recordsets into one, and pass them to a report created with one TTX. 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

 

[lotwal01]

But I can't use only one recordset. The thing is that I'm getting ONE post from recordset 1 and up to three posts from recordset 2. How do you combine them?

/Lotta

 

[kenhamady]

To answer that I would have to know how you were going to combine them in the report. 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

 

[lotwal01]

Well, don't I have to combine them in VB and pass them to the report created with one TTX, like you wrote? I really don't know how to move forward with this. So...if I combine them in VB (or with SQL) how do I do that?

/Lotta

 

[kenhamady]

When you added these 2 TTX files to the report, did you link them together? If these two recordsets were two physical tables, how would you use them in the report?

Each recordset is a virtual table. Crystal needs a single virtual table to create a report, although this virtual table can be made from several tables linked together using joins or append using a union query.

You can append one table to the other vertically (union query) or you can link the tables using a join (horizontally). But the end result has to be one set of rows and columns per report. If you need to use two separate virtual tables, then you need a report and a subreport. 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

 

[lotwal01]

Yes, I linked the TTX files together. I don't know how I would use the recordsets if they were two physical tables.

So what you are saying is that I can't have two TTX files in one report? This is kind of confusing because the other support I wrote to said that I COULD use two TTX files. I think I rather listen to you from now on, you seem to know a bit more than them!

I think I'm going for a subreport now. Hope that will work better...