我试图通过执行带有主键的 SELECT 语句来获取单行
我尝试将 command.ExecuteScalar()
的输出分配给一个Object
变量,然后尝试通过键访问值,例如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
我希望从reader('first_name')
first_name
列中获得值,但我得到了一个NullReferenceException
异常
ExecuteScalar 返回包含单列的单行,但它不返回从中获取数据的 DataReader。相反,它直接将该单个值作为对象返回。如果查询找不到与 Where 语句匹配的任何行,则此对象可能为 Nothing。
如果您只想获取名字,则应将查询文本更改为
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
相反,如果要从该记录中检索所有字段(或多个字段(,请使用 ExecuteReader,然后使用
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