How can I execute an store procedure and read data when a another SQLDataReader is open?
Posted on 2007-11-14
I need to execute a stored procedure from within a loop that is reading another stored procedure. Here is the code:
myConnection = fn_con()
'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
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)
'Closes reader and connection
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).