DAP - Requery using storedprocedure with parameters

[FancyPrairie]

I have created a Data Access Page containing 3 different groups. Looks something like this:

+ Company1
+ Company2
+ Department1
+ Employee1
+ Employee2
...
+ Department2
+ Company3

The recordsource for the 3 group (Employee) is based on a stored procedure that accepts parameters. The BeforeInitialBind event of the MSODSC object contains the following code:

msodsc.RecordsetDefs("spEmp").ParameterValues.Add "@myValue", 1

This works fine. However, the user is allowed to click a button that would require me to change the parameterValue. But I have not been successfull at resetting the ParameterValue after it is initally set. Using debug it appears that it has been set, but the data does not reflect it. It looks like it needs to be requeried. But the group is not visible (expanded) when the parameter is set, so I cannot issue the MSODSC.DataPages(2).requery command.

How would I go about resetting the parameter and requery the page?

 

[fneily]

Unfortunately I have Access 2000 and it doesn't have BeforeInitialBind for MSODSC. But I did see that this event has a SetRootRecordset method to reset the bind to a new recordset. Maybe you could check that out.
Also, did you look at:

http://msdn.microsoft.com/library/d...c2k2/html/odc_customfilteringandsearching.asp

Don't know if that would help.
I tried just refiltering from the other group levels, but to no avail.
Maybe you could expand the section first then requery somehow.
Obviously, I'm stuck.

 

[FancyPrairie]

fneily,

I really appreciate your help in the past and am desparate to get some help on this one. It's a show stopper.

From what I've read the SetRootRecordset works for disconnected recordsets, which won't help. But, regardless, I've made a little headway.

It appears that if my stored procedure only contains 1 parameter, everything works fine. However, if I have 2 or more parameters, the following code executes ok and the parametervalues are updated (verified this via debug), but I'm still prompted to enter the 2 parameters (not prompted when only 1 parameter is declared)

(Note that since 2000 doesn't have BeforeIntialBind, you need to place the code right before the closing head tag.)

<Script language=vbscript>
document.MSODSC.RecordsetDefs("MyQuery").ParameterValues.Add "@FirstArg", 7
document.MSODSC.RecordsetDefs("MyQuery").ParameterValues.Add "@SecondArg", 2
</Script>
</Head>

Also, if I place a button on the page and add the following code to the onclick event, I also get prompted.

<Script language=vbscript>
Sub Command0_onclick()
    document.MSODSC.RecordsetDefs("MyQuery").ParameterValues.Add "@FirstArg", 7
    document.MSODSC.RecordsetDefs("MyQuery").ParameterValues.Add "@SecondArg", 2
    MSODSC.Refresh
end sub
</Script>

Can you duplicate this problem? I have spent over 20 hours trying to get this to work. The first part of my time was spent trying to figure out why Access can't join 2 groups together if the 2nd group contains parameters (see example below). Finally discovered that the 2 parameters was causing the problem. I have found a clumsy work around to this problem. But am still stuck on the 1st problem mentined above.

PLEASE help. I'm desparate. I have not found anything on the net that resolves this issue. However, have seen a couple posts where people asked similiar questions, but no results. Also, microsoft shows examples of using 2 parameters, but I can't get them to work.

Example of Access failing to join the 2 storedprocedures.

+ Facility (contains no parametervalues)
+ Dept (contains 2 parametervalues)

 

[FancyPrairie]

My Solution:

I found a site where they had created a page in Access 2000 that referenced storedprocedures containing 2 or more parameters. This page no longer worked when they upgraded to 2003 (there was no follow-up solution to their problem).

I believe the problem is due to a bug in the Office Web Components (owc10.dll) (See thread181-1038282 for an explanation of which OWC dll's are installed with the different versions of office.)

Problem 1:
Access 2003 will not allow joining 2 groups if at least one of the groups is based on a storedprocedure that contains 2 or more parameters.

Problem 2:
IE prompts the user for the parameter values even though the code populates the parameter values. (This is only true if the storedprocedure contains 2 or more parameters).

Work-around to Problems 1 and 2
Change the storedprocedures that contains 2 or more parameters so that they accept only 1 parameter. This parameter will contain the values for all of the other parameters, with each value separated by some deliminator.

For example, instead of declaring the procedure like this:

CREATE PROCEDURE MyProcedure(@CompanyID int, @DeptID int)

Declare it like this:

CREATE PROCEDURE MyProcedure(@CompDept, nvarchar(20))

Then pass the parameter values like this:

<SCRIPT language=vbscript event=BeforeInitialBind(msodsc) for=MSODSC>
<!--

document.MSODSC.RecordsetDefs("MyProcedure").ParameterValues.Add "@CompDept", "0;1"

-->
</SCRIPT>

Note that your procedure will need to parse out the 2 values.

 

[FancyPrairie]

OK, so maybe there isn't a bug in the Office Web Components. But it didn't work as I expected. Here's what I discovered (or at least what I believe at this point to be true)...

Each of the parameters must be related to a field within the storedprocedure (or table) you're relating it to. However, this still doesn't work for me because my parameters are being used to drive "Case When...Then...Else" statements. Something like what's shown below.

(Note that the @Group parameter is a variable unrelated to the tables. Rather it's a variable that tells me which group a user belongs to and, as such, determines what will be displayed in a field. That is, if the user has not been identified, then asterisks will appear in certain field. If the user is logged in to the page, then they will see asterisks in those fields in all records but their own. If the user is a manager, then they will see asterisk in those fields for all records except those for which they are the manager of. User belonging to the Admin group will see the values in all records and not asterisks.)

CREATE PROCEDURE MyProcedure(@Group int, @lngDeptID int, @lngEmpID int)
AS SELECT ...,

CASE WHEN (@Group = 0) or ((@Group = 1) and (lngEmpID <> @lngEmpID)) or ((@Group = 2) and ((lngDeptID <> @lngDeptID)  or (lngDeptID IS NULL)))
THEN
    '********'
ELSE
    strHomePhone
END AS  fmtstrHomePhone,
...