I'm trying to create a form where you can input your employee code. Even though the Employee ID is correct, it is still read incorrectly. how can I fix it? Here's the code.

con.Open()

If sql = "Select * from tblempinfo WHERE EmpID ='" & Trim(TextBox1.Text) & "'" Then

    MsgBox("You are now logged in ")
    Me.Hide()
    tito.Show()
Else
    MsgBox("Invalid Employee Code")
End If

con.Close()

CodePudding user response：

You have to execute the query and get the result. It's more efficient to use a count of the results, which means only a number needs to be passed.

You should always pass values to a query using SQL parameters, not by putting them directly in the query string. In the following code, the parameter is represented by a ? in the query - the name ("@empid") of the parameter is not used by the code, but it helps you to see which one is which when there is more than one.

Dim sql = "SELECT COUNT(*) FROM tblempinfo WHERE EmpID = ?"
Dim n = 0

Using conn = New OleDbConnection("yourConnectionString"),
       cmd = New OleDbCommand(sql, conn)

    Dim empId = TextBox1.Text.Trim()
    cmd.Parameters.Add("@empid", OleDbType.VarWChar).Value = empId

    conn.Open()
    n = CInt(cmd.ExecuteScalar())

End Using

If n = 1 Then
    MsgBox("You are now logged in ")
    Me.Hide()
    tito.Show()
Else
    MsgBox("Invalid Employee Code")

End If

Please adjust the OleDbType.VarWChar as required to match the actual type of the database column.

CodePudding user response：

You may need to check the user for a level of authentication, and make sure there is only one record.

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    Dim input = TextBox1.Text.Trim()
    Dim empID As Integer
    ' need to validate the text input as integer
    If Integer.TryParse(input, empID) AndAlso isUserAuthenticated(empID) Then
        MsgBox("You are now logged in ")
        Me.Hide()
        tito.Show()
    Else
        MsgBox("Invalid Employee Code") ' TextBox.Text incorrect format
    End If
End Sub

Private Function isUserAuthenticated(empID As Integer) As Boolean
    Using con As New SqlClient.SqlConnection("connection string") ' put your connection string here
        Dim sql = "Select * from tblempinfo WHERE EmpID = @empID"
        Using com As New SqlClient.SqlCommand(sql, con)
            con.Open()
            com.Parameters.AddWithValue("@empID", empID)
            Dim dr = com.ExecuteReader()
            Dim results As New List(Of Object)()
            While dr.Read()
                results = dr.GetValue(0) ' index of result you will use to authenticate user
            End While
            If results.Any() Then
                If results.Count = 1 Then
                    Dim result = DirectCast(results(0), Integer) ' assuming the result is an integer
                    If result > 5 Then ' arbitrary here, but assuming > 5 means authenticated
                        Return True
                    Else
                        Return False ' not authenticated
                    End If
                Else
                    Return False ' > 1 record
                End If
            Else
                Return False ' no records
            End If
        End Using
    End Using
End Function

CodePudding user response：

Please check the following things in this order:-

1. If the code is not erroring out then -

• a) First open the record set for the SQL you are trying to access using db.OpenRecordset()

• b) Verify error scenarios if there is no data on the record set IsNull()

• c) If you arrive at this third step then Check if the table have more than 1 row for the same employee code - RecordCount > 0. If the record set returns > 1 rows in this case, then pls handle the error scenario

• d) Handle each of the above steps in the code in the same order, please do not try to directly arrive at the final state since it will help you to understand the code and data flow together.

• e) If there is still some issue then try to remove the trim and try entering the correct employee code and check if that works fine in this particular scenario.

2. If it is a case code is erroring out then please fix the code and retry.
3. You have mentioned that the data is read incorrectly which seems to suggest that there is no error but the data that is being returned is incorrect. So please provide additional information as to what your input and output are so that more suggestions can be provided. But i still suggest you to handle the above approaches that are suggested.