I'm trying to query an SQL database and I get the following error:

Exception of type 'System.ArgumentOutOfRangeException was thrown. (Parameter 'name')

How can I fix this please?

Dim Connection As New SqliteConnection("Data Source = Database.db")
Dim SQLcommand As String = "SELECT * FROM Menu WHERE ItemID = 113"
Dim CMD As New SqliteCommand
 
Try
    CMD.Connection = Connection
    Connection.Open()
    CMD.CommandText = SQLcommand
    Dim reader As SqliteDataReader = CMD.ExecuteReader()
    While reader.Read()
        Order.Label3.Text = reader(reader("ItemID") & ", " & reader("Name") & ", " & reader("Price"))
    End While
    reader.Close()
    Connection.Close()
Catch e As Exception
    MessageBox.Show(e.Message)
End Try

CodePudding user response：

I think you have too many calls to reader in this line:

Order.Label3.Text = reader(reader("ItemID") & ", " & reader("Name") & ", " & reader("Price"))

Change it to:

Order.Label3.Text = reader("ItemID") & ", " & reader("Name") & ", " & reader("Price")

CodePudding user response：

Separate your user interface code from your database code. Connections, commands and DataReaders need to have their Dispose methods called to release unmanaged resources they use. Using blocks do this for us. Instead of setting properties one by one, you can pass the CommandText and Connection directly to the constructor of the command. I filled a DataTable to pass to the UI code. The DataTable does nor require an open connection the way a DataReader does. I made the connection string a Form level variable because I expect it may be used in more than one place.

Private OPConStr As String = "Data Source = Database.db"

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    Dim dt As DataTable = Nothing
    Try
        dt = GetMenuData(113)
    Catch ex As Exception
        MessageBox.Show(ex.Message)
    End Try
    If dt.Rows.Count > 0 Then
        Order.Label3.Text = String.Join(", ", {dt(0)("ItemID").ToString, dt(0)("Name").ToString, dt(0)("Price")})
    Else
        MessageBox.Show("No records returned")
    End If
End Sub

Private Function GetMenuData(ID As Integer) As DataTable
    Dim SQLcommand As String = "SELECT * FROM Menu WHERE ItemID = 113"
    Dim dt As New DataTable
    Using Connection As New SQLiteConnection(OPConStr),
            CMD As New SQLiteCommand(SQLcommand, Connection)
        CMD.Parameters.Add("@ID", DbType.Int32).Value = ID
        Connection.Open()
        Using reader = CMD.ExecuteReader
            dt.Load(reader)
        End Using
    End Using
    Return dt
End Function