Rowset error with repeater table.

[jcoleman]

HI guys,

I'm using CR 10 to create a labeling report, which requires the user to enter a qty for the number of labels that they require. I'm using Ido's idea(great one) with the repeater table in SQL. Everything works great in CR but when I use CE 10.5 I'm getting the error 'Failed to open a rowset'. Is this because I do not have a join between my actual data table and the repeater table? I don't have any way of joining the two, because the repeater table only has a bunch of numbers in it. Any ideas, to point me in the right direction?

 

[synapsevampire]

Ido's suggestion demonstrates that the table have a join to the quantity required.

A shame you didn't bother to state your database type, this is NOT incidental information when requesting technical information.

One solution might be to create a Stored Prodedure to accomplish the same thing by creating a temp table with the quantity required and joining it to this repeater table.

Or you might use Unions, etc.

Another solution is to write a front end, such as VB.

-k

 

[jcoleman]

Thanks,

I'm using an ODBC connection to SQL, if that's what kind of connection you're asking about? The problem I have with creating the store procedure is that I don't know how many labels the user is going to use, it's going to be different everytime.
What does using Unions mean?? Sorry, I have no training in this stuff at all. I'm winging everything, that kind of puts a VB front end out of the question as well.

 

[synapsevampire]

The SP would have a parameter which prompts the user for the quanitity required.

It's fairly basic, you sound bright enough to work it out and it won't really need a temp table:

Something like:

Create procedure myproc
(
@id int = NULL,
)
As
Begin

select t1.field1, t1.field2
from
repeater t2,
(
select mytable.field1, mytable.field2, @id 'qty' from
mytable) T1,
where t2.qty = t1.qty
and
t2.qty = @id

End

-k

 

[jcoleman]

OK if I did understand this(not really yet), how would I call the stored procedure when the user runs the report, and what would happen if two or more users were running the report at the same time?

 

[jcoleman]

All right, I can not get that code to work. It's telling me that there is a syntax problem near the word 'where'. I'm really not sure even if I'm adding my tables and fields correctly but if I just do a cut and paste from the post it's still giving me syntax errors.

Any one see a problem???