Recordset returned from Oracle view does not contain "number" fields?

[lynnspeer]

I am running Crystal Reports 8.5.

I'm trying to connect my crystal report to my Oracle database using ADO (database -> add database to report -> more data sources -> active data (ADO) -> make new connection.

In the resulting "select data source" box, I select "ADO and OLE DB" and build the connection string by choosing Oracle Provider for OLE DB, plugging in my data source, my user name and my password. After logging in, things look wonderful.

In my data explorer window, I can see my data source and the ado database icon. When I double-click the ado icon, I can fix the sql select statement so that my schema is correct. OK and closing that box pops up the Visual Linking Expert box, as expected.

The Visual Linking Expert box has all 98 of my Oracle data items.

Then life gets weird!

When I click OK on the visual linking expert box, then try to insert -> field object from my ado data source, only 59 of my Oracle data items are available. Coincidentally, all of the missing data fields are classified as "number".

I tried the "Answers By E-Mail" from Crystal Decisions and was told to use the .TTX file method, but said nothing about the missing number fields.

Note that my datasource is an Oracle "view". I just tried the report using one of the tables that is used to create the view, and all of the "number" fields are there. Unfortunately, there are multiple tables involved in the view, so using "just" the table is not an option.

Has anyone run into this problem? Am I seriously being told that number fields from Oracle don't work in ADO recordsets? Is there something that my Oracle DBA needs to do to make the "number" fields work?

 

[kenhamady]

That isn't what they are telling you.

How did you create the report?
Did you use Active Data Driver?
Are the fields in the same order as they were when the report was created?

Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[lynnspeer]

The original report was created using the same view but accessed it via OLEDB instead of ADO.

Both reports use the same view of the same database, but the OLEDB one works (i.e., the number fields are there and available for placement on the report) and the ADO/OLEDB one doesn't (the number fields as described in my previous post).

In addition, if I remove the ADO/OLEDB data connection, and add an ADO/OLEDB connection that uses the table from which the view was generated, the number fields are correct.

I'm starting to think that there is a problem with using my view vice the table(s) contributing to the view.

 

[kenhamady]

What if you create a report directly to the view (not through active data)? Do the numbers show up?

Even if you have to create a TTX to design the report, you could still pass a recordset. Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[lynnspeer]

Some history: originally, back in the dim past, the reports were written using the VB reporting software and used several tables -- cribs.charge_history and cribs.ttn. This was Before My Time.

When I came on the scene, I wrote a report that used the two tables cribs.charge_history and cribs.ttn and OLEDB. Because we were getting cartesian products, it was decided to create a view (cribs.view_charge_history) for the reports to use.

I then rewrote the original reports, still using OLEDB (the one provided by Oracle), but accessing cribs.view_charge_history view of the tables.

Now we've gone to a scheme where VB selects the recordset (and this is a very big recordset, there is a single selection criteria), and sends the recordset to Crystal. Crystal is supposed to then filter out records based on user criteria sent as parameters from the VB client and produce lovely reports.

So the original Crystal code that I wrote used the cribs.charge_history table, then was changed to use the cribs.view_charge_history view -- both using OLEDB and both working just fine.

The current code uses the cribs.view_charge_history view with ADO/OLEDB and has a problem with the fields defined as Oracle "number".

If I tweak the Crystal code so that the database access is ADO/OLEDB but uses the cribs.charge_history TABLE, everything seems to work.