Retreiving a value from one query to pass it to a stored procedure?

Hello! This is driving me crazy. I am having a bit of trouble, when the page loads, I collect a querystring variable from the previous page and pass it through a query to retrieve an office_id, then what I am trying to do is with the value I retrieve is to pass it to another query, which is a stored procedure, so it populates the dropdownlist.

Here is the code:

<asp:SqlDataSource ID="sqlBrokersAgentsRecips" runat="server" ConnectionString="<%$ AppSettings:connectionstring %>" ProviderName="System.Data.SqlClient" SelectCommandType="StoredProcedure" SelectCommand="callcenterdev..spGetBrokersAgentsForRecips">
<SelectParameters>
<asparameter DefaultValue="1" Name="OfficeId" />
</SelectParameters>
</asp:SqlDataSource>

<asp:SqlDataSource ID="sqlMembersOfficeId" runat="server" ConnectionString="<%$ AppSettings:connectionstring %>" ProviderName="System.Data.SqlClient" SelectCommandType="Text" SelectCommand="SELECT m.Office_Number FROM rapdata..Member m WITH(NOLOCK) WHERE Member_Number = @sesBrokerId">
<SelectParameters>
<asp:SessionParameter SessionField="sesBrokerId" Name="sesBrokerId" />
</SelectParameters>
</asp:SqlDataSource>

As you can see I manually add the paramater for the first sqldatasource, which gives me the correct data, but I want to set that parameter to a controlid, or variable if possible, but no luck.

Thank you for your help.

 

you'd be better to create a procedure in a class file to call the sp to create and return the dataset and then bind the listbox to that. It gives you much greater flexibility.

regards

FBJ