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

Answer 1

User248662050 posted
I think it puts the GMT time. We may have to do the calculation/conversion ourselves.

Answer 2

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

Answer 3

User248662050 posted
I did a test. I think it is putting the UTC time rather than the system time

Answer 4

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()

Answer 5

User-1786737777 posted
Thank you people for your help. I will implement it tomorrow. Strange thing it does not use the server time.

Answer 6

User-1786737777 posted

Hi again!

I got the sql for it:

select dateadd(hh,2,lastlogindate) from dbo.aspnet_membership

where email ='[e-mail]'

/Tommy

Answer 7

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).

Answer 8

User-1786737777 posted

You are so right "Herr Lunch"! /hey thanks again for the forum. It is working great.

Answer 9

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,

Lex

Answer 10

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

Answer 11

User248662050 posted

Lex,

Please show your SQL statement (blahblahblah WHERE userid = @userid) so we can take a look.

Thanks.

Answer 12

User380413204 posted

Hi,

SELECT     distinct top 1 UserId, LastLoginDate
FROM         vw_aspnet_MembershipUsers
WHERE     (UserId = @UserId)

With the where clause builder I choose for:

1. column: UserId
2. operator: =
3. Source: Querystring
4. Paramater properties ????
5. default value ????

That's about it.

Regards,

Lex

Answer 13

User248662050 posted

Lex,

Are you sure that you are passing the UserId not the UserName?

The user id is a long uniqueidentifier.

Thanks.

 

Answer 14

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

 

Answer 15

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 Then

ViewStatus = 1

GridViewSQL = "SELECT NameFile, PathFile, FileDesc, IdFile, ViewStatus ,LastUpdated,DownLoads FROM dbo.aspsk_Files WHERE (ViewStatus" & ">=" & ViewStatus & ")"

SqlDataSource1.SelectCommand = GridViewSQL

End If

End Sub

 

The 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 String

UserId = "{" & Membership.GetUser().ProviderUserKey.ToString & "}"

This gets the current logged in User ID

Any questions you know were to find me.

Answer 16

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).LastLoginDate

I 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
Next

GridView1.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 :)

Answer 17

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 String

UserIdhaal = "{" & 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 Sub

 

But obviously at least one problem  [:)] because the grid stays empty.

Thanks in advance,

Lex

 

Answer 18

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

 

 

Answer 19

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)

Answer 20

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?

Answer 21

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
Next

GridView1.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

Answer 22

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.

1. 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)
2. 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 

 

Answer 23

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

Answer 24

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 Sub

Answer 25

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

Answer 26

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)
        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

    Protected Sub Page_PreRender(ByVal sender As Object, ByVal e As System.EventArgs)
        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 Sub

This 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.

Answer 27

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