ASP.NET 4 Visual Studio 2010. Membership Website Question

I have a asp.net 4 membership web with roles. When a user goes to change his password, I want to display that particular users Password Questiion. I added a dataset form view to the membership database and am able to achieve this by a sql query when I specify the users actual name in the query. I want to change my code to query the databased based on the user that is logged in.

Basically I want to change the part in my code that says
WHERE (aspnet_Users.UserName = 'RICK')
to something like

Where aspnet_users.username = (the currently logged in user)

I am not sure what to replace "the logged in user" with to make it equal the currently logged in user

any help would be appreciated.

SELECT aspnet_Membership.PasswordQuestion, aspnet_Membership.PasswordAnswer FROM aspnet_Membership INNER JOIN aspnet_Users ON aspnet_Membership.UserId = aspnet_Users.UserId WHERE (aspnet_Users.UserName = 'RICK')

Open in new window

bidgadgetAsked: 2011-03-09

Who is Participating?

Solutions

LVL 52

Carl TawnConnect With a Mentor Systems and Integration DeveloperCommented: 2011-03-09

Ah, ok, that sounds like you are using a SqlDataSource, which means you need to massage things a little to get it to work. In the dialog choose "Custom sql query...." then for the "select statement" paste your SQL statement as:

SELECT aspnet_Membership.PasswordQuestion, aspnet_Membership.PasswordAnswer FROM aspnet_Membership INNER JOIN aspnet_Users ON aspnet_Membership.UserId = aspnet_Users.UserId WHERE aspnet_Users.UserName = @UserName

Open in new window

When the dialog has finished you should have a SqlDataSource component on your form. Switch to "Source View" and add a SelectParameters section to the SqlDataSource. The result should look like:

    <asp:SqlDataSource ID="SqlDataSource1" runat="server"  OnSelecting="SqlDataSource1_Selecting"
        SelectCommand="SELECT aspnet_Membership.PasswordQuestion, aspnet_Membership.PasswordAnswer FROM aspnet_Membership INNER JOIN aspnet_Users ON aspnet_Membership.UserId = aspnet_Users.UserId WHERE aspnet_Users.UserName = @UserName">
        <SelectParameters>
            <asp:Parameter Name="UserName" Type="String" />
        </SelectParameters>
    </asp:SqlDataSource>

Open in new window

Finally, switch to your code-behind and add the following code block to the page:

        Protected Sub SqlDataSource1_Selecting(sender As Object, e As SqlDataSourceSelectingEventArgs)
            SqlDataSource1.SelectParameters("UserName").DefaultValue = Membership.GetUser().UserName
        End Sub

Open in new window

LVL 52

Carl TawnSystems and Integration DeveloperCommented: 2011-03-09

The following should give you that:

Membership.GetUser().Username

Open in new window

bidgadgetAuthor Commented: 2011-03-09

Thank you for your response. But how would I add that to my sql query?

LVL 52

Carl TawnSystems and Integration DeveloperCommented: 2011-03-09

Are you using inline SQL or calling a stored procedure? If it is inline you would do something like (assuming C#):

string username = Membership.GetUser().UserName;
string sql = string.Format("SELECT aspnet_Membership.PasswordQuestion, aspnet_Membership.PasswordAnswer FROM aspnet_Membership INNER JOIN aspnet_Users ON aspnet_Membership.UserId = aspnet_Users.UserId WHERE (aspnet_Users.UserName = '{0}')
", username);

Open in new window

bidgadgetAuthor Commented: 2011-03-09

I am using inline sql and with VB

LVL 52

Carl TawnSystems and Integration DeveloperCommented: 2011-03-09

In that case:

Dim username As String = Membership.GetUser().UserName
Dim sql As String = String.Format("SELECT aspnet_Membership.PasswordQuestion, aspnet_Membership.PasswordAnswer FROM aspnet_Membership INNER JOIN aspnet_Users ON aspnet_Membership.UserId = aspnet_Users.UserId WHERE (aspnet_Users.UserName = '{0}')
", username)

Open in new window

bidgadgetAuthor Commented: 2011-03-09

Ok, I will give it a try. Do I just paste the code into in the "define custum sql or stored procedure"?

LVL 52

Carl TawnSystems and Integration DeveloperCommented: 2011-03-09

Depends how you are trying to get at the data.

bidgadgetAuthor Commented: 2011-03-09

I added a list view, then made a connection string to the membership database. Then I am at the part where I am creating the custom sql query to return the question. Thank you so much

bidgadgetAuthor Commented: 2011-03-09

I am not sure if I add the sql statement in the vb or sql part

bidgadgetAuthor Commented: 2011-03-09

OK. When I put the first code in, it takes me to another screen that says it recognizes I have a parameter "username" It is asking me for a parameter source, a query string fiels and a default value

LVL 52

Carl TawnSystems and Integration DeveloperCommented: 2011-03-09

Ah, it's a little smarter than I gave it credit for :) Leave the source set to "None" and the default value empty, unless you want to give it a default.

bidgadgetAuthor Commented: 2011-03-09

Still having a little trouble, page coming up blank, but i will play around with it. Most likely I am not implementng correctly. Thaks again for all your help