How to get the primary key value while executing the query?

Question

This is the Login Button code and EditUser() function:

Private Sub BtnLogin_Click(sender As Object, e As EventArgs) Handles btnLogin.Click

        If (isformvalid()) Then
            qr = "Select * from userlogin where UserName='" & txtUser.Text & "' and Password='" & txtPassword.Text & "' and UserType ='" & ComboBox1.Text & "' "

            ds = searchdata(qr)
            If (ds.Tables(0).Rows.Count > 0) Then
                LoginUser = txtUser.Text
                LoginPass = txtPassword.Text
                UserType = ComboBox1.Text

                EditUser()
                Dashbord.Show()
                Me.Close()
            Else
                MsgBox("UserName or Password is Incorrect. Please Cheak And Try Again")
                txtUser.ResetText()
                txtPassword.ResetText()

            End If
        End If
End Sub

Private Function EditUser()
        If (isformvalid()) Then
            q = "Insert into UserLoginAudit (UserName, Password, UserType, ActivityLogin) Values('" & txtUser.Text & "','" & txtPassword.Text & "','" & ComboBox1.Text & "','" & DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss") & "')"
            ProID = lastProID(q)
        End If
End Function

This is the module code:

Module DBSQLServer
    Public con As New SqlConnection("Data Source=JOYALXDESKTOP\SQLEXPRESS;Initial Catalog=SaleInventory;Integrated Security=True")
    Public cmd As New SqlCommand
    Public da As New SqlDataAdapter
    Public ds As New DataSet
    Public dt As DataTable
    Public qr As String
    Public i As Integer
    Public newProID As Integer

    Public Function searchdata(ByVal qr As String) As DataSet
        da = New SqlDataAdapter(qr, con)
        ds = New DataSet
        da.Fill(ds)
        Return ds

    End Function

    Public Function insertdata(ByVal qr As String) As Integer

        cmd = New SqlCommand(qr, con)
        con.Open()
        i = cmd.ExecuteNonQuery()
        con.Close()
        Return i

    End Function

    Public Function lastProID(ByVal qr As String) As Integer

        cmd = New SqlCommand(qr, con)
        con.Open()
        newProID = cmd.ExecuteScalar
        con.Close()
        Return newProID

    End Function
End Module

And this is my Logout code and EditUser() function:

Private Sub Label1_Click_1(sender As Object, e As EventArgs) Handles Label1.Click
        Application.Exit()
        EditUser()
End Sub

    Private Function EditUser()
        q0 = "UPDATE UserLoginAudit SET ActivityOffline = '" & DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss") & "' WHERE AuditID = '" & My.Forms.login.ProID & "';"
        Dim logincorrect As Boolean = Convert.ToBoolean(insertdata(q0))
        If (logincorrect) Then
            MsgBox("LogOut Successful ...", MsgBoxStyle.Information)
        Else
            MsgBox("Something Wrong. LogOut Failed. Please Check and Try Again...", MsgBoxStyle.Critical)
        End If
End Function

When I run the program, after clicking the login button the data (UserName, Password, UserType, ActivityLogin) is inserted perfectly.

But when I click the Logout button in the database ActivityOffline column value remains null.

Then I create a Form Load event to check the value of ProID in the main form where I created the logout button:

Private Sub Dashbord_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        hi.Text = My.Forms.login.ProID
End Sub

It always shows 0

Answer 1

There's a LOT in here, so take some time to read through and understand all the changes... because some of these things that might seem trivial are actually very important:

Module DBSQLServer
    'Don't try to re-use the connection object. It interferes with connection pooling and makes things worse, rather than better
    'Instead, just re-use the connection string
    Private ConnectionString As String = "Connection String here"

    'Make this private. Then have a public method for each actual query you use
    'It will force you to create a cleaner separation between DB and UI
    'If this starts to make for a really huge module, move this to a separate class library, where the public methods are divided up into separate modules for logical groupings and the private methods are in a common Friend module using the InternalsVisibleTo attribute
    Private Function searchdata(sql As String, ParamArray data() As SqlParameter) As DataSet
        Dim result As New DataSet()
        Using cn As New SqlConnection(ConnectionString), _
              cmd As New SqlCommand(sql, cn), _
              da As New SqlDataAdapter(cmd)

            If data IsNot Nothing Then
                For Each parameter As SqlParameter In data
                   cmd.Parameters.Add(parameter)
                Next
            End If
            da.Fill(result)
        End Using
        Return result
    End Function

    ' This may need some work yet
    Private Function GetValue(Of T)(ByVal qr As String, ParamArray data() As SqlParameter) As T
        Using cn As New SqlConnection(ConnectionString), _
              cmd = New SqlCommand(qr, con)

            If data IsNot Nothing Then
                For Each parameter As SqlParameter In data
                   cmd.Parameters.Add(parameter)
                Next
            End If

            con.Open()
            ' To do: better handling for NULL
            Return CType(cmd.ExecuteScalar(), T)
        End Using
    End Function

    'Now the Public methods      

    Public Function ValidateCredentials(Username As String, HashedPassword As String, UserType As String) As Integer
        Dim qr As String = "Select * from userlogin where UserName= @Username and Password= @Password  and UserType = @UserType"

        'Match the parameter declarations to the database column types and lengths
        Dim u As New SqlParameter("@Username", SqlDbType.NVarChar, 20)
        u.Value = UserName

        'Thanks to hashing, this will be a fixed length
        Dim p As New SqlParameter("@Password", SqlDbType.NChar, 60)
        p.Value = HashedPassword

        Dim t As New SqlParameter("@UserType", SqlDbType.VarChar, 10)
        t.Value = UserType

        Dim ds As DataSet = searchdata(qr, u, p, t)
        If ds.Tables(0).Rows.Count > 0 Then
            ' Moving this call here, as part of ValidateCredentials, prevents
            ' any possibility of logging in without also creating an audit
            Return CreateUserLoginAuditRecord(Username, Password, UserType)
        Else
            Return -1
        End If
    End Function

    ' Logging Passwords is also a **HUGE** no-no. Please remove that field from your audit table
    Public Function CreateUserLoginAuditRecord(Username As String, Password As String, Usertype As String)
        'Even better to combine this with the Validate query, as one long sql string handling multiple statements.
        Dim qr As String = "Insert into UserLoginAudit (UserName, Password, UserType, ActivityLogin) Values(@UserName, @Password, @UserType, current_timestamp);Select scope_identity;"

        Dim u As New SqlParameter("@Username", SqlDbType.NVarChar, 20)
        u.Value = UserName
        Dim p As New SqlParameter("@Password", SqlDbType.NChar, 60)
        p.Value = Password
        Dim t As New SqlParameter("@UserType", SqlDbType.VarChar, 10)
        t.Value = UserType

        Return GetValue(Of Integer)(qr, u, p, t)
    End Function

    Public Function CloseUserLoginAuditRecord(AuditID As Integer) As Boolean
        Dim qr As String = "UPDATE UserLoginAudit SET ActivityOffline = current_timestamp WHERE AuditID = @AuditID"

        Dim a As New SqlParameter("@AuditID", SqlDbType.Integer)
        a.Value = AuditID

        Return GetValue(Of Boolean)(qr, a)
    End Function
End Module


Public Function HashPassword(plainText As String) As String
   'Leaving this for you. Read up on BCrypt.
   'THIS IS IMPORTANT!!!!1!!
   Throw New NotImplementedException()
End Function

Private Sub BtnLogin_Click(sender As Object, e As EventArgs) Handles btnLogin.Click

    If isformvalid() Then
        Dim AuditID As Integer = DBSQLServer.ValidateCredentials(txtUser.Text, HashPassword(txtPassword.Text), ComboBox1.Text)

        If (AuditID <> -1) Then
            'Create ProID as a public property of the Dashboard class
            Dashboard.ProID = AuditID                 
            Dashboard.Show()
            Me.Close()
        Else
            MsgBox("UserName or Password is Incorrect. Please Check And Try Again")
            txtUser.ResetText()
            txtPassword.ResetText()
        End If
    End If
End Sub

Private Sub Label1_Click_1(sender As Object, e As EventArgs) Handles Label1.Click
    ' This should really be handled by the form-closing event or similar.
    ' Event that won't be enough. You can't stop task manager, power failures, OS/application crashes, etc.
    ' No matter what, you **WILL** end up with a **LOT** of unresolved audits
    ' Really, it's best just to throw up your hands about this. 
    ' Close what you can, but don't worry about the unclosed audit records, because you really can't stop that...
    ' ... in which case, you might just remove the MsbBox calls.
    If DBSQLServer.CloseUserAuditLoginRecord(ProID) Then
        MsgBox("LogOut Successful ...", MsgBoxStyle.Information)
    Else
        MsgBox("Something went wrong. LogOut Failed. Please Check and Try Again...", MsgBoxStyle.Critical)
    End If
    Application.Exit()
End Sub

Answer 2

At the end of your Insert statement add a semi colon to indicate the end of the command. Then add

SELECT SCOPE_IDENTITY();

You can then use .ExecuteScalar and expect the new Primary Key to be returned.

However

OUTPUT it is Microsoft's preferred way to get the value and from the things I've read they have no intention of fixing any problems with @@identity and scope_Identity in the future as they consider OUTPUT to be the replacement.

CREATE PROCEDURE [Sales].[uspNewCustomer]  
@CustomerName NVARCHAR (40),  
@CustomerID INT OUTPUT  
AS  
BEGIN  
INSERT INTO [Sales].[Customer] (CustomerName) VALUES (@CustomerName);  
SET @CustomerID = SCOPE_IDENTITY();  
RETURN @@ERROR  
END