Inner Loop function been abort due to existing open DataReader with same connection

[wonder_gal]

I have coded a main program having the database connection string being defined, and the main program calling other sub programs using the database connection defined in main program.
I got this error message here.
"System.InvalidOperationException. There is already an open DataReader associated with this Connection which must be closed first. at System.Data.SqlClient.SqlCommand.ValidateCommand(String method, Boolean executing) ..."

Public Sub Main()
        Dim oConn As SqlClient.SqlConnection
 Dim oCommand_ValidBroadcast As SqlClient.SqlCommand
 Dim oReaderValidBroadcast As SqlClient.SqlDataReader
        [B]oConn[/B] = GetConnection_Broadcast()
 .........................

                strSQL = "Select BCT_Group.GP_Type, BCT_Group_Contact.Mobile_No from BCT_Group, BCT_Job_Group, BCT_Group_Contact where BCT_Job_Group.Job_ID = " & Job_ID & " and BCT_Group.GP_ID = BCT_Job_Group.GP_ID and BCT_Group.GP_ID = BCT_Group_Contact.GP_ID and BCT_Group.GP_Type <> 'Blocked'"
                oCommand_ValidBroadcast = oconn.CreateCommand
                oCommand_ValidBroadcast.CommandText = strSQL
                oCommand_ValidBroadcast.CommandType = CommandType.Text
                oReaderValidBroadcast = oCommand_ValidBroadcast.ExecuteReader
                Do While oReaderValidBroadcast.Read
                    Mobile_Number = oReaderValidBroadcast("Mobile_No")
                    Group_Type = oReaderValidBroadcast("GP_Type")
                        
                    If FunctionA(Mobile_Number, BP_ID, Cust_ID, Job_ID, Message, smsCharge, Message_Type, [B]oConn[/B]) Then
                         ............
                    Else
                         ............
                    End If
    
                Loop
                oReaderValidBroadcast.Close()
        oconn.Close()
End Sub
Private Function FunctionA(ByVal p_szMobile_No As String, _
                                 ByVal p_szBP_ID As Integer, _
                                 ByVal p_szCust_ID As Integer, _
                                 ByVal p_szJob_ID As Integer, _
                                 ByVal p_szMessage As String, _
                                 ByVal p_szCharge As Integer, _
                                 ByVal p_szMessage_Type As String, ByVal oConn As SqlClient.SqlConnection) As Boolean
        .......................
        Try
            
                FunctionB(msgId, Job_ID, p_szMobile_No, p_szMessage, p_szCharge, BP_GW_Shortcode, BP_ID, [B]oConn[/B])
                
        Catch Err As Exception
            ..................
        End Try
End Function
Private Function FunctionB(ByVal p_szMsgId As String, _
                                     ByVal p_szJob_ID As Integer, _
                                     ByVal p_szMobile_No As String, _
                                     ByVal p_szMessage As String, _
                                     ByVal p_szCharge As Integer, _
                                     ByVal p_szBP_GW_Shortcode As String, _
                                     ByVal p_szBP_ID As Integer, ByVal oConn As SqlClient.SqlConnection)
        Dim oCommand_Insert As SqlClient.SqlCommand
        Dim strSQL_Insert As String
        Try
            strSQL_Insert = "INSERT INTO BCT_Broadcast_Message_History(" _
                       & "MsgId," _
                       & "Job_ID," _
                       & "Mobile_No," _
                       & "Message," _
                       & "Charge," _
                       & "ShortCode," _
                       & "BP_ID)" _
                       & " VALUES('" _
                       & p_szMsgId & "'," _
                       & p_szJob_ID & ",'" _
                       & p_szMobile_No & "','" _
                       & Replace(p_szMessage, "'", "''") & "'," _
                       & p_szCharge & ",'" _
                       & p_szBP_GW_Shortcode & "'," _
                       & p_szBP_ID & ")"
            oCommand_Insert = [B]oConn[/B].CreateCommand
            oCommand_Insert.CommandText = strSQL_Insert
            oCommand_Insert.CommandType = CommandType.Text
            oCommand_Insert.ExecuteNonQuery()       'the error occurs at this line

        Catch err As Exception
            .............
        Finally
            oCommand_Insert.Dispose()
            oCommand_Insert = Nothing
        End Try
End Function

Anyone can help?

 

[jmcilhinney]

You can't have two DataReaders open using the same connection, plain and simple. You must close one DataReader before you can open another one. If you're trying to perfrom a query based on each record returned by the first DataReader then you'll either have to create a second connection or else read all the data first before perfroming the additional queries.

 

[wonder_gal]

Yup i can understand that. But the problem is that my coding here don't have a SECOND data reader also. The above shown is the main structure of my coding. Could u pls kindly point out to me what triggers this error?

Thanks alot.

 

[jmcilhinney]

It doesn't have to be a data reader. The same connection cannot be used for anything else while the data reader is open. Your Main method calls FunctionA while the data reader is open. FunctionA calls FunctionB and FunctionB tries to insert a record, presumably using the same connection that is busy serving the original data reader.

 

[albertkhor]

Do you use DataReader in the function? I see your code on Main(), you jump into function before close the DataReader. So i think maybe you use the Datareader in the function.

 

[wonder_gal]

Ya, from within the DataReader in my Main method, I jump into FunctionA, so I've been using only ONE DataReader.

And yes, my problem has been temporary solved with the creation of another database connection.

But in the long run, do anyone has any better suggestion to cure this?

I would really appreciate if anyone could share with us here. Thanks.

 

[jmcilhinney]

You've already been given the choices. A single connection can't do two things at once. Either finish one thing before you start the next or use multiple connections. I would think that the former would be the better choice in this case. Instead of accessing the database again for every row you get from the DataReader you should simply create all the new records and add them to a DataTable. Then when the DataReader has finsihed reading you can close it and use a DataAdapter and call Update once to insert all the new records in a batch. That's more efficient than repeatedly access the database too.

 

[wonder_gal]

Ok, thanks alot for ur comments here. I'll take this method into consideration.