ODBC fetching record 2 in one record query or table

[maggiesda]

To improve the performance of our reports, we are starting to calculate some of our totals with SQL rather than use Crystals' variables or Summary functions. So we create a query to sum all the amounts in a field and then have the rpt file fetch the result. This works fine with a DAO connection. But in ODBC the amount doesn't get transferred. It appears that ODBC is reading the last record in the query, which is the new (empty) record. I even created a table and inserted the query results into the table, rendering a one record table. But the result is the same. The field is in the report footer. Does anyone know how to tell Crystal to look at the first record in the table? I tried "Previous" and "OnFirstRecord" and "if (table.autonum = 1)", all to no avail. Thanks in advance.

 

[synapsevampire]

Your methodology doesn't make sense to me.

CR considers every data source to be akin to a table: a single set of columns and values (for the most part).

onfirstrecord is the proper command for determining if a row is the first, but if you'r conditional is in the report footer, you're already past the value.

If you want to store some value of the first row, create a formula with something like:

global numbervar TheValue;
if onfirstrecord
TheValue := {Table.Value}

Now in the report footer use:

global numbervar TheValue;
TheValue

You'll have the first rows value for whatever table.field you supplied.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[maggiesda]

I'm afraid I wasn't clear in describing my situation. I'm using a table that contains one record. I want to fetch the contents of that record in the report footer. However, Crystal, via the ODBC connection, appears to be reading the new record, i.e. second record in the table. This is not a problem with a native (DAO) connection, as we are doing this in our maintenance stream. I need to know if there is any Crystal function that will allow me to move to the first record in this one record table.

Many thanks for your considering my inquiry.

 

[antzzz]

Hi,

I'm also having the same problem. My stored procedure returns only a single row as a result set but my CR 7/8.5 gives an error and states that I don't have any fields in my result set.

I'm also using ODBC in CR. It works if I use OLEDB in CR.

Have you solved your problem?

Thanks for any info.

 

[vidru]

You'd have had a better chance at getting a reponse by starting a new thread instead of piggy-backing on a thread that's a year and a half old.

That being said, the trouble you're having could have something to do with what's happening within the stored procedure. Are you using SQL Server? Oracle? Something else?

If you're using SQL Server, and you're doing some Temp table wrangling, try placing a SET NOCOUNT ON statement at the beginning of the procedure definition. Row counts can sometimes be interpreted as output from a stored procedure.

-dave