Using variable from Code-behind in SQL Query, asp.net C#

Question

I am trying to use a variable from the code-behind page in a sqlDataSource WHERE statement. I am using Membership.GetUser().ProviderUserKey to obtain the UserId of the logged in user and I would like to only show record that have this UserId as a foreign key. How can I write the SQL for this?

Thanks much, Carl

Here is what I have so far:

SQL Query:

SelectCommand="SELECT [UserID], [CarID], [Make], [Model], [Year] FROM [Vehicle]"

Code-Behind

String user2 = ((Guid)Membership.GetUser().ProviderUserKey).ToString();

And I want to add WHERE UserID = user2 to the SQL Query.

Answer 1

There are many ways of interacting with a database in ASP.NET. You haven't indicated how you are executing the SQL, but this is one possible approach:

In your .ASPX file:

<asp:SqlDataSource ID="UserSource" runat="server" ConnectionString="<%$ ConnectionStrings:SQLConn %>"
  SelectCommandType="StoredProcedure" SelectCommand="dbo.NPGetUserInfo">
  <SelectParameters>
    <asp:Parameter Name="UserID" DefaultValue="0" />
  </SelectParameters>
</asp:SqlDataSource>

In your .VB code behind file (the concept is the same in C#):

Protected Sub UserSource_Selecting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceSelectingEventArgs) Handles UserSource.Selecting
    e.Command.Parameters.Item("@UserID").Value = Membership.GetUser().ProviderUserKey
End Sub

Good luck!

Answer 2

I figured it out thanks.

I created a session variable in which I stored ((Guid)Membership.GetUser().ProviderUserKey) and then used the WHERE button in the Configure Data Source Wizard to compair it the the UserID.

Thanks much for your replies.