Database query not returning results from value

Question

I am making a work management system and I am fairly new to Visual Basic.

What I am trying to do is retrieve the name of the employee from the database with the given ID. After that I want this name to be displayed in a Label. After that, he can press the Work Start or Work end buttons.

Here is the code:

Private Function employeeSearchwithID(PersonalNr As String) As String

    Dim mitarbeiter As String
    Dim r As DataRow

    Access.ExecQuery("SELECT [Vorname], [Name] from [TA-Personal] WHERE ([Personal_Nr] = '" & PersonalNr & "');")

    'Report and Abort on Erros or no Records found
    If NoErros(True) = False Or Access.RecordCount < 1 Then Exit Function

    r = Access.DBDT.Rows(0)

    'Populate Label with Data
    mitarbeiter = r.Item("Vorname") & " " & r.Item("Name")

    Return mitarbeiter

End Function

It is used like this:

Private Sub tbxUserInput_KeyDown(sender As Object, e As KeyEventArgs) Handles tbxUserInput.KeyDown
    If e.KeyCode = Keys.Enter Then 'employeeIDnumbersSelect()
        Label5.Text = employeeSearchwithID(tbxUserInput.ToString)
    End If
End Sub

So, the plan is to have this program running on a tablet connected to a scanner. Every employee will have a personal card. When they scan the card, I want their names to be displayed. Of course, the card will be with the ID. But I am having trouble with the names: when I give my personal number it comes up as an empty string.

I have a separate DB Module. I learned from a tutorial:

Imports System.Data.OleDb

Public Class DBControl
    ' DB Connection 
    Public DBCon As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=D:\recycle2000.mdb;")

    'DB Command
    Public DBCmd As OleDbCommand

    'DB Data
    Public DBDA As OleDbDataAdapter
    Public DBDT As DataTable
    'Public Myreader As OleDbDataReader = DBCmd.ExecuteReader

    'Query Paramaters
    Public Params As New List(Of OleDbParameter)

    ' Query Stats
    Public RecordCount As Integer
    Public Exception As String

    Public Sub ExecQuery(Query As String)
        'Reset Query Stats
        RecordCount = 0
        Exception = ""

        Try
            'Open a connection
            DBCon.Open()

            'Create DB Command
            DBCmd = New OleDbCommand(Query, DBCon)

            ' Load params into DB Command
            Params.ForEach(Sub(p) DBCmd.Parameters.Add(p))

            ' Clear params list
            Params.Clear()

            ' Execute command & fill Datatable
            DBDT = New DataTable
            DBDA = New OleDbDataAdapter(DBCmd)
            RecordCount = DBDA.Fill(DBDT)

        Catch ex As Exception

            Exception = ex.Message

        End Try

        ' Close your connection
        If DBCon.State = ConnectionState.Open Then DBCon.Close()

    End Sub

    ' Include query & command params
    Public Sub AddParam(Name As String, Value As Object)

        Dim NewParam As New OleDbParameter(Name, Value)
        Params.Add(NewParam)

    End Sub

End Class

Answer 1

Without knowledge of the Access class, I have to recommend a different approach to querying the database. It is important to make sure that the database is not vulnerable to SQL injection, be it deliberate or accidental. The way to do that is to use what are known as SQL parameters: instead of putting the value in the query string, the value is supplied separately.

Private Function EmployeeSearchwithID(personalNr As String) As String

    Dim mitarbeiter As String = String.Empty

    Dim sql = "SELECT [Vorname], [Name] from [TA-Personal] WHERE [Personal_Nr] = ?;"

    Using conn As New OleDbConnection("your connection string"),
           cmd As New OleDbCommand(sql, conn)

        cmd.Parameters.Add(New OleDbParameter With {.ParameterName = "@PersonalNr",
                                                    .OleDbType = OleDbType.VarChar,
                                                    .Size = 12,
                                                    .Value = personalNr})

        conn.Open()
        Dim rdr = cmd.ExecuteReader()

        If rdr.Read() Then
            mitarbeiter = rdr.GetString(0) & " " & rdr.GetString(1)
        End If

    End Using

    Return mitarbeiter

End Function

Private Sub tbxUserInput_KeyDown(sender As Object, e As KeyEventArgs) Handles tbxUserInput.KeyDown
    If e.KeyCode = Keys.Enter Then 'employeeIDnumbersSelect()
        Dim employeeName = EmployeeSearchwithID(tbxUserInput.Text.Trim())

        If String.IsNullOrEmpty(employeeName) Then
            MessageBox.Show("Not found.", "Problem", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
        Else
            Label5.Text = employeeName
        End If

    End If

End Sub

The Using command makes sure that the "unmanaged resources" involved in querying a database are cleaned up afterwards, even if something goes wrong.

You will need to change the value of OleDbType.VarChar and .Size = 12 to match the type and size of that column in the database.

The name of the parameter is only for convenience with OleDb because it is ignored in the actual query, which uses "?" as a placeholder. Please see OleDbCommand.Parameters Property for full information.

If it still does not work, then please enter the ID manually in the tbxUserInput and see if you can make it work that way.

Hang on... tbxUserInput.ToString should be tbxUserInput.Text. But everything else I wrote still applies.