Close the DataReader

[mentalhard]

How do i close the datareader object if i have used it like following:

Me.Repeater1.DataSource = cmd.ExecuteReader()

then i want to use the same datareader for another query but i get an error

There is already an open DataReader associated with this Command which must be closed first

usually i assign the cmd.ExecuteReader to the datareader and use a loop to read but this approach is totally new for me.

Thanks

P.S. Currently i have solved it with closing and opening the connection for each query.

cmd.Connection.Open() 
Me.Repeater1.DataSource = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection) 
Me.Repeater1.DataBind() 

cmd = New SqlCommand("SELECT COMMAND", connection) 
cmd.Connection.Open() ' HERE I OPEN THE CONN AGAIN 
Me.Repeater2.DataSource = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection) 
Me.Repeater2.DataBind() 
' ETC.

It works but, i am more than sure that there is a better approach than this as i have 10+ repeater controls and i believe that open/close connection 10 times for only few records per command is not the most effective way to go for.

 

[jmcilhinney]

Well, you can't use the same DataReader for another query. You have to close that DataReader and then create another for another query.

Second, if you have assigned the DataReader to the repeater's DataSource property the the repeater's DataSource property refers to the DataReader. Why can't you close it from there? That said, the logical thing to do would be to assign the DataReader to a variable first:

Dim reader As SqlDataReader = cmd.ExecuteReader()

Me.Repeater1.DataSource = reader
reader.Close()

An even better option would be to employ a using block:

Using reader As SqlDataReader = cmd.ExecuteReader()
    Me.Repeater1.DataSource = reader
End Using

 

[mentalhard]

[resolved]

Works like a charm

I couldn't figure out that practically i can declare datareader and then assing the command to it
Dim reader As SqlDataReader = cmd.ExecuteReader()

Thank you very much

 

[jmcilhinney]

I couldn't figure out that practically i can declare datareader and then assing the command to it

You're not doing anything of the sort. You're declaring a variable, calling a method of the Command and then assigning the result of that function, which is a DataReader, to the variable.

 

[mentalhard]

Yeah but i was missing that part (line)

Dim reader As SqlDataReader = cmd.ExecuteReader()

instead i had only this:

Me.Repeater1.DataSource = cmd.ExecuteReader()

so i was not able manipulating the reader object including the call of the close method.

However, now it has been fixed and life goes on lol

Thanks ones again for the help