2 DataReaders sharing 1 sqlConnection? 1

[James1981]

Is it possible to have 2 datareaders sharing one connection? When I try it I keep getting an error telling me I must close one datareader before another can use the same connection. If i close the datareader, the data is not passed into the DropDownlist (which is required). Is there anyway of pushing the data into the DropDownlist and making it stay there, so that I can close the 1st DataReader and then reuse the connection, or must I use another connection?

Regards

James.

 

[Zarcom]

Just run the loop you have that puts the data from the reader into the list.
I may be missing what you are asking if so please try to explain so I can more readily help you. That'l do donkey, that'l do
Mark
If you are unsure of forum etiquette check here faq796-2540

 

[James1981]

Perhaps the code would shed some light on what i am trying to do:

// Bind Marketing Results to Drop down list
// Create Instance of Connection and Command Object
SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
SqlCommand cmdListMarketing_results = new SqlCommand("proc_list_marketing_results",myConnection);

// Mark the Command as a SPROC
cmdListMarketing_results.CommandType = CommandType.StoredProcedure;

// Open connections and perform exec
myConnection.Open();
SqlDataReader myMarketing_results_reader = cmdListMarketing_results.ExecuteReader();

// Configure drop down list
listMarketing_results.DataSource = myMarketing_results_reader;

listMarketing_results.DataValueField = "sport_id";
listMarketing_results.DataTextField = "sport_name";
listMarketing_results.DataBind();
listMarketing_results.Items.Insert(0, "-----------------------"
listMarketing_results.Items.Insert(0, "Select Option"


// Bind Sports to drop down box
SqlCommand cmdListSports = new SqlCommand("proc_list_sports", myConnection);

// Mark the Command as a SPROC
cmdListSports.CommandType = CommandType.StoredProcedure;

// Open connections and perform exec
SqlDataReader mySports_reader = cmdListSports.ExecuteReader();

// Configure drop down list
listSports.DataSource = mySports_reader;
listSports.DataValueField = "sport_id";
listSports.DataTextField = "sport_name";
listSports.DataBind();
listSports.Items.Insert(0, "-----------------------"
listSports.Items.Insert(0, "Select Sport"

---------------------------end of code

so what i am trying to achieve is get the 2 datareaders (myMarketing_report_reader and mySports_reader) to share the same connection. When i try and do this i get an error saying that there is already a datareader (myMarketing_report_reader) that is using the connection and this must be closed first. However, when i close the marketing reader the data is lost in the dropdownlist....

Do i need to use two seperate connections, or is there away of keeping the data in the dropdownlist and closing the datareader.

James

 

[Zarcom]

Just off the top of my head try manually looping through the reader to put the data in the list then close it and do the same with the second one.


do while Not myMarketing_results_reader.eof
listMarketing_results.Items.Add(....) That'l do donkey, that'l do
Mark
If you are unsure of forum etiquette check here faq796-2540

 

[James1981]

Ok, but this seems to be a bit of an archaic method of doing, it. I like the way that the datareader does all the looping automatically when you call bind().

I would like it if there was a way of having making the bind stick... but if this is impossible then I see your solution as the only option, other than creating another connection, which would be resource heavy..

Any last thoughts?

James.

 

[Zarcom]

Hey James. It is a rather archaic method I supposed. Your simply not making use of the capabilities that are included in the objects, but it would work.

However, having said that I did create a test project and did exactly what you are looking for. This is the code I used in my page_Load event.

If Not IsPostBack Then
Try
'open a connection
SqlConnection1.Open()

sdrShipping = scmdShippers.ExecuteReader

drdShippers.DataSource = sdrShipping
drdShippers.DataValueField = "ShipperID"
drdShippers.DataTextField = "CompanyName"
drdShippers.DataBind()

sdrShipping.Close()

sdrSuppliers = scmdSuppliers.ExecuteReader

drdSuppliers.DataSource = sdrSuppliers
drdSuppliers.DataValueField = "SupplierID"
drdSuppliers.DataTextField = "CompanyName"
drdSuppliers.DataBind()

sdrSuppliers.Close()

Finally
SqlConnection1.Close()
End Try
End If
That'l do donkey, that'l do
Mark
If you are unsure of forum etiquette check here faq796-2540

 

[James1981]

Hi Mark,

I tried closing the datareader after doing the bind and that solved the problem, so i don't have to use the workaround with the while() loop.

Thanks for the help, this thread has been v helpful.

James.

 

[Zarcom]

Glad to be of service James That'l do donkey, that'l do
Mark
If you are unsure of forum etiquette check here faq796-2540