There is already an open DataReader associated with this Command which must be closed first.

There is already an open DataReader associated with this Command which must be closed first.

• DannoMontano

  If I understand, I must to close every time the connection for use a new query (Sorry for my very bad english ...)
  

• mjeter

  Hi There,

  I hope someone can help me. I am running into a few bizarre things that I am hoping someone can help me understand.

  I get this error on one web server, but not the other: There is already an open DataReader associated with this Connection which must be closed first.

  I have a SQL2000 DB sitting on DBSERVER1 and my aspx files sitting on DEVELOP1, which is working fine. It has now come time to put this in to production, so we have setup a new site on PRODUCTION1 (setup with the same settings and files as DEVELOP1) and straight away I get the above error…

  Both servers have .net 2.0 redistributable installed, however, when an error is produced on both environments\servers it refers to ‘Version Information: Microsoft .NET Framework Version:1.1.4322.2300; ASP.NET Version:1.1.4322.2300’ at the bottom of the page.

  DEVELOP1 has the .net 2.0 sdk installed and the PRODUCTION1 doesn’t, I don’t know if this makes any difference

  I have been reading up on this error (this page being the most helpful) and I can’t understand how this can be happening.

  Here is a snippet of the code:

  Function DisplayCurrentLogin() as boolean

  DisplayCurrentLogin = false

  'set declarations

  dim objConn as OleDBConnection

  dim objCmdMgr as OledbCommand

  dim objDRMgr as OleDbDataReader

  dim objCmdAdmin as OledbCommand

  dim objDRAdmin as OleDbDataReader

  dim strSQLMgr as string

  dim strSQLAdmin as string

  dim strConn as string

  dim strLogonUser as string = request.ServerVariables("LOGON_USER")

  strLogonUser = lcase(right(strLogonUser, (len(strLogonUser) - instr(strLogonUser, "\"))))

  strConn = Provider=SQLOLEDB;Data Source=dbserver1;Initial Catalog=dbname;User ID=uid;Password=pwd

  'set a new connection object

  objConn = New OleDBConnection(strConn)

  'open db connection

  objConn.Open()

  'build query string

  strSQLMgr = "select top 1 pertitle + ' ' + perknownas + ' ' + persurname from staffdetails where domainlogin = '" & strLogonUser & "' order by persurname"

  strSQLAdmin = "select top 1 title + ' ' + knownas + ' ' + surname from authorisedusers where domainlogin = '" & strLogonUser & "' order by surname"

  'create a new db command object

  objCmdMgr = New OledbCommand(strSQLMgr, objConn)

  objCmdAdmin = New OledbCommand(strSQLAdmin, objConn)

  'perform db object tasks

  objDRMgr = objCmdMgr.ExecuteReader()

  objDRAdmin = objCmdAdmin.ExecuteReader()

  objDRMgr.read()

  objDRAdmin.read()

  if objDRMgr.hasrows then

  lblHeaderPreCloseLogin.text = objDRMgr.item(0)

  DisplayCurrentLogin = true

  end if

  
  

  if objDRAdmin.hasrows then

  lblHeaderPreCloseLogin.text = objDRAdmin.item(0)

  DisplayCurrentLogin = true

  end if

  'close db connection

  objConn.Close()

  End function

  If you require any more information regarding this, please let me know.

  Hope someone can provide me with some suggestions or ideas.
  

  Thanks,
  Nathan.

  
  

• Jangid

  Ok, but what about if the multiple connections have to be inside a clr procedure
  
  SQL Server 2005 returns an Exception like that:
  
  System.InvalidOperationException: There is already an open DataReader associated with this Command which must be closed first.
  
  Exactly the same as before and when I add "MultipleActiveResultSets=True" to the connection string it says:
  
  System.InvalidOperationException: The only additional connection string keyword that may be used when requesting the context connection is the Type System Version keyword.
  
  
  

• iming

  We have the same problem but with 1.1net and sql server 2005. We are prefectly sure, after the series of debugging that our reader and connection is closed properly but we keep getting "There is already an open DataReader associated with this Command which must be closed first."

  Are there any found bugs for .net 1.1 and sql2005 We cannot use MARS as 1.1 provider doesn'deal with it.

  
  

• Jeps

  I made changes in my connection string as per your instruction. But, still it gives the same error. I did same thing using Access as database using Oledb connection. It work well. Now i would like to do the same using SQL 2000. Please help me

  Dim con As SqlConnection

  Dim da As SqlDataAdapter

  Dim ds As New DataSet()

  Dim objcmd As SqlCommand

  Dim objDataReader As SqlDataReader

  Dim ConnectionString As String

  Dim STRSQL As String

  Dim rec_count1 As Integer

  ConnectionString="Data Source=Server07;Initial Catalog=mydata;Persist Security Info=True;MultipleActiveResultSets=True;User ID=xyz;password=1234"

  con = New SqlConnection(strcon)

  con.Open()

  STRSQL = "SELECT * From training_date WHERE course = '" & Trim(CourseList.Text) & "' AND Tr_date >='" & Now() & "'"

  objcmd = New SqlCommand(STRSQL, cn)

  objDataReader = objcmd.ExecuteReader()

  Do While objDataReader.Read() = True

  STRSQL = " Select * from training WHERE (TR_date ='" & CDate(objDataReader("tr_date")) & "' and course='" & objDataReader("course") & "')"

  da = New SqlDataAdapter(STRSQL, cn)

  da.Fill(ds, "Training") ' ---Here it gives error for There is already an open DataReader associated with this Command which must be closed first.

  rec_count1 = ds.Tables("Training").Rows.Count

  ''---'--Add tr_date to tr_date list if <5 student are ragistered for each date

  If rec_count1 < 5 Then

  tr_dateList.Items.Add(objDataReader("tr_date"))

  End If

  ds.Clear()

  Loop

  objDataReader.Close()

  
  

• Vatroslav Mihalj

  This is due to a change in the default setting for MARs.  It used to be on by default and we changed it to off by default post RC1.  So just change your connection string to add it back (add MultipleActiveResultSets=True to connection string).
  
  
  
  

• Joe Buys

  Matt Neerincx wrote:

  This is due to a change in the default setting for MARs.  It used to be on by default and we changed it to off by default post RC1.  So just change your connection string to add it back (add MultipleActiveResultSets=True to connection string).

  
  
  Arrrggghhhh...!!!  THANK YOU!!
  
  At least I can stop chasing this thing down and assuming I must be an idiot (well... perhaps I should not be so quick on that one).
  
  If you don't mind me asking... Why the last minute change to something that has been in place for so long
  

• MyP3uK

  MultipleActiveResultSets requires that the provider support it.

  SQL2000 doesn't support MultipleActiveResultSets.

  This was implemented on SQL2005.

  Hope that helps.

  
  

• John Bar

  Yes, you need to talk to a SQL 2005 server and use .NET 2.0 to get this feature. The feature requires both client and server support to work.
  
  

• Nian2000

  This occurs when you have multiple DataReaders open concurrently on the same connection, ie you call SqlCommand.ExecuteReader but don't close the SqlDataReader returned by this method before calling it again (either on the same command or another command on the same connection).
  
  

• Liebethal

  Not quite... this is true for most cases, but if you are running sql-2005 and using ado.net-2.0 then you can have multiple active resultsets (MARS) open on a single connection... however you must set the switch in the connection string.
  
  I use this all the time with sql-2005 and ado.net-2.0 and, except for MS changing the MARS defaults from on to off (see above) it works just fine.
  

• meiki

  Hello,
  
  I tried to make as I understood but I am confronted with the same error however I put MultipleActiveResultSets=True well at the chains of connection
  
  Syntax presents myself thus at the time of the call :
  
  

  Private ReadOnly SqlCon As System.Data.SqlClient.SqlConnection

  Public Function Insert(ByRef Query As String) As Integer

  Insert = New System.Data.SqlClient.SqlCommand(Query, SqlCon).ExecuteNonQuery

  End Function
  
  I don't understand, the order is however single since it has been just created...  Can you help me
  
  SqlCon is opened with ConnectionString = "MultipleActiveResultSets=True;User Id=sa;Initial Catalog=mydb;Data Source=SOLEIL;" and I use MSDE

  
  

• Blastobi

  I'm not sure I understand the question, but as far as I'm aware, MARS is only available for SQL-2005 connections, at this stage.
  

• Dariusdm

  Ok, I understand ! But I need MSQE (I haven't money for buy the final version). Thanks for your help. Bye and good programmation !
  
• There is already an open DataReader associated with this Command which must be closed first.