What is wrong with this OleDbReader?

Question

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?

wrap your code in a Try...catch block, and set a breakpoint in there. I think you are missing single quotes around your user variable. — Jeremy, Jul 06 '15 at 14:35
You havent opened your connection, have you? Always use parameterized queries instead of string concatenation to prevent sql injection and other issues. — Tim Schmelter, Jul 06 '15 at 14:37
Don't run the code (or invoke the method) in `OnLoad`, move it to (or invoke the method in) `OnShown`. — Bjørn-Roger Kringsjå, Jul 06 '15 at 14:50
The connection was opened at an earlier part in the code. I put it in a try...catch, and actually received an error for a change. Jeremy, you were correct I was missing the single quotes around my user variable. Thank you all for your responses — belaythat, Jul 06 '15 at 14:55
mismatched ticks can be avoided using [Parameters](http://stackoverflow.com/a/29187199/1070452) — Ňɏssa Pøngjǣrdenlarp, Jul 06 '15 at 15:09

score 3 · Accepted Answer · answered Jul 06 '15 at 15:15

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

belaythat · Answer 2 · 2015-07-06T15:07:36.100

-1

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!

edited Jul 06 '15 at 15:07

answered Jul 06 '15 at 15:00

belaythat

45
8

4

I hope this is not production code because you should be using Parameters https://xkcd.com/327/ – Seph Jul 06 '15 at 15:14
It's my first time using SQL and had no idea what parameters were up till yesterday, the code currently being used resembles the accepted answer for the post. – belaythat Jul 08 '15 at 13:45

What is wrong with this OleDbReader?

2 Answers2