SQL Command / SP data access 1

[WannaFly]

Ok i've ben trying to get past this problem for a few days now, I went and bought CR10 and still cant get it the way i want it. Using an MSSQL 2000 DB

I have 4 complex SQL statements i need to run - each needs 4 parameters. (the same four for each). I've tried a stored proecdure, but i couldnt figure out howto pass the parameters to it with VB.NET(Can you even?) And add command works, but i have to use four different sets of the same four parameters, it cant 'reuse' the parameters?? Maybe i'm on the wrong track with this but any help would be greatly appreciated. Thanks.

 

[synapsevampire]

Here's a whitepaper that addresses passing a null to a SP parameter using SQL Server 2K:

http://support.businessobjects.com/library/kbase/articles/c2011736.asp

Just change the nothing to whatever.

As for reusing the parameters within a report, I think that you misunderstand how Crystal works. You cannot supply it with four unlinked datasources.

If you want the combination of the rows, create a Union Query, as in:

select * from table where field = 'blah'
union
select * from table where field = 'foo'

-k

 

[WannaFly]

synapsevampire,
Thanks for the link. I managed to create a union in the SQL command but i want to know howto pass it the parameters, it never prompts for them and the .properties object in VB.NET is read only - it would probably work but right now i have no clue where it is getting its parameters from.
Thanks again.

 

[synapsevampire]

The link shows how to set parms for an SP, if that's what the latest question is.

You stated that you buiolt the Union, is this within the SP? I believe that you have a Parameters button in the Add Command screen that allows you to place parameters in your SQL if that's the question (I only have CR 8,.5 here so I can't replicate the steps for you).

Otherwise I'm not sure what you're asking now...

-k

 

[WannaFly]

synapsevampire, thank you so much for your time nad help - i've gotten it to pass the SP the procedure the parameters needed but now I have a different problem.

if I put any data on the report from the SP the entire report goes blank - even the data that isnt being retrived from the SP. I can do running totals with it(i assume) but once i put them on the report all my data goes blank. I've ran SQL profiler and its passing the correct parameters and returning a valid recordset/table. I have nocount set to on.
I've read through crystals technical briefs and forums and all over google but cant figure this one out, any idea? thanks.

 

[synapsevampire]

What happens if you right click a field and select Browse Data?

Hopefully there isn't anything in the record selection formula (Check Report->Edit Record Selection->both record and group).

Try changing the connectivity type, perhaps that's the concern (are you using ODBC, or?).

Otherwise I'm not sure why it's not returning data.

-k

 

[WannaFly]

I didnt know you could right click and browse data - thanks!! The data appears when i do that, and they are all correct too.

There is something in the record selection formula - the top part of the report uses it - i'll try taking that out (but that would screw up the top portion of my report)

I'm using OLE DB (ADO) - i'll try another method if that doesnt work. Thanks.

 

[WannaFly]

I wish you could edit posts on this message board...

Looks like the record selection formula was it.
Now how can I make my report? I guess i'll try it. Thank you synapsevampire!