Displaying data based on user logged in

Imar · January 27th, 2009, 03:31 AM

Hi there,

As I hinted in my previous message, when using the SqlDataSource control you need to handle the Selecting event (that fires before the control goes out to the database to get the data) and pass the current user's name to the correct parameter.

To make this work, first change your SelectParameter. The user name is not stored directly in a cookie (it's encrypted) so you need to use a generic Parameter instead:

Code:

<asp:SqlDataSource ... Other settings here
    SelectCommand="SELECT Whatever FROM [SomeTable] WHERE ([UserName] LIKE '%' + @UserName + '%')">
  <SelectParameters>
    <asp:Parameter DefaultValue="" Name="UserName" Type="String" />
  </SelectParameters>
</asp:SqlDataSource>

This sets up a Parameter but doesn't tell it where to get its data from. This should instead be done in Code Behind:

Code:

 
Protected Sub SqlDataSource1_Selecting(ByVal sender As Object, _
           ByVal e As System.Web.UI.WebControls.SqlDataSourceSelectingEventArgs) _
           Handles SqlDataSource1.Selecting
  Dim userName As String = Context.User.Identity.Name
  e.Command.Parameters("@UserName").Value = userName
End Sub

When this code is triggered by the SqlDataSource, the user name is retrieved from the Identity and assigned to the UserName parameter. This value is then forwarded to the database where it's used to select only those records that belong to the current user.

BTW: in your SQL statement you're using LIKE which is a bit dangerous. It does wild card searching, so for example, for a user called Imar it will also return records that belong to users called Primary, Imario and so on....

If you want to do yourself a favor, consider getting a copy of my book Beginning ASP.NET 3.5 in C# and VB.NET. The book talks about stuff like this and a lot more....
http://www.wrox.com/WileyCDA/WroxTit...47018759X.html

Cheers,

Imar

Lee Dumond · January 27th, 2009, 11:42 AM

Yes, Imar's solution is what you want. You have to set the parameter in code, because you can only define the parameter's DefaultValue as a literal value.

Also, as suggested, do NOT use LIKE. Use an equals sign instead:

Code:

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ProdConnectionString %>"
   SelectCommand="SELECT [UserID], [UserName], [LastName] FROM [TblUsers] WHERE ([UserName] = @UserName)"
   OnSelecting="SqlDataSource1_Selecting">
   <SelectParameters>
      <asp:Parameter Name="UserName" Type="String" />
   </SelectParameters>
</asp:SqlDataSource>

Spider · January 27th, 2009, 01:12 PM

Here is the code I am using:

Code:

<asp:LoginNameID="LoginName1"runat="server"/>
<br/>
<asp:GridViewID="GridView1"runat="server"AutoGenerateColumns="False"DataSourceID="SqlDataSource1">
<Columns>
<asp:BoundFieldDataField="UserId"HeaderText="UserId"SortExpression="UserId"/>
<asp:BoundFieldDataField="UserName"HeaderText="UserName"SortExpression="UserName"/>
</Columns>
</asp:GridView>
<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:ASPNETDBConnectionString %>"SelectCommand="SELECT [UserId], [UserName] FROM [vw_aspnet_Users] WHERE ([UserName] = @UserName)">
<SelectParameters>
<asp:ParameterDefaultValue=""Name="UserName"Type="String"/>
</SelectParameters>
</asp:SqlDataSource>

and this is the code behind:

Code:

PartialPublicClass _Default
Inherits System.Web.UI.Page
ProtectedSub SqlDataSource1_Selecting(ByVal sender AsObject, _
ByVal e As System.Web.UI.WebControls.SqlDataSourceSelectingEventArgs) _
Handles SqlDataSource1.Selecting
Dim userName AsString = Context.User.Identity.Name
e.Command.Parameters("@UserName").Value = userName
EndSub
EndClass

Thanks for taking the time to look at this

Lee Dumond · January 27th, 2009, 01:37 PM

Spider,

What's wrong with this? It looks good to me. What trouble are you having with it?

My only suggestion, right off the top of my head, is that I probably wouldn't use a GridView for a query that only returns a single record. Better to use a DetailsView or FormView for that.

Spider · January 27th, 2009, 03:25 PM

Lee,
I changed it to a form view, and for whatever reason - it just worked!

Thanks to both you and Imar for your time over the last couple of days. I am sure I might need to pick your brains again in the near future!

mashype · January 27th, 2009, 11:39 PM

Are the code examples provided in VB or in C#?

mashype · January 28th, 2009, 04:56 PM

I have tried Spiders code and still doesn't seem to work. I assume the code behind is going on the same page that the form view is going on? Also, could someone help me get this converted to C# language so I can see if I can mirror this?

Lee Dumond · January 28th, 2009, 05:15 PM

This is getting somewhat confusing, as there are two different users asking stuff in this same thread.

Okay, this is for Mashype. Based on the code example you did posted yesterday. I am using a DetailsView here. Here is the entire code sample.

Page:

Code:

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
   <title></title>
</head>
<body>
   <form id="form1" runat="server">
      <div>
         <asp:DetailsView ID="DetailsView1" runat="server" AutoGenerateRows="False" DataKeyNames="UserID"
            DataSourceID="SqlDataSource1" Height="50px" Width="125px">
            <Fields>
               <asp:BoundField DataField="UserID" HeaderText="UserID" InsertVisible="False" ReadOnly="True"
                  SortExpression="UserID" />
               <asp:BoundField DataField="UserName" HeaderText="UserName" SortExpression="UserName" />
               <asp:BoundField DataField="LastName" HeaderText="LastName" SortExpression="LastName" />
            </Fields>
         </asp:DetailsView>
         <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ProdConnectionString %>"
            SelectCommand="SELECT [UserID], [UserName], [LastName] FROM [TblUsers] WHERE ([UserName] = @UserName)"
            OnSelecting="SqlDataSource1_Selecting">
            <SelectParameters>
               <asp:Parameter Name="UserName" Type="String" />
            </SelectParameters>
         </asp:SqlDataSource>
      </div>
   </form>
</body>
</html>

Code-behind:

Code:

using System.Web.UI.WebControls;

public partial class _Default : System.Web.UI.Page
{
   protected void SqlDataSource1_Selecting(object sender, SqlDataSourceSelectingEventArgs e)
   {
      e.Command.Parameters["@UserName"].Value = Context.User.Identity.Name;
   }
}

mashype · January 31st, 2009, 12:27 PM

Hi guys,

I was able to get the code Lee posted to work without problem, but it is still not displaying my user. Is there a function that will display the variable string I creade in the code behind file on the aspx page just to confirm what I am querying against?

Lee Dumond · January 31st, 2009, 01:27 PM

Quote:

Originally Posted by mashype

Hi guys,

I was able to get the code Lee posted to work without problem, but it is still not displaying my user.

The only conditions I can think of that would not create a record to display is if one of the following were true:

You are not logged in, making Context.User.Identity.Name an empty string
You are logged in, but the user name you are logged in under does not exist in TblUsers.

You should check both of these conditions first.