Have 2 parameters, want one to filter the other

[garybug]

Hi all

I have 2 parameters, pServer & pServerRoles. I want the drop down boxes to work so that when a user selects a Server, the other drop down box will only show the roles for that Server. Obviously I have 2 tables, where there are many server roles per server. At the moment I have 2 data sets, one for each drop down box. pServer has ServerID & ServerName. pServerRole has ServerRoleID, ServerRoleName, and ServerID (as a foreign key). I have tried adding pServer as a parameter to the pServerRole data set, but it keeps coming back with 'it is the wrong type'.

Grr... Can anyone help?

Ta

g

 

[SQLBI]

Hi,

It sounds like you may have your Value and Lable elements of the parameters mixed up.

I'm assuming you're set up something like this:

Server Dataset:

SELECT DISTINCT ServerID, ServerName
FROM ServerTable
ORDER BY ServerID

Make sure your Label property of pServer is set to ServerName and the Value Property set to ServerID.

ServerRoles Dataset:

SELECT ServerRoleID, ServerRoleName
FROM ServerRoleTable
WHERE ServerID = @pServer

Also ensure that the Label property of pServerRoles set to ServerRoleName and the Value Property set to ServerRoleID.

When you change pServer, pServerRoles should be populated with values relating to the Server selectedin pServer.

Hope this helps.

Cheers,
Leigh

"If you had one shot, one opportunity, to seize everything you ever wanted, in one moment, would you capture it, or let it slip" - Eminem

 

[garybug]

Thanks Leigh - also found this, which is useful too..

http://msdn2.microsoft.com/en-us/library/aa337426.aspx

Ta much

G

 

[SQLBI]

Glad to help.

Cheers,
Leigh

"If you had one shot, one opportunity, to seize everything you ever wanted, in one moment, would you capture it, or let it slip" - Eminem