What is wrong with this OleDbReader?-CodePudding

Working in VB, I'm connecting to an access db, and need to get a value from a table.

    user = Environment.UserName
    command.CommandText = "SELECT 'nid', 'UserName' FROM qryUSERJOBS WHERE UserName = " & user
    command.Connection = connect
    Using reader As OleDbDataReader = command.ExecuteReader()
        While reader.Read()
            record = reader("nid").ToString
        End While
    End Using

When my code steps into the Using statement, reader is created, and my win form is immediately displayed without reading the database or hitting the End Sub of the routine that contains it. Any ideas?

CodePudding user response：

use parameterized query
avoid sql injection
Avoid quote mistakes like this one
Make use of disposable objects

Using connect As New OleDbConnection(connectionString)
    connect.Open()
    Using command As New SqlCommand(
    "SELECT nid, UserName FROM qryUSERJOBS WHERE UserName = @user", 
    connect)
        user = Environment.UserName
        command.Parameters.Add(New OleDbParameter("@user", user))

        Using reader As OleDbDataReader = command.ExecuteReader()
            While reader.Read()
                record = reader("nid").ToString
            End While
        End Using

    End Using
End Using

CodePudding user response：

After putting the code in a Try...Catch block I got an error regarding my parameters. I needed single quotes around my user variable.

Code fixed:

    user = Environment.UserName
    command.CommandText = "SELECT nid, UserName FROM qryUSERJOBS WHERE UserName = '" & user & "'"
    command.Connection = connect
    connect.Open()
    Try
        Using reader As OleDbDataReader = command.ExecuteReader()
            While reader.Read()
                record = reader("nid").ToString
            End While
        End Using
    Catch ex As Exception

    End Try

Thanks everyone for your help and advice!