Solved

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

Posted on 2007-11-14
2
155 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

If you're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place! Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and l…
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 …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
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…

734 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