How to show only records that belong to the logged in user.

Hello.

I realize that this question has been asked before, but I still can't get it to work. Below are some links that might be of help:

http://forums.asp.net/p/1159666/1913519.aspx#1913519
http://forums.asp.net/p/1161930/1924264.aspx
http://forums.asp.net/p/1116601/1732359.aspx#1732359
http://forums.asp.net/t/1104718.aspx
http://forums.asp.net/p/1096290/1655706.aspx#1655706
http://forums.asp.net/p/1110162/1707952.aspx#1707952

Basically, I need aDropDownListto displayonly projectsfor which thelogged in user is assigned asleader. The [Projects] table contains an integer ProjectId, a string ProjectName, a uniqueidentifier ProjectLeader, and other fields. Can someone help me with the SQL query and code?

* Here is the definition of the SqlDataSource: 

<asp:SqlDataSource ID="SqlDataSource5" runat="server" ConnectionString="<%$ ConnectionStrings:ASPNETDB.MDFConnectionString %>"
 SelectCommand="SELECT [ProjectId], [ProjectName] FROM [Projects] WHERE ([ProjectLeader] = @.Leader)" OnSelecting="SqlDataSource5_Selecting">
 <SelectParameters>
 <asp:Parameter Name="Leader" Type="Object" />
 </SelectParameters>
 </asp:SqlDataSource>

 * Here is the definition of the SqlDataSource5_Selecting method:

  protected void SqlDataSource5_Selecting(object sender, SqlDataSourceSelectingEventArgs e)
 {
 e.Command.Parameters("@.Leader").Value = loggedInUserId;
 }
 
where loggedInUserId is a global variable of type System.Guid. It has been evaluated in the Page_Load event to as: loggedInUserId = (System.Guid)Membership.GetUser().ProviderUserKey;
 
Now the first problem I encounter is that when I run the page, the compiler complains and says, "error CS0118: 'System.Data.Common.DbCommand.Parameters' is a 'property' but is used like a 'method'." 
The second problem is when I insert the line: SqlDataSource5.SelectParameters("Leader").DefaultValue = loggedInUserId;
in page_Load. The compiler again says, "error CS0118: 'System.Data.Common.DbCommand.Parameters' is a 'property' but is used like a 'method'."
 
I've spent a long time trying to figure it out, but could not solve it. I would appreciate it if someone can help me out. Thank you very much.
 

change it to

e.Command.Parameters["@.Leader"].Value = loggedInUserId;

Brackets not paranthesis

|||

Thank you very much for your reply. It now compiles with seemingly no problems, but the DropDownList is empty. Without the WHERE clause, it would display all projects available.

I'm probably wrong, but it could be a databind problem. Any other mistakes you could spot?

Thanks.

|||

not sure try changing the type on the parameter to the type of that param so like

<asp:Parameter Name="Leader" Type="Int32" />

Do you have access rights to the DB so that you can run the SQL Profiler?

Try that and see what query is running against the DB...

Or try debugging it to see what that param is maybe it's not getting set right and you're actually setting it to 0

|||

Thanks again, PureWeen, for trying to help. I debugged the method that determines the Id of the logged-in user, and it correctly gave me {9a6fdee6-fe40-4545-b18a-c05a278080b5}, a valid uniqueidentifier.

Now I tried looking for the SQL Profiler by searching for it in the C drive, but it seems I don't have it installed. I'm using Microsoft SQL Server Management Studio Express.

|||

I also tried to insert the statement SqlDataSource5.SelectParameters["Leader"].DefaultValue = loggedInUserId.ToString(); in the Page_Load event, but the compiler complained, "error CS0029: Cannot implicitly convert type 'System.Guid' to 'string'."

So, I changed the statement to SqlDataSource5.SelectParameters["Leader"].DefaultValue = loggedInUserId.ToString(); and the DropDownList was still empty. I wonder if that statement is needed here in Page_Load.

|||

well you don't really have to set it in Page_Load event that might be your problem... Did you debug the Selecting Event to make sure that loggedInUserID does indeed equal the GUID at that point?

Instead of setting a value why not use a accessor method like

private Guid LoggedInUserID{
get{
return (System.Guid)Membership.GetUser().ProviderUserKey;

}

}

If you just delete the parameter and don't filter the query does the dropdownlist populate?

Can you try filtering on a different field in the table to test if that works?

|||

if that doesn't work maybe post more of your code or all of it :)

|||

Sorry for the late reply; I didn't have access to the source codefor the whole day, and I still don't. I'll try to answer from memory.

Basically,I can get it to work with strings, but not Guids. So I created a newfield in the table and called it ProjectLeaderName (a string), whichwould store the UserName of the user whose Id is ProjectLeader (auniqueidentifier). It worked fine this way. But when I modify the type fromString toObject, and use ProjectLeader (the Guid) instead ofProjectLeaderName (the String), the DropDownList displays nothing. Iwonder if the type "Object" is not the correct choice.

PureWeen:

wellyou don't really have to set it in Page_Load event that might be yourproblem... Did you debug the Selecting Event to make sure thatloggedInUserID does indeed equal the GUID at that point?

Iadded this chunk of code in Page_Load while I was desperate, and as itdidn't change a thing, I commented it out. The only code is now in theSqlDataSource5_Selecting method. And yes, the global variableloggedInUserId did get the right value as the one in the aspnet_Userstable when I was in debug mode.

PureWeen:

Instead of setting a value why not use a accessor method like

private Guid LoggedInUserID{
get{
return (System.Guid)Membership.GetUser().ProviderUserKey;

}

}

That's a tough one. I think the original intent wasthat I wanted to use a variable (loggedInUserId) as part of the SQLquery, but I failed miserably. Regardless, Its value is calculated in asimple method (which is called in Page_Load) thatsets theglobal variable but does not return it. I'm not overly familiar withaccessors or properties, although it looks like a quick, yet viable change I could make.

PureWeen:

If you just delete the parameter and don't filter the query does the dropdownlist populate?

Yep. Removing the WHERE clause shows all projects ever created in the DropDownList.

PureWeen:

Can you try filtering on a different field in the table to test if that works?

Yes,it works fine on strings (ProjectName) and ints (ProjectId), but not onGuids (ProjectLeader). That's why I added the new string fieldrepresenting thename of the project leader with the stored Id(ProjectLeaderName), and filtering by that works fine. I just call theproperty User.Identity.Name to return the name of the logged-in user.

PureWeen:

if that doesn't work maybe post more of your code or all of it :)

Sure. I won't have access to the code until Monday, so I'll post more relevant parts of it then.

Once again, your help is always appreciated. Thanks a lot.

|||

Can you build your own SqlCommand and pass it a Guid that returns filtered rows?

If you can then in the selecting event just replace the sqlcommand with yours..

or
MAybe specify the type on the parameter in the selecting event

cmd.Parameters[0].DbType = System.Data.DbType.Guid;

or
Maybe just skip having the parameter specified on the SqlDataSource parameter list at all then add it yourself ...
cmd.Parameters.Add("Name", System.Data.SqlDbType.UniqueIdentifier)

the SQL Profiler is in the management studio under Tools->Sql Profiler but I'm not sure if express has it

|||

Those are some interesting ideas I might have to try out. Unfortunately, I just realized I'm not very familiar with the logic or syntax of the statements above, so I think what I have to do is do some more reading until I can effectively tackle this out. As of right now, I'm content that it's working, even though I'm only using strings. Once I'm done reading, I should be more capable of correctly implementing your suggestions. I already have a good feeling one of them may work. I'll probably be ready in a week or so, so I'll keep you informed.

Thank you very much.

|||

PureWeen:

Can you build your own SqlCommand and pass it a Guid that returns filtered rows?

If you can then in the selecting event just replace the sqlcommand with yours..

I tried to pass the SqlCommand, but for some reason it's not reading it. It may be a timing issue where I need to pass it before the Page_Load event, but it wouldn't. The DropDownList was still empty.

I did some more reading and searching, and one of the sources claimed that all I needed to do was delete the Object type attribute, simply because Guids are... special.

So what I had to do is simply replace<asp:Parameter Name="LeaderId"Type="Object"/> with<asp:Parameter Name="LeaderId"/>, and it worked fine.

PureWeen:

the SQL Profiler is in the management studio under Tools->Sql Profiler but I'm not sure if express has it

Too bad I can't find it here in the Express Edition.

So as a quick recap, the solution was to remove Type="Object" from the parameter, and it worked great.

Thank you, PureWeen, for setting me on the right track all that time. I appreciate your selfless efforts all that time.

Cheers.