Solved

How can I execute an store procedure and read data when a another SQLDataReader is open?

Posted on 2007-11-14
2
153 Views
Last Modified: 2010-04-23
I need to execute a stored procedure from within a loop that is reading another stored procedure. Here is the code:

myConnection = fn_con()
myConnection.Open()

'Set the sp to run and asign the user object
strSql = "execute sp_sales"
cmdUser = New SqlCommand(strSql, myConnection)

'dReader reads from the SP
dReader = cmdUser.ExecuteReader()

'Get data while rows exist
Do While dReader.Read
   'If row exist read, else close connection stop the process
   If dReader.HasRows Then
       'do something
       strSql1 = "execute sp_query"
       cmdUser1 = New SqlCommand(strSql1, myConnection)
       dReader1 = cmdUser1.ExecuteReader()

       'If a record is found, bring the info
       If dReader1.HasRows Then
           strModePay = dReader1.GetString(0)
           strAccType = dReader1.GetString(1)
           strBankCode = dReader1.GetString(2)

           'Close reader
           dReader1.Close()
           myConnection.Close()
      End If
   Else
       dReader.Close()
       myConnection.Close()
       Return
   End If
Loop

'Closes reader and connection
dReader.Close()
myConnection.Close()

When I want to run the application is shows me this error "There is already an open DataReader associated with this Command which must be closed first." I understand what it means but I can find a way to execute the second sp in the way I want, any suggestions? (I have to use SP to get the info no queries on the code).
0
Comment
Question by:jormillan
2 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 50 total points
ID: 20283005
2 methods:
* create a second connection
* add this in the connection string of your current connection string: MultipleActiveResultSets=True;  (in case of sql server 2005)
* use myCommand.ExecuteReader(CommandBehavior.CloseConnection)
0
 

Author Comment

by:jormillan
ID: 20285070
"MultipleActiveResultSets=True;" worked great thanks a lot.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

861 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question