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:
Who is Participating?
 
Carl TawnConnect With a Mentor Systems and Integration DeveloperCommented:
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

0
 
Carl TawnSystems and Integration DeveloperCommented:
The following should give you that:
Membership.GetUser().Username

Open in new window

0
 
bidgadgetAuthor Commented:
Thank you for your response.  But how would I add that to my sql query?
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
Carl TawnSystems and Integration DeveloperCommented:
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

0
 
bidgadgetAuthor Commented:
I am using inline sql and with VB
0
 
Carl TawnSystems and Integration DeveloperCommented:
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

0
 
bidgadgetAuthor Commented:
Ok, I will give it a try.  Do I just paste the code into in the "define custum sql or stored procedure"?
0
 
Carl TawnSystems and Integration DeveloperCommented:
Depends how you are trying to get at the data.
0
 
bidgadgetAuthor Commented:
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
0
 
bidgadgetAuthor Commented:
I am not sure if I add the sql statement in the vb or sql part
0
 
bidgadgetAuthor Commented:
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
0
 
Carl TawnSystems and Integration DeveloperCommented:
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.
0
 
bidgadgetAuthor Commented:
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
0
 
bidgadgetAuthor Commented:
thanks again
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.