Generating Reports using Stored Procedures for Visual Basic Front Ends

I have been working on this problem for a while now. I have finally found out how to make it work. The problem with Crystal Reports is that it cannot generate a report with a recordcount of -1.

How is it possible to have a recordcount of -1 you might ask? When using a recordset, you can choose what cursor type you would like. The options are Dynamic, ForwardOnly, Keyset, and Static. Each of these cursor types are good for certain things but I'd like to talk about the ForwardOnly cursor type. ForwardOnly cursor types are fast, very good for retrieving data, However as the name might suggest, The cursor is 'forward only', so you can't navigate very well with this cursortype, you can only go forward. This is the reason that you receive -1 as a recordcount. To get a recordcount the cursor has to run all the way through the recordset to get the count and then go back to the top so you can navigate the recordset, but with a ForwardOnly cursor you cant do that.

There is a way to get around this however. The recordset Object's cursor location is Server Side by default. This is a good thing because you typically want the server to do all the work. But if you change the cursor type from Server Side to Client Side, you can get a record count from a forward only RecordSet. I can't exactly explain why this is so, other than I think a recordset generated on a Server will not return the recordcount, but if you are generating the recordset on your own computer, it is easier to calculate a recordcount.

So what does this have to do with Stored Procedures? Well, Stored Procedures only return ForwardOnly recordsets. So if you want to generate a report using a Stored Procedure, the cursor type needs to be Client Side. I hope this helps anyone who needs it.

Good Luck!