[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Using the Loginname control for a SQL Query

Posted on 2008-11-10
16
Medium Priority
?
833 Views
Last Modified: 2012-05-05
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
 
0
Comment
Question by:leap29
  • 7
  • 7
  • 2
16 Comments
 
LVL 20

Expert Comment

by:Juan_Barrera
ID: 22919881
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
 

Author Comment

by:leap29
ID: 22919924
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
 
LVL 20

Expert Comment

by:Juan_Barrera
ID: 22919943
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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 

Author Comment

by:leap29
ID: 22920016
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
 

Author Comment

by:leap29
ID: 22920046
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
 
LVL 43

Expert Comment

by:TimCottee
ID: 22920096
Hello leap29,

Your actual select statement needs to be

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

Regards,

TimCottee
0
 
LVL 20

Expert Comment

by:Juan_Barrera
ID: 22920103
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
 

Author Comment

by:leap29
ID: 22920128
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
 
LVL 20

Expert Comment

by:Juan_Barrera
ID: 22920154
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
 

Author Comment

by:leap29
ID: 22920179
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
 
LVL 43

Accepted Solution

by:
TimCottee earned 500 total points
ID: 22920190
leap29,

That is not what I suggested:

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

TimCottee
0
 
LVL 20

Expert Comment

by:Juan_Barrera
ID: 22920204
Yeap, you should be doing as TimCottee suggested.
0
 

Author Comment

by:leap29
ID: 22920223
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
 
LVL 20

Expert Comment

by:Juan_Barrera
ID: 22920341
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
 

Author Comment

by:leap29
ID: 22920365
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
 
LVL 20

Assisted Solution

by:Juan_Barrera
Juan_Barrera earned 500 total points
ID: 22920417
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

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article shows how to use the open source plupload control to upload multiple images. The images are resized on the client side before uploading and the upload is done in chunks. Background I had to provide a way for user…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
Suggested Courses
Course of the Month20 days, 9 hours left to enroll

868 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question