Help - datareader connectoin conflict?

  • Thread starter Thread starter David Watson
  • Start date Start date
D

David Watson

I have setup up a datareader, and when I try to loop through the
datareader, each time my application tries to run another query, the
following exception occurs:

there is already an open datareader associated with this connection
which must be closed first

The datareader and the query are both using seperate connections.
I'm pulling my hair out trying figure this one out.

Your suggestions are appreciated.
See code below...

Try
Dim conn1 As New
SqlClient.SqlConnection("server=SalesLogix;uid=sa;pwd=123;database=SLXTEST")
conn1.Open() 'used for datareader
Dim conn2 As New
SqlClient.SqlConnection("server=SalesLogix;uid=sa;pwd=123;database=SLXTEST")
conn2.Open() 'used for datareader
Dim conn3 As New
SqlClient.SqlConnection("server=SalesLogix;uid=sa;pwd=123;database=SLXTEST")
conn3.Open() 'used to get license count

Dim getAccountIDs As SqlClient.SqlCommand = New
SqlClient.SqlCommand("select accountid from slxmailerdata")
getAccountIDs.Connection = conn2
Dim myReader As SqlClient.SqlDataReader =
getAccountIDs.ExecuteReader() 'get account ids for cursor operation

Dim getLicCount As SqlClient.SqlCommand = New
SqlClient.SqlCommand
getLicCount.Connection = conn3


'Dim UpdateLicContractCount As New
SqlClient.SqlCommand("Update slxmailerdata SET numliccontracts = " &
licCount & " where accountid = '" & acID & "'")
Dim UpdateLicContractCount As New
SqlClient.SqlCommand("Update slxmailerdata SET numliccontracts = 0")
UpdateLicContractCount.Connection = conn1


lb.Items.Add("Updating contract count for license files
.... this will take a few minutes")
lb.Refresh()

Do While myReader.Read() 'update slxmailerdata table

acID = myReader.GetString(0)
strQuery1 = "select count(a.accountproductid) from
licaccountprodids a, contractinformation b WHERE b.accountproductid =
a.accountproductid AND b.accountid = '" & acID & "'"
getLicCount.CommandText = strQuery1
licCount = getLicCount.ExecuteScalar()
If licCount > 0 Then
strQuery2 = "Update slxmailerdata SET
numliccontracts = " & licCount & " where accountid = '" & acID & "'"
'UpdateLicContractCount.CommandText = strQuery2

FAILS HERE>> UpdateLicContractCount.ExecuteNonQuery()

lb.Items.Add(acID.ToString & " " &
licCount.ToString)
lb.Refresh()
End If
irecords = irecords + 1
Loop

conn3.Close()
conn2.Close()
conn1.close()



lb.Items.Add(irecords.ToString() + " records updated")
lb.Refresh()


Catch ex As Exception ' Catch the error.
MsgBox(ex.ToString) ' Show friendly error message.
End Try
 
You can only have one open datareader with an open connection this is
different then classic ado where one connection could have multliple
recordsets executing on the same connection. (ADO in the background opened
additional connections to handle this functionality. This situation is by
design. I suggest you create another connection and open a datareader on
the new connection for the functionality you want.
 
Back
Top