0

I'm trying to get a single row by executing a SELECT statement with a primary key

I have tried to assign the output of command.ExecuteScalar() to an Object variable then tried accessing the values by key like obj('column_name')

Using conn As New SQLiteConnection(connectionString:=connection.get_connection_string())
                    conn.Open()
    Dim sql_string As String = "SELECT * FROM employees WHERE id = @id"
    Using cmd As New SQLiteCommand(connection:=conn, commandText:=sql_string)
        cmd.Parameters.AddWithValue("@id", employee_id)
        Dim reader As Object = cmd.ExecuteScalar()
        If reader <> Nothing Then
            Me.user_details(2) = reader("first_name")
        End If
    End Using
End Using

I was expecting to reap the values in the column first_name from reader('first_name') but i'm getting a NullReferenceException exception

Redgren Grumbholdt
  • 1,115
  • 1
  • 19
  • 36

1 Answers1

1

ExecuteScalar returns a single row with a single column but it doesn't return a DataReader from which you get the data. Instead it returns directly that single value as an Object. This object could be Nothing if the query doesn't find any row matching the Where statement

If you want to get only the first name then you should change your query text as 

Dim sql_string As String = "SELECT first_name FROM employees WHERE id = @id"
Using cmd As New SQLiteCommand(connection:=conn, commandText:=sql_string)
    cmd.Parameters.AddWithValue("@id", employee_id)
    Dim result As Object = cmd.ExecuteScalar()
    If result <> Nothing Then
        Me.user_details(2) = result.ToString()
    End If
End Using

Instead if you want to retrieve all fields (or more than one field) from that record you use ExecuteReader and then start reading the results with

Dim sql_string As String = "SELECT first_name, last_name, city FROM employees WHERE id = @id"
Using cmd As New SQLiteCommand(connection:=conn, commandText:=sql_string)
    cmd.Parameters.AddWithValue("@id", employee_id)
    Dim reader = cmd.ExecuteReader()
    ' Try to position the reader on the first record returned by the query
    ' if there isn't records the Read returns false. 
    ' You can also test this with reader.HasRows
    While reader.Read()
        Me.user_details(1) = reader("last_name").ToString()
        Me.user_details(2) = reader("first_name").ToString()
        Me.user_details(3) = reader("city").ToString()
    End While
End Using
Steve
  • 213,761
  • 22
  • 232
  • 286
  • Can I get values from multiple columns at the same time? – Redgren Grumbholdt Apr 12 '19 at 10:09
  • Yes, but then you use ExecuteReader instead of ExecuteScalar – Steve Apr 12 '19 at 10:11
  • I'm getting `Object reference not set to an instance of an object.` – Redgren Grumbholdt Apr 12 '19 at 10:22
  • That line was just for an example. However if you really have a _last_name_ field and it is possible for that field to be null then you should check if it contains a value (using [IsDBNull](https://learn.microsoft.com/en-us/previous-versions/visualstudio/visual-studio-2008/tckcces5(v=vs.90)) before calling ToString – Steve Apr 12 '19 at 10:30
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/191722/discussion-between-percival-rapha-and-steve). – Redgren Grumbholdt Apr 12 '19 at 10:31