how to close datareader...

[yashsays]

hi everyone

i am tryin gto fill a couple of listboxes using stored procedures...and i m passing the sqlcommand object directly to listbox, instead of using datareader object...like this:
//
cmdInsert = New SqlCommand("Exec proc_po_number", objSql)
lstPONo.DataSource = cmdInsert.ExecuteReader
lstPONo.DataTextField = "po_number"
lstPONo.DataBind()

cmdInsert = New SqlCommand("Exec proc_wo_number", objSql)
lstWONo.DataSource = cmdInsert.ExecuteReader
lstWONo.DataTextField = "wo_number"
lstWONo.DataBind()

//
...now the problem occurs after secong ExecuteReader is called...it gives following error:

//There is already an open DataReader associated with this Connection which must be closed first. //

can anyone plz tel how can i close a datareader object if i m not using any.

if i go the other way, i.e. by using dtrList( a datareder object) and use:
//
dtrList=cmdInsert.ExecuteReader
lstPONo.DataSource=dtrList
//
..then i can easily call dtrPO.close and then rest of code will work fine....but someone plz tell me how to do it without using dtrPO.

thnx.

[dj4uk]

You must load the datareader into a local variable first otherwise you cannot call the close method.

cmdInsert = New SqlCommand("Exec proc_po_number", objSql)
SqlDataReader dtrStuff = cmdInsert.ExecuteReader
lstPONo.DataSource = dtrStuff
lstPONo.DataTextField = "po_number"
lstPONo.DataBind()

dtrStuff.Close()

cmdInsert = New SqlCommand("Exec proc_wo_number", objSql)
dtrStuff = cmdInsert.ExecuteReader
lstWONo.DataSource = dtrStuff
lstWONo.DataTextField = "wo_number"
lstWONo.DataBind()

dtrStuff.Close()

I would very strongly recommend you do this as not closing DataReader can seriously effect performance.

DJ