How to handle Empty Recordset

[Anser]

Hi,

I create ADO Recordset from my Harbour/FiveWin application and passes
the RecordSet to FastReport via the oFr:SetUserData(). Everything is
working fine in FastReport as expected, but for some reason if the
recordset is empty ie no records, then it errors out ie obviously the
commands oRecSet:MoveFirst() and oRecSet:MoveNext() oRecSet:MovePrev()
etc will error out if the recordset is empty. How do I handle this ?.
Whether this is to be controlled from the Harbour/FiveWin code or from
FastReport via the Pascal script ? and How ?

Here is the code that I use in FiveWin to use the recordset created
from Harbour/FiveWin to use it in FastReport

nFieldCount := oRecSet:Fields:Count()-1
for i:= 0 to nFieldCount
cFieldNames := cFieldNames + oRecSet:Fields(i):Name + iif( i <
nFieldCount ,";","" )
Next

oFr:SetUserDataSet(cName, cFieldNames,;
{|| oRecSet:MoveFirst() } ,;
{|| oRecSet:MoveNext() } ,;
{|| oRecSet:MovePrev() } ,;
{|| oRecSet:EOF() } ,;
{ | cFieldName |
oRecSet:Fields( cFieldName ):Value } )

Any help would be appreciated.

Regards
Anser

[Reinaldo Crespo]

Anser;

Hi.  I had a similar problem with other UserDatasets, such as arrays.  I have never used ADO, so I can’t exactly say how but I found the problem to be mostly with the eof() code block.  Here is a workaround that should help:

   oFr:SetUserDataSet(cName, cFieldNames,;
                          {|| oRecSet:MoveFirst() } ,;
                          {|| oRecSet:MoveNext() } ,;
                          {|| oRecSet:MovePrev() } ,;

                          {|| oRecSet:EOF() .OR. oRecSet:Bof() .OR. EMPTY( recordset )      } ,;

                          { | cFieldName |
oRecSet:Fields( cFieldName ):Value } )

The idea is to add logic other than Eof() that will return true whenever the record set is empty.

Does that help?

Reinaldo Crespo-Bazán

reinald...@gmail.com

[SOI, s.a. de c.v.]

Friends:

 

Perhaps this code can help you, I use it in a TXbrowse Class:

 

   oFr:SetUserDataSet(cName, cFieldNames,;
                          {|| IF( ! oRecSet:EOF() .AND. ! oRecSet:EOF(), oRecSet:MoveFirst(), ) } ,;
                          {|| IF( ! oRecSet:BOF() .AND. ! oRecSet:EOF(), oRecSet:MoveNext(), ) } ,;
                          {|| IF( ! oRecSet:BOF() .AND. ! oRecSet:EOF(), oRecSet:MoveLast, ) } ,;

                          {|| oRecSet:EOF() .OR. oRecSet:Bof() .OR. EMPTY( recordset )      } ,;
                          { | cFieldName |
oRecSet:Fields( cFieldName ):Value } )

 

Pls, try to adapt it to Fastreport

 

Regards

SOI, s.a. de c.v.
soisa2002 at prodigy dot net dot mx
http://www.soisa.mex.tl/
http://sqlcmd.blogspot.com/
Tel. (728) 2-85-10-10
Carpe diem quam minimum credula postero

[anserkk]

Hi Friends,

Thanks for the support. Unfortunately the problem remains as it is.

Dear Reinaldo,

As suggested by you, I tried

oFr:SetUserDataSet(cName, cFieldNames,;
                    {|| oRecSet:MoveFirst() } ,;
                    {|| oRecSet:MoveNext()  } ,;

                    {|| oRecSet:MovePrev()  } ,;
                    {|| oRecSet:EOF() .OR. oRecSet:BOF()  .OR. Empty(oRecSet)  } ,;

                            { | cFieldName | oRecSet:Fields( cFieldName ):Value } ) 

It works fine if recordset contains data, but I if the recordset contains 0 records then it errors out. The screen snapshot of the error is attached herewith. It is the ADO error that occurs when you try to traverse on an empty recordset.




Dear SOI, s.a. de c.v.,

I tried the code suggested by you also, as given below

oFr:SetUserDataSet(cName, cFieldNames,;
                     {|| IF( ! oRecSet:BOF() .AND. ! oRecSet:EOF(), oRecSet:MoveFirst(), ) } ,;
                     {|| IF( ! oRecSet:BOF() .AND. ! oRecSet:EOF(), oRecSet:MoveNext(), ) } ,;
                     {|| IF( ! oRecSet:BOF() .AND. ! oRecSet:EOF(), oRecSet:MoveLast(), ) } ,;

                     {|| oRecSet:EOF() .OR. oRecSet:Bof() .OR. EMPTY( recordset )      } ,;
                        { | cFieldName | oRecSet:Fields( cFieldName ):Value } )

After trying the code suggested by you, It will not error in case of zero record recordset, but it will not display data on FastREport even if there are records in the recordset.

I wonder why nobody till now has not experienced this problem. This is a very common situation, recordsets can be empty in some situations.

I am trying to create a very simple report in FastReport using 2 recordsets.

RecordSet1 contains only 1 record ie Employee details For Eg. Employee Code, Name, Address, Date of Birth, Joing Date etc
RecordSet2 contains different kinds of Salary Allowances for the employee in RecordSet1. This will be either multiple records or zero records. if the employee does not have any other salary allowances then the recordset will not contain any records.

Any hint on how to resolve this problem ? Any help is appreciated.

Regards

Anser

--
You received this message because you are subscribed to the Google Groups "FastReport for [x]Harbour, Alaska Xbase++, Visual FoxPro" group.
To post to this group, send email to fastreport_f...@googlegroups.com.
To unsubscribe from this group, send email to fastreport_for_x_h...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/fastreport_for_x_harbour?hl=en.

[Reinaldo Crespo]

Anserk;

I’ve never used ADO before.  Is there a way to find how many records in a record set?  Suppose there is, then you could store that value into n, and change the eof code block to:

                    {|| oRecSet:EOF() .OR.  n == 0  } ,;

Reinaldo Crespo-Bazán

reinald...@gmail.com

On Jul 2, 2012, at 5:11 AM, anserkk wrote:

Hi Friends,

Thanks for the support. Unfortunately the problem remains as it is.

Dear Reinaldo,

As suggested by you, I tried

oFr:SetUserDataSet(cName, cFieldNames,;
                    {|| oRecSet:MoveFirst() } ,;
                    {|| oRecSet:MoveNext()  } ,;
                    {|| oRecSet:MovePrev()  } ,;
                    {|| oRecSet:EOF() .OR. oRecSet:BOF()  .OR. Empty(oRecSet)  } ,;
                            { | cFieldName | oRecSet:Fields( cFieldName ):Value } ) 

It works fine if recordset contains data, but I if the recordset contains 0 records then it errors out. The screen snapshot of the error is attached herewith. It is the ADO error that occurs when you try to traverse on an empty recordset.

<FastRep Error.jpg>

[anserkk]

Dear Reinaldo,

Using ADO, I can get the Record Count via the following command.

nRecCount:=oRecSet:RecordCount()

I tried the code suggested by you. Unfortunately it errors with the same message.

nRecCount:=oRecSet:RecordCount()

oFr:SetUserDataSet(cName, cFieldNames,;
                               {|| oRecSet:MoveFirst() } ,;
                               {|| oRecSet:MoveNext() } ,;
                               {|| oRecSet:MovePrev() } ,;

                               {|| oRecSet:EOF() .OR. nRecCount == 0   } ,;

                               { | cFieldName | oRecSet:Fields( cFieldName ):Value } ) 

I modified the code as below and is working fine now. Instead of .AND. I used .OR.

oFr:SetUserDataSet(cName, cFieldNames,;
                          {|| If(!oRecSet:Bof() .OR. !oRecSet:Eof(),oRecSet:MoveFirst(),) } ,;

                          {|| oRecSet:MoveNext()  } ,;
                          {|| oRecSet:MovePrev()  } ,;

                          {|| oRecSet:EOF()  } ,;

                          { | cFieldName | oRecSet:Fields( cFieldName ):Value } ) 

Thanks & Regards

Anser

[kamleh patel]

Respected Sir,

I am Kamlesh Patel. I am connect fastreport with harbour source success but not show record with ado recordset.

Please send source file and fr3 file of fastreport.

Thanks & Regards

Kamlesh