vb.net - OLEDB: Opening the connection does not work in the second time -

hello 2 ^_^...

i have vb code connects sever database , used virtual machine client (sorry have 1 computer :p).

on 1st click select query works, on second time not work anymore... here code:

from module

imports system.data.oledb  module oledb      public conn oledbconnection = new oledbconnection()     public comm oledbcommand     public reader oledbdatareader     public query string      sub connection()         try             conn.connectionstring = "provider=microsoft.jet.oledb.4.0;data source=\\vboxsvr\documents\sampledb.mdb;user id=admin;password="         catch ex exception             msgbox(ex.tostring)         end try     end sub end module 

from form

imports system.data.oledb  public class form1      private sub form1_load(byval sender system.object, byval e system.eventargs) handles mybase.load         call connection()     end sub      private sub button1_click(byval sender system.object, byval e system.eventargs) handles button1.click         try             conn.open()             query = "select * sample_tbl"             comm = new oledbcommand(query, conn)             reader = comm.executereader              while reader.read                 listbox1.items.add("number: " + reader("number").tostring + "letter: " + reader("letter").tostring)             end while             conn.close()         catch ex exception             msgbox(ex.tostring)                     conn.dispose()         end try     end sub end class 

the error

the connectionstring property has not been initialized

the line of error on form code

conn.open

thank :-)

from msdn:

we recommend close or dispose of connection when finished using in order return connection pool.

this means db connections should created, opened , closed each use. less critical access, doing have prevented problem encountered:

module oledb      public conn oledbconnection = new oledbconnection()     public comm oledbcommand     public reader oledbdatareader 

creating 1 global oledbcommand object asking trouble. unlike connection object settings , properties are same, dbcommand object specific query. reusing them can result in things last use carrying over. code posted not encounter because does create new 1 each time:

comm = new oledbcommand(query, conn) 

however, old 1 not closed or disposed of. in question code unable create new dbcommand objects after while because system resources depleted.

in end, only thing saved using global command object simply: dim. still other problems can result reusing datareader objects.

connections

it can tedious create new connection each time need one, if no other reason having copy connection string strewn on code. answer not global connection, helper method:

public function getconnection(optional usr string = "admin",                        optional pw string = "") oledb.oledbconnection      return new oledb.oledbconnection( _         string.format("provider=microsoft.jet.oledb.4.0;data source={0};user id={1};password={2};",                       dbfile, usr, pw)) end function 

note: use connectionstringbuilder here, think oledb version decidedly less easy use (compared mysql version instance).

using blocks

using blocks should used of these db provider objects. example:

using dbcon oledbconnection = getconnection()     ... end using 

using creates new block scope - target variable declared , created @ start of block , exists in block. object closed , disposed @ end cleaned up.

since oledbcommand object should disposed of after use, these can employ using block. trying reuse global oledbdatareader can result in invalidoperationexception:

there open datareader associated command must closed first.

since not closing , disposing of datareader, may matter of time. these can employ using blocks. your code revised:

dim sql = "select * sample_tbl" using dbcon oledbconnection = getconnection()     using cmd new oledbcommand(sql, dbcon)         dbcon.open()         using rdr oledbdatareader = cmd.executereader             if rdr.hasrows                 while rdr.read                     listbox1.items.add("number: " & rdr("number").tostring                                         & "letter: " + rdr("letter").tostring)                 end while             end if         end using     end using end using 

everything needs closed , disposed of, be. should created anew, is. note can reduce indentation/number of using blocks, stacking or including more 1 object:

using dbcon oledbconnection = getconnection(),     cmd new oledbcommand(sql, dbcon)     ' ... end using     ' close , dispose of both dbcon , cmd objects