Using the Loginname control for a SQL Query

I have two pages login.aspx and default.aspx.  There's an  asp login box on the login.aspx control that logins a user on using there windows login details.  The users is then past to the default.aspx page.  This all works fine the problem I'm having is using the login name to query my sql 05 DB.  Is there a way to referance a loginname control?

something along these lines

Select *
from users
where loginname = loginname1
 
leap29Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Juan_BarreraCommented:
Hi leap29,

If the LoginName control is using the standard asp.net Membership, you can just reference the current user identity:

Dim userName as String 
If HttpContext.Current.Request.IsAuthenticated Then
   userName = = HttpContext.Current.User.Identity.Name
End If
 
And Then:
 
Select * from users
where loginname = userName

Open in new window

0
leap29Author Commented:
I've tried the above code but my query just brings in every user in my table.  Could you explain what you mean by standard asp.net membership?
0
Juan_BarreraCommented:
Sure: The LoginName works with the ASP.NET Membership provider by displaying the User Name of the current user, if authenticated in the current context (HttpContext), That's why you can reference it directly by querying the HttpContext object.
Here is more detailed information: http://msdn.microsoft.com/en-us/library/yh26yfzy.aspx

So, the string userName should be populated with the currently authenticated user, and the Select statement should return you just the rows where loginname = userName.

I'm not sure why you are getting all the rows. Is the loginname field unique?
0
OWASP: Forgery and Phishing

Learn the techniques to avoid forgery and phishing attacks and the types of attacks an application or network may face.

leap29Author Commented:
The loginname on my table is a unique field and I've added a loginname control to the page that displays the correct username.

The table I'm trying to referance in my query is a users table for our DB system.  The username for this is the same as the users windows login.  Could it be a case that the loginname is domain/username and in my query i'm just trying to referance the username?
0
leap29Author Commented:
Just realised why it's pulled in most of my users using the above query.  In my users table i've a coloumn called username.  So it's referanceing that in the query.  I've tried using the code below but get an error saying invalid coloumn name.

Dim cuserName as String 
If HttpContext.Current.Request.IsAuthenticated Then
   cuserName = = HttpContext.Current.User.Identity.Name
End If
 
 
Select * from users
where loginname = cuserName

Open in new window

0
TimCotteeHead of Software ServicesCommented:
Hello leap29,

Your actual select statement needs to be

"Select * From Users Where LoginName = '" & cuserName & "'"

Regards,

TimCottee
0
Juan_BarreraCommented:
How do you call the SQL query from the application?
The invalid column name error is because it's taking cuserName as a column instead of as a parameter value. To do that you should enclose it, as a string.

The final sql query should look like this:

Select * from users
where loginname = 'cuserName'

Open in new window

0
leap29Author Commented:
I've tried the above suggestions and both don't seem to work.  I'm trying to write my query as the select stament in a gridview.  

Is there an easier way perhap to write the loginame to a textbox and referance that in my select statment???

Dim cuserName as String 
If HttpContext.Current.Request.IsAuthenticated Then
   cuserName = = HttpContext.Current.User.Identity.Name
End If
 
 
Select * from users
where loginname = cuserName

Open in new window

0
Juan_BarreraCommented:
The problem seems to be your SQL query, so getting the value as you are doing it now should be fine.
Can you please paste the code where you set the select statement of the gridview?
0
leap29Author Commented:
I've tried that and still get nothing.  Here's the selectstament from gridview.  Could it be a problem where I've declared cusername.  I've but that in the pageload event??
SelectCommand="Select * from users
where loginname = 'cuserName'

Open in new window

0
TimCotteeHead of Software ServicesCommented:
leap29,

That is not what I suggested:

SelectCommand = "Select * From Users Where LoginName = '" & cuserName & "'"

TimCottee
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Juan_BarreraCommented:
Yeap, you should be doing as TimCottee suggested.
0
leap29Author Commented:
I'm getting a server tag error now.  Here's the code
<asp:SqlDataSource runat="server" ID="SqlDataSource1" ProviderName="System.Data.SqlClient" ConnectionString="Data Source=xxx;Initial Catalog=xxxx;User ID=xxxx;Password=xxxx" SelectCommand = "Select * From Users Where LoginName = '" & cuserName & "'"></asp:SqlDataSource>

Open in new window

0
Juan_BarreraCommented:
To set it as we are saying, you need to do it in the code-behind class, not in the .aspx markup.
Just remove the SelectCommand property from the code above, and add the following in the PageLoad event.

SqlDataSource1.SelectCommand = "Select * From Users Where LoginName = '" & cuserName & "'"

Open in new window

0
leap29Author Commented:
I think I get what you mean but still no joy.  I've posted the content of my page below.
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<%@ Page Language="VB" AutoEventWireup="false"  %>
<script>
protected void Page_Load(object sender, EventArgs e)
{
  
  Dim cusername as String 
If HttpContext.Current.Request.IsAuthenticated Then
   cusername = = HttpContext.Current.User.Identity.Name
End If
 
SqlDataSource1.SelectCommand = "Select * From Users Where LoginName = '" & cuserName & "'"
 
 
 
}
 
 
 
</script>
 
 
 
<html xmlns="http://www.w3.org/1999/xhtml">
 
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled 1</title>
</head>
 
<body>
 
<form id="form1" runat="server">
	<p>
	<asp:LoginName runat="server" id="LoginName1" />
	</p>
	<asp:GridView runat="server" id="GridView1" DataSourceID="SqlDataSource1">
	</asp:GridView>
	<asp:SqlDataSource runat="server" ID="SqlDataSource1" ProviderName="System.Data.SqlClient" ConnectionString="Data Source=xxxx;Initial Catalog=xxx;Persist Security Info=True;User ID=rdbx1;Password=xxxx" >
	</asp:SqlDataSource>
</form>
 
</body>
 
</html>

Open in new window

0
Juan_BarreraCommented:
You are mixing a VB declaration:

<%@ Page Language="VB" AutoEventWireup="false"  %>

with C# code. Just do it in code-behind, not in the page markup as a script ("F7" will get you there)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.