Report with Stored Procedure as source

[birgitd]

Hi,

I have a stored procedure that accepts 3 input variables (start_dt, end_dt and password). I would like the user to enter the first 2 (start_dt and end_dt), but I would like the system to look up the third one (from a file on the local hard drive) and transparently pass it to the stored procedure.

The password is to decrypt data in the database, and so I (obviously) don't want to hard-code it in the application or the stored procedure. We also don't want to store it on the database server. Additionally, we don't give the password to users (since they'll put it on a post-it note on their monitor). Basically, we bury a text file in the depths of their hard-drive (which they don't know about), and pull the password from there when we need it).

So, if I put the stored procedure as the report record source, then the user gets pop-up boxes for all three variables, but if I execute the stored procedure using the "report.activate" VB code, it doesn't see any of my fields (this is where you declare, bind, append, and set variables and execute procedure).

Any suggestions as to how I can accomplish this would be greatly appreciated!

Thanks!
Birgit

 

[cmmrfrds]

Put the 3 parameters in the "Input Parameter" property on the data tab of the report.

You could make 3 public variables that filled in before executing the Report.

@parm1=var1,@parm2=var2,@parm3=var3

 

[birgitd]

Have you gotten this to work? I tried it (loaded the variables with input boxes on Report_Activate), but it doesn't seem to recognize the input parameters were referring to variables. It gives me the pop-up boxes for the values like normal.

Here is my Input Parameter data:
@start_dt = start_dt, @end_dt = end_dt, @password = passwd

Thanks again!
Birgit

 

[neecie]

I had the same problem. I use a sub procedure to input the variables into textboxes on a hidden form, then reference the form fields for the parameters.

 

[cmmrfrds]

I don't have an example with me at work, but I believe I may have used a public function to return the variable. It will work, just need to get the right combination.


public aparm1 as string
Public Parm1() as String
Parm1 = aparm1
End Function

Input Parameter

@parm1=Parm1(),etc...