locked
WHERE (Username = @Username) RRS feed

  • Question

  • User371613159 posted

    I thought this would be an easy one for me but it has been whopping my butt for months now.

    I simply want to query data in a table were the column username equals the currently logged in user. Back in the day using frontpage and sharepoint designer, I could simply create a hidden field and pull <%= Request.ServerVariables("LOGON_USER") %>. How can I accomplish this with ASP.NET using visual studio express.

    This is logically what I'm trying to do:

    <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ScorecardsSQL_testConnectionString %>" SelectCommand="SELECT Name_ID, First_Name, Last_Name, Username, Active FROM Producer WHERE (Username = @Username)">

    <SelectParameters>

    <asp:ProfileParameter Name="Username" PropertyName="UserName" Type="Object" />

    </SelectParameters>

    </asp:SqlDataSource>

    Thursday, January 8, 2015 5:10 PM

Answers

  • User-271186128 posted

    Hi raymondm4,


    getting  'Parameters' is not a member of 'String'.

    According to your description, I create a sample using the following code. It worked well on my side. And, I'm not meeting the above issue. So, will you please explain more details about the above issue? If possible I suggest you could post some relevant code, so that we could quickly help you solve your problem.

            <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="Id">
                <Columns>
                    <asp:BoundField DataField="Id" HeaderText="Id" ReadOnly="True" SortExpression="Id" />
                    <asp:BoundField DataField="Name" HeaderText="Name" SortExpression="Name" />
                    <asp:BoundField DataField="Gender" HeaderText="Gender" SortExpression="Gender" />
                </Columns>
            </asp:GridView>
            <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:DefaultConnection %>" SelectCommand="SELECT [Id], [Name], [Gender] FROM [Test] where Id =@Id">
            </asp:SqlDataSource>
    
    Code Behind
            protected void Page_Load(object sender, EventArgs e)
            {
                SqlDataSource1.SelectParameters.Add("Id", DbType.Int32, "1001");
    
                GridView1.DataSourceID="SqlDataSource1";
                GridView1.DataBind();
            }

    Besides, here is a similar thread with the similar issue, you could refer to it.

    https://social.msdn.microsoft.com/Forums/vstudio/en-US/a6ecf173-9eb6-4a18-b1fa-691ba81c5757/parse-is-not-a-member-of-string?forum=vbgeneral

    Best Regards,
    Dillion

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, January 9, 2015 5:03 AM
  • User281315223 posted

    raymondm4

    I keep getting  'Parameters' is not a member of 'String'.
    I'm completely green with this. I tried what they had on the other page but I could not get it to work either.

    This was a typo on my behalf (I wasn't around a development environment at the time)

    It should be :

    SqlDataSource1.SelectParameters.Add("@UserName", User.Identity.Name);

    Since you are using Visual Basic however, your code-behind might look something like this :

    Public Class admin_user
        Inherits System.Web.UI.Page
    
        Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
            ' When your page opens, set your datasource parameter '
            SqlDataSource1.SelectParameters.Add("@UserName", User.Identity.Name)
        End Sub
    
    End Class

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, January 9, 2015 9:27 AM

All replies

  • User281315223 posted

    If you wanted to pass in the username of the current user, you could consider just setting the parameter within your code-behind when your page is loaded?

    SqlDataSource1.SelectCommand.Parameters.AddWithValue("@UserName", User.Identity.Name);

    This would relieve you from having to create an explicit SelectParameter within your markup :

    <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ScorecardsSQL_testConnectionString %>" SelectCommand="SELECT Name_ID, First_Name, Last_Name, Username, Active FROM Producer WHERE (Username = @Username)">
    </asp:SqlDataSource>

    If you aren't a fan of that approach, you could try using a recommendation mentioned in this related Stack Overflow discussion, which focuses on building a custom parameter type and using it.

    Thursday, January 8, 2015 5:42 PM
  • User-1199946673 posted

    There's no build in datasource parameter to pass the username. You need to do that in code behind as shown by Rion Williams, or you can create your own custom parameter:

    http://weblogs.asp.net/anasghanem/creating-custom-parameters-for-your-data-sources

    Thursday, January 8, 2015 7:15 PM
  • User371613159 posted

    I keep getting  'Parameters' is not a member of 'String'.
    I'm completely green with this. I tried what they had on the other page but I could not get it to work either.

    Thursday, January 8, 2015 7:19 PM
  • User371613159 posted

    Thanks Hans,

    I will try the link. And I will continue to try to get the code behind to work.

    Thursday, January 8, 2015 7:21 PM
  • User-271186128 posted

    Hi raymondm4,


    getting  'Parameters' is not a member of 'String'.

    According to your description, I create a sample using the following code. It worked well on my side. And, I'm not meeting the above issue. So, will you please explain more details about the above issue? If possible I suggest you could post some relevant code, so that we could quickly help you solve your problem.

            <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="Id">
                <Columns>
                    <asp:BoundField DataField="Id" HeaderText="Id" ReadOnly="True" SortExpression="Id" />
                    <asp:BoundField DataField="Name" HeaderText="Name" SortExpression="Name" />
                    <asp:BoundField DataField="Gender" HeaderText="Gender" SortExpression="Gender" />
                </Columns>
            </asp:GridView>
            <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:DefaultConnection %>" SelectCommand="SELECT [Id], [Name], [Gender] FROM [Test] where Id =@Id">
            </asp:SqlDataSource>
    
    Code Behind
            protected void Page_Load(object sender, EventArgs e)
            {
                SqlDataSource1.SelectParameters.Add("Id", DbType.Int32, "1001");
    
                GridView1.DataSourceID="SqlDataSource1";
                GridView1.DataBind();
            }

    Besides, here is a similar thread with the similar issue, you could refer to it.

    https://social.msdn.microsoft.com/Forums/vstudio/en-US/a6ecf173-9eb6-4a18-b1fa-691ba81c5757/parse-is-not-a-member-of-string?forum=vbgeneral

    Best Regards,
    Dillion

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, January 9, 2015 5:03 AM
  • User371613159 posted

    This is what I'm trying to do (I'm sure I'm doing something very wrong here). I simply want to be able to run a query that take the username of who is currently viewing the page and filters the list to just show him/her. The end result is that I want to filter all data on the site based on the office id of who is viewing the page. And have a page where users can edit, add, and delete data only pertaining to them.

    Zhi, I tried using the code but the code behind give me multiple errors. I'm not familiar with using the code behind so I simply pasting it before "end Class".

    <%@ Page Language="VB" AutoEventWireup="false" CodeFile="user.aspx.vb" Inherits="admin_user" %>
    
    <!DOCTYPE html>
    
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head runat="server">
        <title></title>
    </head>
    <body>
        <form id="form1" runat="server">
        <div>
        
        </div>
            <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="Name_ID" DataSourceID="SqlDataSource1">
                <Columns>
                    <asp:BoundField DataField="Name_ID" HeaderText="Name_ID" InsertVisible="False" ReadOnly="True" SortExpression="Name_ID" />
                    <asp:BoundField DataField="First_Name" HeaderText="First_Name" SortExpression="First_Name" />
                    <asp:BoundField DataField="Last_Name" HeaderText="Last_Name" SortExpression="Last_Name" />
                    <asp:BoundField DataField="Username" HeaderText="Username" SortExpression="Username" />
                    <asp:CheckBoxField DataField="Active" HeaderText="Active" SortExpression="Active" />
                    <asp:BoundField DataField="Officeid" HeaderText="Officeid" SortExpression="Officeid" />
                </Columns>
            </asp:GridView>
            <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ScorecardsSQLConnectionString %>" SelectCommand="SELECT * FROM [Producer] WHERE ([Username] = @Username2)">
                <SelectParameters>
                    <asp:ProfileParameter Name="Username2" PropertyName="username" Type="String" />
                </SelectParameters>
            </asp:SqlDataSource>
        </form>
    </body>
    </html>
    
    backend has nothing (starting new)
    
    Partial Class admin_user
        Inherits System.Web.UI.Page
    
    End Class

    Friday, January 9, 2015 8:43 AM
  • User281315223 posted

    raymondm4

    I keep getting  'Parameters' is not a member of 'String'.
    I'm completely green with this. I tried what they had on the other page but I could not get it to work either.

    This was a typo on my behalf (I wasn't around a development environment at the time)

    It should be :

    SqlDataSource1.SelectParameters.Add("@UserName", User.Identity.Name);

    Since you are using Visual Basic however, your code-behind might look something like this :

    Public Class admin_user
        Inherits System.Web.UI.Page
    
        Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
            ' When your page opens, set your datasource parameter '
            SqlDataSource1.SelectParameters.Add("@UserName", User.Identity.Name)
        End Sub
    
    End Class

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, January 9, 2015 9:27 AM