Pushing Data to Report using Data Definition File

[cmr0263]

I am new to CR on the web, have used it in local applications. I am trying to use it to create an application for a user to print with data they have already entered.

I have successfully created a MS Access table record with the data on it. I have also created a report using a data definition file. My SQL is working and only returns the 1 record. When the report is displayed, only the headers are there, there is no data. How do I push the data from the SQL to the report? Below is a sample of the code.....This was basically taken from the sample code on the CR site.

Thanks-
CMR

MYSQL = "SELECT lastname, firstname, appnum "
mysql = mysql & "FROM report WHERE AppNum = '" & request.querystring("appnum&quot & "'"

set rstemp = conn.execute(mysql)
set session("oRs&quot = rstemp

session("oRpt&quot.SQLQueryString = CStr(rstemp)

On Error Resume Next

session("oRpt&quot.ReadRecords

 

[FVTrainer]

You should be using the pdsmon.dll driver (i.e., Active Data), correct?

Your code probably should look like this, after you create "oRs" (set session("oRs&quot = rstemp):

Set Database = session("oRpt&quot.Database
Set Table1 = Database.Tables.Item(1)
session("oRpt&quot.DiscardSavedData
Table1.SetPrivateData 3, session("oRs&quot

session("oRpt&quot.ReadRecords

Note that there is no need for adjusting the query string.

 

[cmr0263]

FVTrainer,

Thanks for the quick response. I tried this and it still didn't give me the data. I am using Active Data, but do I need to reference the data definition file somewhere in my code?

Thanks-
CMR

 

[FVTrainer]

No, I don't think so. First, test your recordset. Is your query returning data? (Test it in the context of the asp page, using something like:

rstemp.movefirst
Do Until rstemp.eof
response.write rstemp.fields(0).value
responwe.write &quot;<br>&quot;
Loop

)

If you're getting a valid recordset, the above code should work.

by the way, if you are using the ReadRecords method as I showed above, you don't need to use the include file MoreRequiredSteps.asp (if you are using it). Or, you could remove the ReadRecords line above and replace it with an include statement pointing to MoreRequiredSteps.asp.

And I assume you are including at the bottom of your asp page a reference to one of the viewers, as in
<!-- #include file=&quot;SmartViewerActiveX.asp&quot; -->

However, having said all that, Crystal has some additional samples for CR 9; one of the differences is in the method used to assign the recordset to the report. They show it as:

oRptTable.SetDataSource oADORecordset, 3

which in your case would be:

Table1.SetDataSource session(&quot;oRs&quot, 3

You should be able to check the Err object at each stage and see if something is amiss, as well as checking the contents of your recordset. So, if it doesn't work, please provide more details regarding errors, etc.

 

[FVTrainer]

One other note (courtesy of the Crystal sample app):

'***********
'If you are using a TTX file with your report, make sure that the fields in the
'TTX file match the fields you are retrieving from the database.

'For Example, if this is your select statement:
'Select [Employee ID],[First Name], [Last Name] From Employee

'Then make sure that your TTX file looks like this as well:
'Employee ID Number 123
'First Name String 30 First Name
'Last Name String 30 Last Name

In other words, the fields returned by your query have to match in terms of number, order of the fields, and data type the items in your TTX file.

 

[cmr0263]

FVTrainer,

Thanks again.....I finally got my simple report to work the way I needed it to - now on to the complex one.

Thanks for all your help!!!
CMR