Solved

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

Posted on 2007-11-14
2
154 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

Suggested Solutions

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 …
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

685 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