Asked by:
LastLoginDate (2 hours off)

Question
-
User-1786737777 posted
Hi!
I was wondering if you people also find the LastLoginDate to be 2 hours off in the aspnet.membership table. It is a little bit annoying as I show the name and time of the last 5 logins.
Regards,
Tommy
Wednesday, May 17, 2006 8:38 AM
All replies
-
User248662050 posted
I think it puts the GMT time. We may have to do the calculation/conversion ourselves.Wednesday, May 17, 2006 9:30 AM -
User-1853252149 posted
I was wondering if you people also find the LastLoginDate to be 2 hours off in the aspnet.membership table. It is a little bit annoying as I show the name and time of the last 5 logins.
Mine's five hours off. If you want to correct this, move two time zones west. :)
It's GMT, you need to do an offset for the correct time. This could be done before saving the date to the database, or in the retrieval for display.
Jeff
Wednesday, May 17, 2006 9:35 AM -
User248662050 posted
I did a test. I think it is putting the UTC time rather than the system timeWednesday, May 17, 2006 9:47 AM -
User654902800 posted
>move two time zones west. :) ha ha!
You can't change the date from UTC before it's saved in this case, as different login controls will set this date and expect it to be in UTC.
What you need to do is change it to local time when you display it, which is easy:
dim utcdate as date
... get the date from database
localdate = utcdate.ToLocalTime()Wednesday, May 17, 2006 1:38 PM -
User-1786737777 posted
Thank you people for your help. I will implement it tomorrow. Strange thing it does not use the server time.Wednesday, May 17, 2006 2:53 PM -
User-1786737777 posted
Hi again!
I got the sql for it:
select
dateadd(hh,2,lastlogindate) from dbo.aspnet_membershipwhere
email ='abc123@cnn.com'/Tommy
Wednesday, May 17, 2006 3:46 PM -
User654902800 posted
Hiya Tommy,
that will work, but only if you live in Sweden and there's no daylight savings time :)
Where I live, sometimes we are 7 hours different from UTC, sometimes 8.
The date function ToLocalTime() takes this into account, so if you use that, you can move your software to New York and it will still work correctly. That might never be an issue for you, but I think it's a good practice to try to write code that's going to run properly regardless of where it's hosted.
I think this is why the date is stored as UTC in the first place, because it means you *can* write code that converts it to local time (not just server local time either, maybe you'll eventually want to time to appear in the user's local time, so if I browse to your site on the other side of the world, the time I see is *my* local time, not Swedish time).
Wednesday, May 17, 2006 4:32 PM -
User-1786737777 posted
You are so right "Herr Lunch"! /hey thanks again for the forum. It is working great.
Thursday, May 18, 2006 1:28 AM -
User380413204 posted
Hi ,I am trying to show the last Logindate like Tommy. (and more of that sort of data when I succeed)I have been trying to do that using SQLdatasource and a gridview,But I can't synchronize it with the current (logged-in) user.Something like blahblahblah WHERE userid = @userid, but I am drawing blanks.Also I could be doing this the wrong way altogether of course.....Could someone point me in the right direction?Thanks,LexFriday, May 19, 2006 5:02 AM -
User-1786737777 posted
This does not fix Lex problem, but it was the final solution to my problem.
<ItemTemplate>
<%# Convert.ToDateTime(Eval("lastlogindate")).ToLocalTime() %>
</ItemTemplate>Regards,
Tommy
Friday, May 19, 2006 8:18 AM -
User248662050 posted
Lex,
Please show your SQL statement (blahblahblah WHERE userid = @userid) so we can take a look.
Thanks.
Friday, May 19, 2006 9:02 AM -
User380413204 posted
Hi,
SELECT distinct top 1 UserId, LastLoginDate
FROM vw_aspnet_MembershipUsers
WHERE (UserId = @UserId)With the where clause builder I choose for:
- column: UserId
- operator: =
- Source: Querystring
- Paramater properties ????
- default value ????
That's about it.
Regards,
Lex
Friday, May 19, 2006 9:14 AM -
User248662050 posted
Lex,
Are you sure that you are passing the UserId not the UserName?
The user id is a long uniqueidentifier.
Thanks.
Friday, May 19, 2006 9:28 AM -
User380413204 posted
Hi,
Well I choose it because it defines the user uniquely, (so does Username I guess)
In both cases my problem seems to be to hold it against the current logged-in user.
Thanks,
Lex
Friday, May 19, 2006 9:56 AM -
User2069888697 posted
Hi lex here is an example of how to build the select statement in the script and call it from the sqldatasource.
I find this easier for manipulating the query string. You could also place this in the App_Code folder so it can be called by other pages. For now try it in the page you are working on.
Public Sub Get_Authentication(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.PreLoad
Dim GridViewSQL As String
If
User.IsInRole("Administrators") = True ThenViewStatus = 1
GridViewSQL =
"SELECT NameFile, PathFile, FileDesc, IdFile, ViewStatus ,LastUpdated,DownLoads FROM dbo.aspsk_Files WHERE (ViewStatus" & ">=" & ViewStatus & ")"SqlDataSource1.SelectCommand = GridViewSQL
End IfEnd
SubThe SqlDatasource should look like this.
<
asp:SqlDataSource ID="SqlDataSource1" runat="server" OnLoad="Get_Authentication" ConnectionString="<%$ ConnectionStrings:ClubSiteDB %>"> </asp:SqlDataSource>Also Make sure the UserId is formatted correcty before executing the statement. My solution is,
Dim
UserId As StringUserId =
"{" & Membership.GetUser().ProviderUserKey.ToString & "}"This gets the current logged in User ID
Any questions you know were to find me.
Friday, May 19, 2006 12:13 PM -
User654902800 posted
What exactly are you trying to do Lex (in terms of what the final result is supposed to be)?
Getting the current user last login date doesn't need a database query, it's as simple as
Membership.GetUser().LastLoginDate
To get the last login of some other user, do
Membership.GetUser(memberid).LastLoginDate
or
Membership.GetUser(username).LastLoginDateI think Tommy was using a query because he was getting the last N people who logged in, but you can do a lot of this sort of thing without going to the database, for example, to get a list of all online users, and another list of all users who logged in in the last hour, you could do:
Dim allMembers As MembershipUserCollection = Membership.GetAllUsers()
Dim myOnlineUsers As MembershipUserCollection = New MembershipUserCollection
Dim myRecentUsers As MembershipUserCollection = New MembershipUserCollection
For Each person As MembershipUser In allMembers
If person.IsOnline Then
myOnlineUsers.Add(person)
myRecentUsers.Add(person)
ElseIf person.LastLoginDate > DateAdd(DateInterval.Hour, -1, Now) Then
myRecentUsers.Add(person)
End If
NextGridView1.DataSource = MyOnlineUsers
GridView2.DataSource = myRecentUsers
DataBind()
or something like that.
As an aside - there's something about the club kit that can lead you down a painful path, and that is the fact that the club kit has this Fistname & Lastname in the memberinfo table. If you want to display these names, then you're forced to do a query or join against that table to get the names. I think that's a bad idea for two reasons - one is that it's totally possible for there to be 2 or more John Smiths in the memberinfo table, the other is that the user can change those values, and conceal who they are (or were). That being said, I did the join in my forums code. I shouldn't have. I think you should display the username (like the login control does when you're logged in).
On another more obscure note - the username field in the aspnet DB isn't guranteed to be unique - since that db can hold user data for more than one application, thus it's a bad idea to query that db WHERE username = 'foo', much better to user the Membership class to get the user (since it will construct the query using the correct application name).
Anyhow, I strongly recommend against querying the aspnet DB at all, unless you must. There are programmatic ways to get that info that are much safer. If you need to get data about users, roles and whatnot and use that as a datasource for a grid or something, consider creating a class that gets the data and exposes it as an ObjectDataSource.
OK, off my soapbox now :)
Friday, May 19, 2006 12:29 PM -
User380413204 posted
Hi Frank,
I transposed your code as follows:
Dim GridViewSQL As String
Dim UserIdhaal As String
UserIdhaal =
"{" & Membership.GetUser().ProviderUserKey.ToString & "}"GridViewSQL =
"SELECT [LastLoginDate], [UserId] FROM [vw_aspnet_MembershipUsers] WHERE (UserId" & ">=" & UserIdhaal & ")"SqlDataSource1.SelectCommand = GridViewSQL
But then I have a problem that on loading the page I get
Object reference not set to an instance of an object.
That makes sense of course since I should run the code after logging in
So I tried it in the Logged-in template:
Protected Sub lv1_DataBinding(ByVal sender As Object, ByVal e As System.EventArgs) Dim GridViewSQL As String Dim UserIdhaal As StringUserIdhaal =
"{" & Membership.GetUser().ProviderUserKey.ToString & "}"GridViewSQL =
"SELECT [LastLoginDate], [UserId] FROM [vw_aspnet_MembershipUsers] WHERE (UserId" & ">=" & UserIdhaal & ")" Dim sqldta As System.Web.UI.WebControls.Login = lv1.FindControl("Login1") Dim jawat As SqlDataSource = CType(sqldta.FindControl("SqlDataSource6"), SqlDataSource)jawat.SelectCommand = GridViewSQL
End SubBut obviously at least one problem [:)] because the grid stays empty.
Thanks in advance,
Lex
Friday, May 19, 2006 1:51 PM -
User380413204 posted
Hi Mark,
This looks short and neat I must say. But as I am trying to put it in default.aspx I don't know where exactly to put it.
Sort of the same problem as described in the previous post: When the page loads the user is not logged on yet.
But it certainly is a piece of code that could come in handy at all sorts of pages.
Thanks,
Lex
Friday, May 19, 2006 2:00 PM -
User2069888697 posted
I'm afraid I'm a little confused, what is the reasoning for sqldta and jawat
I was just showing how you could build a query string in the script for a Sqldatasource, if your just trying to fill a gridview that uses Sqldatasource6 then just use SqlDatasource6.selectcommand = GridViewSql.
Dim sqldta As System.Web.UI.WebControls.Login = lv1.FindControl("Login1")
Dim jawat As SqlDataSource = CType(sqldta.FindControl("SqlDataSource6"), SqlDataSource)
jawat.SelectCommand = GridViewSQL
I do believe Mark has offered a better solution, you may just need to play around with it to get it to do what you want. The MembershipProvider is a great feature with Asp.net and should be a priority for anyone to learn(including myself)
Friday, May 19, 2006 2:59 PM -
User654902800 posted
But as I am trying to put it in default.aspx I don't know where exactly to put it.You haven't said *what* you're tying to put in default.aspx! Can you tell me?
Do you want the current user last logged in date? (only works if user is logged in)
Do you want the last time anybody logged in?
Are you trying to make a list of people who logged in recently? Or a list of people who are logged in now? Or what?
Friday, May 19, 2006 3:19 PM -
User380413204 posted
Hi Mark,
In according to Frank's advice I will focus on your solution (thanks Frank)
This is what I am trying to do:
I want the Current users Lastlogindate/time
(in this case its about lastlogindate or time, but I imagine it is useful in all sorts of scenario's to be able to determine who has logged in)
Because the grid must be filled after logging in (current user), I don't know where to put the code
you provided. For example lets say I put it in Page-Load the user is not logged in yet, so not there.
My last attempt was making a Sub in the Script part of the page and put your code in there:
Sub Test()
Dim allMembers As MembershipUserCollection = Membership.GetAllUsers()
Dim myOnlineUsers As MembershipUserCollection = New MembershipUserCollection
Dim myRecentUsers As MembershipUserCollection = New MembershipUserCollection
For Each person As MembershipUser In allMembers
If person.IsOnline Then
myOnlineUsers.Add(person)
myRecentUsers.Add(person)
ElseIf person.LastLoginDate > DateAdd(DateInterval.Hour, -1, Now) Then
myRecentUsers.Add(person)
End If
NextGridView1.DataSource = MyOnlineUsers
GridView2.DataSource = myRecentUsers
DataBind()
End sub
I get the error that Gridviews are not declared, (when I Dim Gridview1 as Gridview I get the warning that data has not yet been assigned to the grid) I can understand this, but I simply don't know how to solve it.
I want to place the Datagrid in the Logged-in-Template of Default.aspx
But I don't know how to get the result into the Datagrid.
Mark, I am most probably going the wrong way totally here, but perhaps you understand better what I am trying to do than in my last post.
Anyway thank you (and Frank) for your response, (I promise I will start with the basics as soon as I get my book [:$])
Lex
Saturday, May 20, 2006 4:04 AM -
User380413204 posted
Hi guys,
Small update on the previous. I am getting there slowly.
I almost got it working. Tried it on another page and Bang, right on, no errors, beautiful.
So I got back to Default.aspx and made a sub called Showusers and put your code in. And it now works with the click of a button.
The problem is narrowed down to a few (small?) things perhaps.
- When I try to add the gridview to Logged-In-Template I have problems with declaring the same gridview. (so now I have a gridview outside it . (problem solved now I come to think of it)
- I use an extra Button so I can hook up your code to the Button_Click event. Where Ideally it would go along with logging-in. (tried to add the On-Click to the RolloverButton but to no avail.
So almost there, just the last bit that puzzles me.
Lex
Saturday, May 20, 2006 6:19 AM -
User380413204 posted
Update nr.2
I completely overlooked the fact that the logindate is ovewritten at logging-in (dumb, dumber, ....)
What I want is the date before it is overwritten, but I guess that could be difficult if not impossible?
Lex
Saturday, May 20, 2006 8:49 AM -
User654902800 posted
The date before it is overwritten:
Protected Sub Login1_LoggingIn(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.LoginCancelEventArgs) Dim previousdate As Date = Membership.GetUser(Login1.UserName).LastLoginDate() End SubSaturday, May 20, 2006 3:08 PM -
User380413204 posted
Thanks Mark,I am almost there, got it up to to showing it on the page. That part should not be so difficult, but I can't get it done. I put a label in Login1 and I try to bring the value to it.
But I keep getting a Nullreference. The code works because when I do msgbox(previousdate) I see the previous login date, just like I wanted.
Protected Sub Login1_LoggingIn(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.LoginCancelEventArgs)
Dim login As System.Web.UI.WebControls.Login = lv1.FindControl("Login1")
Dim txt As TextBox = CType(login.FindControl("UserName"), TextBox)
Dim lbluser As Label = CType(login.FindControl("Laatste"), Label)
Dim previousdate As Date = Membership.GetUser(login.UserName).LastLoginDate()
lbluser.Text = previousdate
When I compare it to other labels (that work fine) I can see no difference. Do I somehow have to use a Cast or somethiong like that? Tried a zillion things.
Object reference not set to an instance of an object.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.NullReferenceException: Object reference not set to an instance of an object.
Source Error:
Line 100: Line 101: Dim previousdate As Date = Membership.GetUser(login.UserName).LastLoginDate() Line 102: lbluser.Text = previousdate Line 103: Label3.Text = previousdate Line 104:
Thanks for your patience,
Lex
Saturday, May 20, 2006 5:41 PM -
User654902800 posted
OK, several things happening here...
first, when the login control successfully logs someone in, it redirects to the page, so any local variables that were set (like previousdate) get lost.
second, the login control is not visible on a successful login, so the label you added to the login control doesn't exist - that's the null object error you were getting. You want that label to be in the part of the login view that will be visible when the user has logged in.
so anyhow to make it work, you have to store the previous date in some place outside of the local page variables, we'll use a session variable to do it, there are other ways besides this way.
first make sure that the login control tag looks like this:
<asp:Login ID="Login1" runat="server" OnLoggingIn="Login1_LoggingIn">then, make sure that your label with ID="Laatste" is inside the LoggedInTemplate part of the login view control.
What we're doing is when they begin to log in, we stuff the previous login date and time into a session variable, and then when they are redirected to the page, we'll use the variable to fill in the label:
Protected Sub Login1_LoggingIn(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.LoginCancelEventArgs)
Protected Sub Page_PreRender(ByVal sender As Object, ByVal e As System.EventArgs)
Dim login As System.Web.UI.WebControls.Login = CType(sender, System.Web.UI.WebControls.Login)
Dim foo As Date = Membership.GetUser(login.UserName).LastLoginDate()
Session("previousdate") = foo.ToLongDateString() & " " & foo.ToShortTimeString()
End Sub
Dim lbluser As Label = CType(lv1.FindControl("Laatste"), Label)
If Not lbluser Is Nothing Then
If Not Session("previousdate") Is Nothing Then
If Not String.IsNullOrEmpty(Session("previousdate").ToString) Then
lbluser.Text = "You last logged in: " & Session("previousdate").ToString
Else
lbluser.Text = ""
End If
End If
End If
End SubThis is pretty weird Lex :) But it's a great example of what happens with these fancy controls like Login - they have all kinds of things they do in the background (for example this redirect behaviour) and so you have to figure out what they are really doing. Setting breakpoints in the code and stepping through it line by line is the best way, that's how I figured it out. It's also in the documentation for Login, but it's not obvious. You picked something kind of unusual to do here.
Saturday, May 20, 2006 11:48 PM -
User380413204 posted
Hi Mark,
Thanks, that works fine, (obviously no beginners stuff , or perhaps because it's about how the motor works it qualifies just as that ...)
Nice, very nice, I owe you many thanks....
Best regards,
Lex
Sunday, May 21, 2006 3:15 AM