Open Datareader must be closed first

[sodakotahusker]

I am getting a message trying to populate two dropdownlists
There is already an open DataReader associated with this Connection which must be closed first.

I changed my code so each dropdown has its own reader object, own command object and own connection. I still get error. I am closing the sqlreader. Even if I comment out the first set of code I get the error on the 2nd set. If I comment out the 2nd set - the first dropdownlist populates perfectly and life is good. This is driving me nuts! Anyone see something I am doing wrong? Thanks!


Dim mySqlConnection As New SqlConnection("server=(local);Trusted_Connection=yes;database=localstuff")

mySqlConnection.open()
Dim mySqlConnection2 As New SqlConnection("server=(local);Trusted_Connection=yes;database=localstuff")

mySqlConnection2.open()

Dim objCommand As New SqlCommand("select cityname,cityid from locations order by cityname", mySqlConnection)

Dim sqlReader = objCommand.ExecuteReader()

cityNames.DataSource = sqlReader
cityNames.DataTextField = "cityname"
cityNames.DataValueField = "cityid"

cityNames.databind()

sqlreader.close()
objcommand = nothing
mySqlConnection.close()

Dim objCommand2 As New SqlCommand("select * from churchcategories order by churchcategory", mySqlConnection2)

Dim sqlReader2 = objCommand2.ExecuteReader()

sqlreader2 = objcommand2.executereader()
cbochurchcategories.DataSource = sqlReader2
cbochurchcategories.DataTextField = "churchcategory"
cbochurchcategories.DataValueField = "churchcategoryid"

cbochurchcategories.databind()
sqlreader2.close

mySqlConnection2.close()

 

[cmn2]

I've had similar problems with datareaders and their connections in the past. I just don't use them anymore. Datasets are my choice.

 

[Kris11]

It looks like you have two ExecuteReader's and that could be the reason it is throwing you the error. Try changing this code

       Dim sqlReader2 = objCommand2.ExecuteReader() --This could be the error beccause you have another ExecuteReader following this
               
        sqlreader2 = objcommand2.executereader()
        cbochurchcategories.DataSource = sqlReader2
        cbochurchcategories.DataTextField = "churchcategory"
        cbochurchcategories.DataValueField = "churchcategoryid"

to

       Dim sqlReader2 as SQLDataReader
               
        sqlreader2 = objcommand2.executereader()
        cbochurchcategories.DataSource = sqlReader2
        cbochurchcategories.DataTextField = "churchcategory"
        cbochurchcategories.DataValueField = "churchcategoryid"

-Kris

 

[sodakotahusker]

Actually I solved the problem by coming the calls into one
select * from table1;select * from table2

instead of sqlreader.close after the first set
sqlreader.nextresult()

Worked like a champ.

I think I will use datasets in the future however. I am still learning how to use them so I was deploying sqlreader for now. Thanks for your inputs.