Populate dropdownlist using stored proc based on user logged in
<p>I'm just learning asp.net so I may be wrong on how I'm doing this so let me know if there is a better way.</p>

<p>I have a "portal", where a user logs in and then they have a dropdownlist of all their sites they manage.</p>

<p>I want to populate the dropdown box using a stored proc I created which is based on the user logged in.</p>

<p>I used the built in createuserWizard which stores the users in the database. I created another table that has all the sites and a 3rd table to link the sites and users together.</p>

<p>Here is the dropdown list</p>

<pre><code>&lt;code&gt;

    &lt;asp:DropDownList ID="DropDownListAClient" runat="server" 
         OnSelectedIndexChanged="DropDownList1_SelectedIndexChanged" 
         DataSourceID="SqlDataSourceSitePicker" DataTextField="SiteName" 
         DataValueField="SiteName"&gt;
    &lt;/asp:DropDownList&gt;

    &lt;asp:SqlDataSource ID="SqlDataSourceSitePicker" runat="server" 
         ConnectionString="&lt;%$ ConnectionStrings:MainAppDB %&gt;"
         SelectCommand="portal_SitePicker" SelectCommandType="StoredProcedure"&gt;

         &lt;SelectParameters&gt;
               &lt;asp:Parameter Name="portaluserid" Type="String" /&gt;
         &lt;/SelectParameters&gt;

    &lt;/asp:SqlDataSource&gt;

&lt;/code&gt;
</code></pre>

<p>Here is the code for the stored procedure</p>

<pre><code>&lt;code&gt;
ALTER PROCEDURE [dbo].[portal_SitePicker]
    -- Add the parameters for the stored procedure here
    @portaluserid varchar 
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    SELECT SiteName
FROM dbo.aspnet_UserMap, dbo.Sites 
WHERE dbo.Sites.SiteID = dbo.aspnet_UserMap.SiteID
and dbo.aspnet_UserMap.UserID = @portaluserid
END
&lt;/code&gt;
</code></pre>

<p>If I test the stored proc it pulls the correct sites, but I'm not sure how to get the userid value from the current logged in user.</p>

<p>Any suggestions?</p>
