?
Solved

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

Posted on 2007-11-14
2
Medium Priority
?
157 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 200 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

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

Question has a verified solution.

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

This article explains how to create and use a custom WaterMark textbox class.  The custom WaterMark textbox class allows you to set the WaterMark Background Color and WaterMark text at design time.   IMAGE OF WATERMARKS STEPS Create VB …
1.0 - Introduction Converting Visual Basic 6.0 (VB6) to Visual Basic 2008+ (VB.NET). If ever there was a subject full of murkiness and bad decisions, it is this one!   The first problem seems to be that people considering this task of converting…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses

777 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