Unable to read rows from MySQL due to InvalidOperationException

Question

So basically this is my code, Visual Studio gives me the following error message: "An unhandled exception of type "System.InvalidOperationException" occurred in System Data" and it points to the line .Fill(Table). Details: "Fill: SelectCommand.Connection property has not been initialized" "

Edit: ListViewSale is the ListViewBox I would like the MySQL data to be pasted in.

If anyone could give me some input as to what is wrong, I would really appreciate it. The mysql login information is handled with earlier in the code with the following code:

  Dim connection As New MySqlConnection(INFORMATION HERE)       

  Public Sub updateList()
        Dim sqlQuery As String = "SELECT * FROM Produkt"
        Dim sqlAdapter As New MySqlDataAdapter
        Dim sqlCommand As New MySqlCommand
        Dim Table As New DataTable
        Dim i As Integer

    With sqlCommand
        .CommandText = sqlQuery
        .Connection = connection
    End With

    With sqlAdapter
        .SelectCommand = sqlCommand
        .Fill(Table)
    End With

    For i = 0 To Table.Rows.Count - 1
        With ListViewSale
            .Items.Add(Table.Rows(i)("id"))
            With .Items(.Items.Count - 1).SubItems
                .Add(Table.Rows(i)("Name"))
                .Add(Table.Rows(i)("Price"))
                .Add(Table.Rows(i)("Quantity"))
                .Add(Table.Rows(i)("Description"))
            End With
        End With
    Next
End Sub

Answer 1

There are several ways do what you want more economically:

Private dtProduct As DataTable

Public Sub updateList()
    Dim sqlQuery As String = "SELECT a,b,c,q,e ... FROM Produkt"

    Using dbcon As New MySqlConnection(mySQLConnStr)
        Using cmd As New MySqlCommand(sqlQuery, dbcon)

            dtProduct = New DataTable() 
            dbcon.Open()
            dtProduct.Load(cmd.ExecuteReader)

        End Using
    End Using
    ...

1. Dont use a global connection object. Create it, use it and dispose of it
   • You can however, use a global connection string so it is not littered all over your code
2. Use Using blocks to close and dispose of things which have a .Dispose method. This allows them to release resources they allocate.
3. Rather than setting the SQL and connection as properties for the Command object, this passes them as constructor args. This is just personal preference, but once you get into the habit of it, it makes the code more concise and less likely that you will forget those key items.
4. If you specify the columns in your SQL you can control the order they appear.
5. There is no need for a DataAdapter just to fill a table. The code above uses a DataReader to do so.

If you do use a DataAdpater, make it global and configure it fully. This will prevent you from having to run that code again. daProduct.Fill(dtProduct) will (can) refresh the contents, getting just any new or changed data.

Finally, the ListView is very ill suited for db operations. First, everything is stored as string. That means numerics like Price and Quantity have to be converted back for use (or updates). Second, using a DataGridView, all that code to populate the LV can be replaced by:

dgvProd.DataSource = dtProduct

The DGV will create and columns, and add all the rows with no need to loop or create each row. The data remains in the DataTable and Typed.