• 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

    • At some point, hardware becomes obsolete and we cant blame vendors for dropping support. Much of the models dropped, run Intel hardware which means they can install something else on the device if they truly want to keep using it.
    • Well, that was fun: PS C:\WINDOWS\system32> Install-Script -Name Set-InetpubFolderAcl WARNING: Unable to download from URI 'https://go.microsoft.com/fwlin...627338&clcid=0x409' to ''. WARNING: Unable to download the list of available providers. Check your internet connection. PackageManagement\Install-PackageProvider : No match was found for the specified search criteria for the provider 'NuGet'. The package provider requires 'PackageManagement' and 'Provider' tags. Please check if the specified package has the tags. At C:\Program Files\WindowsPowerShell\Modules\PowerShellGet\1.0.0.1\PSModule.psm1:7468 char:21 + ... $null = PackageManagement\Install-PackageProvider -Name $script:N ... + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : InvalidArgument: (Microsoft.Power...PackageProvider:InstallPackageProvider) [Install-PackageProvider], Exception + FullyQualifiedErrorId : NoMatchFoundForProvider,Microsoft.PowerShell.PackageManagement.Cmdlets.InstallPackageProvider PackageManagement\Import-PackageProvider : No match was found for the specified search criteria and provider name 'NuGet'. Try 'Get-PackageProvider -ListAvailable' to see if the provider exists on the system. At C:\Program Files\WindowsPowerShell\Modules\PowerShellGet\1.0.0.1\PSModule.psm1:7474 char:21 + ... $null = PackageManagement\Import-PackageProvider -Name $script:Nu ... + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : InvalidData: (NuGet:String) [Import-PackageProvider], Exception + FullyQualifiedErrorId : NoMatchFoundForCriteria,Microsoft.PowerShell.PackageManagement.Cmdlets.ImportPackageProvider WARNING: Unable to download from URI 'https://go.microsoft.com/fwlin...627338&clcid=0x409' to ''. WARNING: Unable to download the list of available providers. Check your internet connection. PackageManagement\Get-PackageProvider : Unable to find package provider 'NuGet'. It may not be imported yet. Try 'Get-PackageProvider -ListAvailable'. At C:\Program Files\WindowsPowerShell\Modules\PowerShellGet\1.0.0.1\PSModule.psm1:7478 char:30 + ... tProvider = PackageManagement\Get-PackageProvider -Name $script:NuGet ... + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : ObjectNotFound: (Microsoft.Power...PackageProvider:GetPackageProvider) [Get-PackageProvider], Exceptio n + FullyQualifiedErrorId : UnknownProviderFromActivatedList,Microsoft.PowerShell.PackageManagement.Cmdlets.GetPackageProvider Install-Script : NuGet provider is required to interact with NuGet-based repositories. Please ensure that '2.8.5.201' or newer version of NuGet provider is installed. At line:1 char:1 + Install-Script -Name Set-InetpubFolderAcl + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : InvalidOperation: ( [Install-Script], InvalidOperationException + FullyQualifiedErrorId : CouldNotInstallNuGetProvider,Install-Script
    • Because Apple has never discontinued Mac models before now?
    • I recall reading that they intend to synchronize the version numbers between iOS, tvOS and macOS, so we will also be getting macOS 26 up next.
    • every time.... why can't I install windows 11 on my 8088?
  • Recent Achievements

    • 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
    • One Year In
      Vladimir Migunov earned a badge
      One Year In
  • Popular Contributors

    1. 1
      +primortal
      489
    2. 2
      +FloatingFatMan
      257
    3. 3
      snowy owl
      247
    4. 4
      ATLien_0
      223
    5. 5
      +Edouard
      190
  • Tell a friend

    Love Neowin? Tell a friend!