• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 161
  • Last Modified:

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

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
jormillan
Asked:
jormillan
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
jormillanAuthor Commented:
"MultipleActiveResultSets=True;" worked great thanks a lot.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now