• 0

[ASP.NET] Update SQL date/time field upon successful login?


Question

I'm trying to get my feet wet in ASP.net programming, as one of our companies applications is designed in it, and while I understand VB.net, ASP.net is a little strange to me. As a test, I've started working on a database driven web application that has a log in form. The log in form makes a call to an SQL database to see if the users name and PW exist in a table. If they do, it redirects them to the main form. SO far everything is working, but now I'd like to do a test by having the form write the current date/time back to the database to a field called "LastLogin" if their result was found.

What I have so far:

Partial Class Login
    Inherits System.Web.UI.Page


    Public Sub LogIn(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmdLogin.Click
        Dim connStr As String = "Data Source=WHITE-DWARF;Initial Catalog=UserDatabase;Integrated Security=True"
        Dim sqlconnet As Data.SqlClient.SqlConnection
        Dim MyComm As Data.SqlClient.SqlCommand

        sqlconnet = New Data.SqlClient.SqlConnection()
        sqlconnet.ConnectionString = connStr
        MyComm = New Data.SqlClient.SqlCommand("", sqlconnet)

        MyComm.CommandType = Data.CommandType.Text

        MyComm.CommandText = "SELECT * FROM Logins WHERE (UserName ='" & txtUserName.Text & "') AND (Password = '" & txtPassword.Text & "') "
        sqlconnet.Open()

        Dim result As Data.SqlClient.SqlDataReader = MyComm.ExecuteReader(Data.CommandBehavior.CloseConnection)


        If result.HasRows = False Then

            lblStatus.Text = "Username and/or Password does not exist!"

        Else

            Session("Username") = txtUserName.Text
            Response.Redirect("success.aspx")
           ------> MyComm.CommandText = "UPDATE Logins SET LastLogin=" & Now & " WHERE (UserName ='" & txtUserName.Text & "') AND (Password = '" & txtPassword.Text & "')"

        End If

        result.Close()

    End Sub
End Class

The text next to the "---->" is what I'm trying to have done back to the database, but not sure if I need to redeclare the whole SQL data connection again just to perform an UPDATE...is there an easier way than redeclaring everything?

3 answers to this question

Recommended Posts

  • 0

Try putting the following line:

MyComm.ExecuteNonQuery()

to get the Update command to fire.

It is similar to the ExecuteReader command for the Select statement

Dim result As Data.SqlClient.SqlDataReader = MyComm.ExecuteReader(Data.Commandbehavior.CloseConnection)

  • 0

Yeah, you need to execute the last query, also the Response.Redirect should come after the query execution.

Off-topic, but please never never do this:

        MyComm.CommandText = "SELECT * FROM Logins WHERE (UserName ='" & txtUserName.Text & "') AND (Password = '" & txtPassword.Text & "') "

Use parameters so you're not open to SQL injection.

  • 0

I agree.

- Create parameter driven stored procedures in your SQL database to use within your website

- Sanitise the user inputs with functions

- Put the 'Update' before the redirect ;)

Small Example Below:

Imports System.Web.Security
Imports System.Data.SqlClient
Imports System.Data

Partial Class Controls_LoginDialog
    Inherits System.Web.UI.UserControl

    Dim strTitle As String = String.Empty


    Private Sub btnLogin_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnLogin.Click
        Login(fSanitise(Me.txtUsername.Text), fSanitise(Me.txtPassword.Text))
    End Sub

    Private Sub Login(ByVal userName As String, ByVal passWord As String)

        Dim cnnSecurity As New SqlConnection(System.Configuration.ConfigurationManager.AppSettings.Item("connection_security       Dim cmdSelect As New SqlCommand("usp_mySite_Login", cnnSecurity)
        Dim Authd As Boolean
        Dim MySettings As UserSettings = Nothing

        With cmdSelect
            .CommandType = CommandType.StoredProcedure
            .Parameters.Add(New SqlParameter("@Username", SqlDbType.VarChar)).Value = userName
            .Parameters.Add(New SqlParameter("@Password", SqlDbType.VarChar)).Value = passWord

            Try
                .Connection.Open()

                Dim drUser As SqlDataReader = .ExecuteReader(CommandBehavior.CloseConnection)

                If drUser.Read Then

                    FormsAuthentication.SetAuthCookie(userName, False)

                    Session.Add("LastUnloadedthing) 'session variable for auto-logout
                    MySettings = New UserSettings(Convert.ToString(drUser.Item("Username_
                                                  Convert.ToString(drUser.Item("Surname_
                                                  Convert.ToString(drUser.Item("Firstname_
                                                  Convert.ToString(drUser.Item("Email
                    'Session.Add("usersettingsSettings)
                    UserSettings.storeSettings(MySettings)

                    Authd = True

                    Session.Timeout = MySettings.SessionTimeout

                    drUser.Close()
                Else
                    Me.vldCustPassword.IsValid = False
                    Me.vldCustPassword.ErrorMessage = "Incorrect User/Password Combination Provided"
                End If

            Catch ex As Exception
                Me.vldCustPassword.IsValid = False
                Me.vldCustPassword.ErrorMessage = "Serious Error Has Occured - Please Contact WebAdmin"
                Throw
            Finally
                If Not .Connection.State = ConnectionState.Closed Then .Connection.Close()
            End Try
        End With

        If Authd And (Not MySettings Is Nothing) Then

            If Request.QueryString.Item("ReturnUrl Nothing Then
                Response.Redirect(MySettings.DefaultUrl)
            Else
                Response.Redirect(Request.QueryString.Item("ReturnUrl           End If


        End If
    End Sub

End Class

This topic is now closed to further replies.
  • Recently Browsing   0 members

    • No registered users viewing this page.
  • Posts

    • That'll be it for my 2018 Mac mini then. There is, of course, OpenCore Legacy Patcher . . .
    • Support cost cutting and nudging people to upgrade for profit. Pure and simple. Nothing as noble as hardware actually becoming "obsolete" about most of the hardware requirements from Apple, Microsoft, or Android companies either. iMac 2017 with AMD running legacy Core still have native GPU support in macOS 15 and run perfectly fine. Doesn't even need any fancy patching outside of the minimum patching needed to get macOS 15 on the system to bypass Apple system requirements aking to running windows 11 on a PC that doesn't match the arbitrary CPU generation requirements that make you bypass them too. You can usually tell if hardware is truly in the realm of "obsolete" if you are having a hard time finding a major Linux distro that'll install on it.
    • >Mozilla's Firefox has been left behind over time in terms of market share, as it has not been able to keep up with Chromium-based browsers in the performance department. I have no problems with Firefox's performance whatsoever. I suspect the reason Firefox is lagging in market share is that average consumers haven't heard of it and are fine with what was installed on their systems.
    • The Expanse: Osiris Reborn is a new narrative-driven sci-fi RPG inspired by Mass Effect by Pulasthi Ariyasinghe Out of nowhere, a narrative-driven sci-fi action RPG has been announced that will take players into the universe of The Expanse TV show and novels. The reveal trailer landed today during the Future Games Showcase, offering both a cinematic look at the setting as well as snippets of gameplay. Check out the The Expanse: Osiris Reborn debut trailer above. The studio behind the project is Owlcat Games. Some may remember that name from being involved in the role-playing titles Pathfinder: Kingmaker, Pathfinder: Wrath of the Righteous, as well as the most recent hugely well-received RPG Warhammer 40,000: Rogue Trader. Now, the studio's sights are set on The Expanse universe, and it's going for the over-the-shoulder third-person gameplay route for the first time. “We’ve been dreaming about building a sci-fi action RPG of this scale for a long time, and The Expanse is the perfect universe to bring that vision to life,” says Owlcat’s Creative Director, Alexander Mishulin. “It’s a world grounded in realism and complexity, perfect for telling a story the way we like it— mature and character-driven, where your choices truly matter." The story will have players taking the role of a custom captain that can be from the Earth, Mars, or the Belt to take control of the most advanced ship currently available. There's a crew to meet and lead, tactical third-person combat, and, as expected from this franchise, a divided solar system to navigate and make decisions on. The studio was also very direct about this experience being inspired by BioWare's Mass Effect trilogy. Aside from the action-heavy gameplay seen in the trailer, Owlcat is promising plenty of political intrigue, as well as romance options for players to dive into. Game Design Producer Yuliya Chernenko added "many of us first played it in our teenage years, and it left a lasting impression," and that "we are building on that legacy and expanding what players anticipate from this experience." The Expanse: Osiris Reborn does not have a release window just yet, but it will be coming out on PC (Steam, Epic Games Store, GOG), Xbox Series X|S, and PlayStation 5.
    • I don't know of anyway other than winareo tweaker to set the font type in Win11. They took that option away!   No wonder people are staying with Win10, or 7.   It's not our computer with Win11.... it's theirs!
  • Recent Achievements

    • Conversation Starter
      Naomi723 earned a badge
      Conversation Starter
    • Week One Done
      abortretryfail earned a badge
      Week One Done
    • First Post
      Mr bot earned a badge
      First Post
    • First Post
      Bkl211 earned a badge
      First Post
    • One Year In
      Mido gaber earned a badge
      One Year In
  • Popular Contributors

    1. 1
      +primortal
      486
    2. 2
      +FloatingFatMan
      256
    3. 3
      snowy owl
      243
    4. 4
      ATLien_0
      222
    5. 5
      +Edouard
      191
  • Tell a friend

    Love Neowin? Tell a friend!