Show Records in Gridview for Current Logged On User

[djs45uk]

Hi all

I'm using the following data source to populate a gridview. It is supposed to only return the records for the current user who is logged in.

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:StPaulsDatabase %>"
SelectCommand="SELECT [observeTeacher], [observeBy], [observeReason], [observeFocus], [observeDate] FROM [Observations] WHERE ([observeBy] = @observeBy)">
                     
<SelectParameters>
  <asp:ProfileParameter Name="observeBy" PropertyName="UserName" Type="String" />
</SelectParameters>

</asp:SqlDataSource>

Whilst it doesnt return an error message to say there's a problem with the statement, it isnt returning any results either. There are records for this user in the database.

Can anyone please tell me where I am going wrong?

Many thanks

Daniel

 

[ca8msm]

At a guess, I'd say that the parameter isn't being set correctly. You can check this by running SQL Profiler and seeing what query is being run against your database.


____________________________________________________________
Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]

Need help finding an answer? Try the Search Facility or read FAQ222-2244.

 

[djs45uk]

Thanks ca8msm! Knowing me, I'm probably not setting the parameter correctly. I've searched and searched but cant find the way to do it.

PS I cant see SQL Profiler - I'm guessing this comes with the full version of SQL. I'm using the Express interface to connect to my hosted SQL database remotely.

 

[jmeckley]

I don't think you can automatically link to the user name using declarative syntax. you still need to assign the value in code behind.

protected void Page_Load(object sender, EventArgs e)
{
   if (!Page.IsPostback)
   {
      SqlDataSource1.Parameters[0].Value = User.Name;
   }
}

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[djs45uk]

Thank you Jason - I've tried your bit of code (I'm using VB though so its a bit different):

<script runat="server">
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)
        If Not Page.IsPostback Then
            SqlDataSource1.Parameters(0).Value = User.Identity.Name
        End If
    End Sub
</script>


                 <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:StPaulsDatabase %>"
                     SelectCommand="SELECT [observeTeacher], [observeBy], [observeYear], [observePunctual] FROM [Observations] WHERE ([observeBy] = @observeBy)">
                     <SelectParameters>
                         <asp:Parameter Name="observeBy" Type="String" />
                     </SelectParameters>
                 </asp:SqlDataSource>

I am getting an error though on the SqlDataSource1 part of your bit of code. It says the name is not declared.

Have I added it in the way you expected?

Thanks

D

 

[jmeckley]

if [tt]name[/tt] is not declared then the Indentity object may be null. set a break point in the code behind and check the [tt]User.Identity[/tt] value. if this is null then authentication may not be configured properly.

if this is the case, then your orginal code may work without the codebehind. once authentication is working.

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[djs45uk]

Thank you again Jason!

Unfortunately Tek-tips is blocked at my work (don't know why) so I couldn't read your post but I cracked it after a day of fiddling.

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)
        SqlDataSource1.SelectParameters(0).DefaultValue = User.Identity.Name
        SqlDataSource2.SelectParameters(0).DefaultValue = User.Identity.Name
    End Sub

The above is working for me now. Why it didn't work before I guess we will never know.

I appreciate all your help.

Dan