jormillan
asked on
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).
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).
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER