vb.net - Unable to read rows from MySQL due to InvalidOperationException -

so code, visual studio gives me following error message: "an unhandled exception of type "system.invalidoperationexception" occurred in system data" , points line .fill(table). details: "fill: selectcommand.connection property has not been initialized" "

edit: listviewsale listviewbox mysql data pasted in.

if give me input wrong, appreciate it. mysql login information handled earlier in code following code:

  dim connection new mysqlconnection(information here)           public sub updatelist()         dim sqlquery string = "select * produkt"         dim sqladapter new mysqldataadapter         dim sqlcommand new mysqlcommand         dim table new datatable         dim integer      sqlcommand         .commandtext = sqlquery         .connection = connection     end      sqladapter         .selectcommand = sqlcommand         .fill(table)     end      = 0 table.rows.count - 1         listviewsale             .items.add(table.rows(i)("id"))             .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         end     next end sub 

there several ways want more economically:

private dtproduct datatable  public sub updatelist()     dim sqlquery string = "select a,b,c,q,e ... produkt"      using dbcon new mysqlconnection(mysqlconnstr)         using cmd new mysqlcommand(sqlquery, dbcon)              dtproduct = new datatable()              dbcon.open()             dtproduct.load(cmd.executereader)          end using     end using     ... 

1. dont use global connection object. create it, use , dispose of
   • you can however, use global connection string not littered on code
2. use using blocks close , dispose of things have .dispose method. allows them release resources allocate.
3. rather setting sql , connection properties command object, passes them constructor args. personal preference, once habit of it, makes code more concise , less forget key items.
4. if specify columns in sql can control order appear.
5. there no need dataadapter fill table. code above uses datareader so.

if use dataadpater, make global , configure fully. prevent having run code again. daproduct.fill(dtproduct) (can) refresh contents, getting new or changed data.

finally, listview ill suited db operations. first, stored string. means numerics price , quantity have converted use (or updates). second, using datagridview, code populate lv can replaced by:

dgvprod.datasource = dtproduct 

the dgv create , columns, , add rows no need loop or create each row. data remains in datatable , typed.