|
web
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
There is already an open DataReader associated with this Connection which must be closed firstAs I read data from tblA, I want to populate tblB. I use SQLDataReader for both tables. I do not use thread. When I ExecuteReader on tblB, I get the error "There is already an open DataReader associated with this Connection which must be closed first." How can I fix this error ? For each DataReader, do I want to open and close the connection (in this case adoCon) to avoid this error ? Thank you. m_cmdSQL = New SqlClient.SqlCommand With m_cmdSQL .Connection = adoCon .CommandText = "SELECT * FROM tblA" End With m_drSQL = m_cmdSQL.ExecuteReader() Do While m_drSQL.Read sSQL = "insert into tblB (Account,name,Company)" sSQL = sSQL & (" VALUES ('" & m_drSQL.Item("Account") & "'") sSQL = sSQL & (" ,'" & m_drSQL.Item("Name") & "'") sSQL = sSQL & (" ,'" & m_drSQL.Item("company") & "')") m_cmdSQL2 = New SqlClient.SqlCommand With m_cmdSQL2 .Connection = adoCon .CommandText = sSQL End With m_drSQL2 = m_cmdSQL2.ExecuteReader() ---> ERROR: There is already an open DataReader associated with this Connection which must be closed first. rs.CloseRS() rs = Nothing Loop fniles,
As the error says, you can only have 1 open datareader per connection. You need to open a second connection to the database and use it for the second datareader. Kerry Moorman Show quoteHide quote "fniles" wrote: > I am using VB.NET 2003, SQL 2000, and SqlDataReader. > As I read data from tblA, I want to populate tblB. I use SQLDataReader for > both tables. I do not use thread. > When I ExecuteReader on tblB, I get the error "There is already an open > DataReader associated with this Connection which must be closed first." > How can I fix this error ? > For each DataReader, do I want to open and close the connection (in this > case adoCon) to avoid this error ? > Thank you. > > m_cmdSQL = New SqlClient.SqlCommand > With m_cmdSQL > .Connection = adoCon > .CommandText = "SELECT * FROM tblA" > End With > m_drSQL = m_cmdSQL.ExecuteReader() > Do While m_drSQL.Read > sSQL = "insert into tblB (Account,name,Company)" > sSQL = sSQL & (" VALUES ('" & m_drSQL.Item("Account") & "'") > sSQL = sSQL & (" ,'" & m_drSQL.Item("Name") & "'") > sSQL = sSQL & (" ,'" & m_drSQL.Item("company") & "')") > m_cmdSQL2 = New SqlClient.SqlCommand > With m_cmdSQL2 > .Connection = adoCon > .CommandText = sSQL > End With > m_drSQL2 = m_cmdSQL2.ExecuteReader() ---> ERROR: There is already > an open DataReader associated with this Connection which must be closed > first. > rs.CloseRS() > rs = Nothing > Loop > > > > > fniles wrote:
Show quoteHide quote > Do While m_drSQL.Read It makes no sense to call ExecuteReader on an INSERT statement.> sSQL = "insert into tblB (Account,name,Company)" > sSQL = sSQL & (" VALUES ('" & m_drSQL.Item("Account") & "'") > sSQL = sSQL & (" ,'" & m_drSQL.Item("Name") & "'") > sSQL = sSQL & (" ,'" & m_drSQL.Item("company") & "')") > m_cmdSQL2 = New SqlClient.SqlCommand > With m_cmdSQL2 > .Connection = adoCon > .CommandText = sSQL > End With > m_drSQL2 = m_cmdSQL2.ExecuteReader() ---> ERROR: There is already > an open DataReader associated with this Connection which must be closed > first. > rs.CloseRS() > rs = Nothing > Loop ExecuteReader is designed to retrieve data into a DataReader. To execute your INSERT statement, call ExecuteNonQuery. Thank you, all.
I changed it from ExecuteReader to ExecuteNonQuery, but without opening another Database connection, I still get the error "There is already an open DataReader associated with this Connection which must be closed first." Please confirm, if the following looks correct in terms of opening another Database connection: m_cmdSQL = New SqlClient.SqlCommand With m_cmdSQL .Connection = adoCon .CommandText = "SELECT * FROM tblA" End With m_drSQL = m_cmdSQL.ExecuteReader() '------------ OPENING ANOTHER DB CONNECTION ---------------- DBCon= New SqlClient.SqlConnection With DBCon .ConnectionString = DB_Path .Open() End With '---------------------------- Do While m_drSQL.Read sSQL = "insert into tblB (Account,name,Company)" sSQL = sSQL & (" VALUES ('" & m_drSQL.Item("Account") & "'") sSQL = sSQL & (" ,'" & m_drSQL.Item("Name") & "'") sSQL = sSQL & (" ,'" & m_drSQL.Item("company") & "')") m_cmdSQL2 = New SqlClient.SqlCommand With m_cmdSQL2 .Connection = DBCon ----> USE THE 2ND DB CONNECTION .CommandText = sSQL End With m_cmdSQL2.ExecuteQuery() rs.CloseRS() rs = Nothing Loop '------------ CLOSING THE 2ND DB CONNECTION ---------------- If Not DBConIs Nothing Then If DBConn.ConnectionString <> "" Then DBConn.Close() DBConn = Nothing End If End If '------------------------------------- Show quoteHide quote "Chris Dunaway" <dunaw***@gmail.com> wrote in message news:1166038463.191299.21130@j72g2000cwa.googlegroups.com... > fniles wrote: > >> Do While m_drSQL.Read >> sSQL = "insert into tblB (Account,name,Company)" >> sSQL = sSQL & (" VALUES ('" & m_drSQL.Item("Account") & "'") >> sSQL = sSQL & (" ,'" & m_drSQL.Item("Name") & "'") >> sSQL = sSQL & (" ,'" & m_drSQL.Item("company") & "')") >> m_cmdSQL2 = New SqlClient.SqlCommand >> With m_cmdSQL2 >> .Connection = adoCon >> .CommandText = sSQL >> End With >> m_drSQL2 = m_cmdSQL2.ExecuteReader() ---> ERROR: There is >> already >> an open DataReader associated with this Connection which must be closed >> first. >> rs.CloseRS() >> rs = Nothing >> Loop > > It makes no sense to call ExecuteReader on an INSERT statement. > ExecuteReader is designed to retrieve data into a DataReader. To > execute your INSERT statement, call ExecuteNonQuery. > In general, you cannot have two open queries to the same SQL Server database
from your application. Even if you use a different connection, ADO.NET is probably doing connection pooling/sharing. If you absolutely must have both queries open at once (or you need to have one query open while you issue NonQuery updates to the same database), there are two alternatives. 1) Load the first query into a DataTable or DataSet object instead of using a DataReader. This might not be a good idea of you have massive amounts of data coming back from the query. 2) Enable "MARS" in your SQL Server connect. (I think MARS stands for "Multiple Active Result Sets.") MARS is new in the 2005 version of SQL Server, and requires version 2.0 of the .NET Framework. Other databases, such as Oracle, support MARS-like features, although under a different name. You can find information about MARS in the Visual Studio documentation. Basically, you add an extra parameter to your connection string to enable it. ----- Tim Patrick - www.timaki.com Start-to-Finish Visual Basic 2005 Show quoteHide quote > Thank you, all. > I changed it from ExecuteReader to ExecuteNonQuery, but without > opening > another Database connection, I still get the error "There is already > an open > DataReader associated with this Connection which must be closed > first." > Please confirm, if the following looks correct in terms of opening > another > Database connection: > m_cmdSQL = New SqlClient.SqlCommand > With m_cmdSQL > .Connection = adoCon > .CommandText = "SELECT * FROM tblA" > End With > m_drSQL = m_cmdSQL.ExecuteReader() > '------------ OPENING ANOTHER DB CONNECTION ---------------- > DBCon= New SqlClient.SqlConnection > With DBCon > .ConnectionString = DB_Path > .Open() > End With > '---------------------------- > Do While m_drSQL.Read > sSQL = "insert into tblB (Account,name,Company)" > sSQL = sSQL & (" VALUES ('" & m_drSQL.Item("Account") & > "'") > sSQL = sSQL & (" ,'" & m_drSQL.Item("Name") & "'") > sSQL = sSQL & (" ,'" & m_drSQL.Item("company") & "')") > m_cmdSQL2 = New SqlClient.SqlCommand > With m_cmdSQL2 > .Connection = DBCon ----> USE THE 2ND DB CONNECTION > .CommandText = sSQL > End With > m_cmdSQL2.ExecuteQuery() > rs.CloseRS() > rs = Nothing > Loop > '------------ CLOSING THE 2ND DB CONNECTION ---------------- > If Not DBConIs Nothing Then > If DBConn.ConnectionString <> "" Then > DBConn.Close() > DBConn = Nothing > End If > End If > '------------------------------------- > > "Chris Dunaway" <dunaw***@gmail.com> wrote in message > news:1166038463.191299.21130@j72g2000cwa.googlegroups.com... > >> fniles wrote: >> >>> Do While m_drSQL.Read >>> sSQL = "insert into tblB (Account,name,Company)" >>> sSQL = sSQL & (" VALUES ('" & m_drSQL.Item("Account") & "'") >>> sSQL = sSQL & (" ,'" & m_drSQL.Item("Name") & "'") >>> sSQL = sSQL & (" ,'" & m_drSQL.Item("company") & "')") >>> m_cmdSQL2 = New SqlClient.SqlCommand >>> With m_cmdSQL2 >>> .Connection = adoCon >>> .CommandText = sSQL >>> End With >>> m_drSQL2 = m_cmdSQL2.ExecuteReader() ---> ERROR: There is >>> already >>> an open DataReader associated with this Connection which must be >>> closed >>> first. >>> rs.CloseRS() >>> rs = Nothing >>> Loop >> It makes no sense to call ExecuteReader on an INSERT statement. >> ExecuteReader is designed to retrieve data into a DataReader. To >> execute your INSERT statement, call ExecuteNonQuery. >> Thank you for your reply.
>Even if you use a different connection, ADO.NET is probably doing That's what I am thinking, by opening the 2nd connection, it actually using >connection pooling/sharing. the connection pooling. Is there anything bad with connection pooling ? Thanks Show quoteHide quote "Tim Patrick" <inva***@invalid.com.invalid> wrote in message news:e3b46976392a8c8eccf37b440a0@newsgroups.comcast.net... > In general, you cannot have two open queries to the same SQL Server > database from your application. Even if you use a different connection, > ADO.NET is probably doing connection pooling/sharing. If you absolutely > must have both queries open at once (or you need to have one query open > while you issue NonQuery updates to the same database), there are two > alternatives. > > 1) Load the first query into a DataTable or DataSet object instead of > using a DataReader. This might not be a good idea of you have massive > amounts of data coming back from the query. > > 2) Enable "MARS" in your SQL Server connect. (I think MARS stands for > "Multiple Active Result Sets.") MARS is new in the 2005 version of SQL > Server, and requires version 2.0 of the .NET Framework. Other databases, > such as Oracle, support MARS-like features, although under a different > name. You can find information about MARS in the Visual Studio > documentation. Basically, you add an extra parameter to your connection > string to enable it. > > ----- > Tim Patrick - www.timaki.com > Start-to-Finish Visual Basic 2005 > >> Thank you, all. >> I changed it from ExecuteReader to ExecuteNonQuery, but without >> opening >> another Database connection, I still get the error "There is already >> an open >> DataReader associated with this Connection which must be closed >> first." >> Please confirm, if the following looks correct in terms of opening >> another >> Database connection: >> m_cmdSQL = New SqlClient.SqlCommand >> With m_cmdSQL >> .Connection = adoCon >> .CommandText = "SELECT * FROM tblA" >> End With >> m_drSQL = m_cmdSQL.ExecuteReader() >> '------------ OPENING ANOTHER DB CONNECTION ---------------- >> DBCon= New SqlClient.SqlConnection >> With DBCon >> .ConnectionString = DB_Path >> .Open() >> End With >> '---------------------------- >> Do While m_drSQL.Read >> sSQL = "insert into tblB (Account,name,Company)" >> sSQL = sSQL & (" VALUES ('" & m_drSQL.Item("Account") & >> "'") >> sSQL = sSQL & (" ,'" & m_drSQL.Item("Name") & "'") >> sSQL = sSQL & (" ,'" & m_drSQL.Item("company") & "')") >> m_cmdSQL2 = New SqlClient.SqlCommand >> With m_cmdSQL2 >> .Connection = DBCon ----> USE THE 2ND DB CONNECTION >> .CommandText = sSQL >> End With >> m_cmdSQL2.ExecuteQuery() >> rs.CloseRS() >> rs = Nothing >> Loop >> '------------ CLOSING THE 2ND DB CONNECTION ---------------- >> If Not DBConIs Nothing Then >> If DBConn.ConnectionString <> "" Then >> DBConn.Close() >> DBConn = Nothing >> End If >> End If >> '------------------------------------- >> >> "Chris Dunaway" <dunaw***@gmail.com> wrote in message >> news:1166038463.191299.21130@j72g2000cwa.googlegroups.com... >> >>> fniles wrote: >>> >>>> Do While m_drSQL.Read >>>> sSQL = "insert into tblB (Account,name,Company)" >>>> sSQL = sSQL & (" VALUES ('" & m_drSQL.Item("Account") & "'") >>>> sSQL = sSQL & (" ,'" & m_drSQL.Item("Name") & "'") >>>> sSQL = sSQL & (" ,'" & m_drSQL.Item("company") & "')") >>>> m_cmdSQL2 = New SqlClient.SqlCommand >>>> With m_cmdSQL2 >>>> .Connection = adoCon >>>> .CommandText = sSQL >>>> End With >>>> m_drSQL2 = m_cmdSQL2.ExecuteReader() ---> ERROR: There is >>>> already >>>> an open DataReader associated with this Connection which must be >>>> closed >>>> first. >>>> rs.CloseRS() >>>> rs = Nothing >>>> Loop >>> It makes no sense to call ExecuteReader on an INSERT statement. >>> ExecuteReader is designed to retrieve data into a DataReader. To >>> execute your INSERT statement, call ExecuteNonQuery. >>> > > It has its advantages and disadvantages. But whatever the benefits, you will
still probably need to adjust your code to compensate. ----- Tim Patrick - www.timaki.com Start-to-Finish Visual Basic 2005 Show quoteHide quote > Thank you for your reply. > >> Even if you use a different connection, ADO.NET is probably doing >> connection pooling/sharing. >> > That's what I am thinking, by opening the 2nd connection, it actually > using > the connection pooling. > Is there anything bad with connection pooling ? > Thanks > > "Tim Patrick" <inva***@invalid.com.invalid> wrote in message > news:e3b46976392a8c8eccf37b440a0@newsgroups.comcast.net... > >> In general, you cannot have two open queries to the same SQL Server >> database from your application. Even if you use a different >> connection, ADO.NET is probably doing connection pooling/sharing. Tim,
This is news to me. Perhaps you can explain what you mean in a little more detail. Kerry Moorman Show quoteHide quote "Tim Patrick" wrote: > In general, you cannot have two open queries to the same SQL Server database > from your application. Am I the only one who is thinking 'what the hell is he doing this for?'?
A much more efficient approach would be to get the Sql Server to do the work. As far as I can see it is as simple as: Dim _con As New SqlConnection(<connection string>) Dim _com As New SqlCommand("insert into tblB(Account,name,Company) select Account,Name,company from tblA", _con _con.Open() _com.ExecuteNonQuery() _con.Close() But aside from that here is your code with the problem areas annotated: ' Turn both Option Strict and Option Explicit on m_cmdSQL = New SqlCommand() ' variable used without being explicitly defined 'should be Dim m_cmdSQL As New SqlCommand() With m_cmdSQL .Connection = adoCon ' adoCon is assumed to be a properly constructed SqlConnection object .CommandText = "SELECT * FROM tblA" End With m_drSQL = m_cmdSQL.ExecuteReader() ' variable used without being explicitly defined 'should be Dim m_drSQL As SqlDataReader = m_cmdSQL.ExecuteReader() DBCon = New SqlConnection() ' variable used without being explicitly defined 'should be Dim DBCon As New SqlConnection() With DBCon .ConnectionString = DB_Path .Open() End With ' the above can be better written as Dim DBCon As New SqlConnection(adoCon.ConnectionString) DBCon.Open() ' this way ensures that the connection string used for DBCon is the same as the connectionstring used adoCon ' moved to here because the SqlCommand object need to be instantiated only once Dim m_cmdSQL2 As New SqlCommand("insert into tblB (Account,name,Company) values(@account,@name,@company)", DBCon ' but this way some parameters are required and these need their values populated on each iteration m_cmdSQL2.Parameters.Add("@account") m_cmdSQL2.Parameters.Add("@name") m_cmdSQL2.Parameters.Add("@company") ' although Do While/Loop is effectively the same as While/End While, I consider the While/End While to be more intuitive and easier to read While m_drSQL.Read() ' supply the values for the parameters from the incoming values m_cmdSQL2.Parameters("@account").Value = m_drSQL("Account") m_cmdSQL2.Parameters("@name").Value = m_drSQL("Name") m_cmdSQL2.Parameters("@company").Value = m_drSQL("company") ' execute the insert statement m_cmdSQL2.ExecuteNonQuery() End While ' close the connection that was opened DBCon.Close() ' close the SqlDataReader object drSQL.Close() Now for the grumbly bit! The number of typos and other syntactically incorrectly elements in the code you posted indicates that the code you posted is not the code that you are trying to run because it would never compile. In future, if you are going to post a code fragment, the copy it and paste it verbatiom from your IDE. Once you do so, it might look like rubbish, so select the text in question, select Format/Rich Text (HTML) from the menu in Outlook Express and then select Format/Plain Text. Show quoteHide quote "fniles" <fni***@pfmail.com> wrote in message news:O5bJjVvHHHA.3668@TK2MSFTNGP02.phx.gbl... > Thank you, all. > I changed it from ExecuteReader to ExecuteNonQuery, but without opening > another Database connection, I still get the error "There is already an > open > DataReader associated with this Connection which must be closed first." > > Please confirm, if the following looks correct in terms of opening another > Database connection: > m_cmdSQL = New SqlClient.SqlCommand > With m_cmdSQL > .Connection = adoCon > .CommandText = "SELECT * FROM tblA" > End With > m_drSQL = m_cmdSQL.ExecuteReader() > '------------ OPENING ANOTHER DB CONNECTION ---------------- > DBCon= New SqlClient.SqlConnection > With DBCon > .ConnectionString = DB_Path > .Open() > End With > '---------------------------- > Do While m_drSQL.Read > sSQL = "insert into tblB (Account,name,Company)" > sSQL = sSQL & (" VALUES ('" & m_drSQL.Item("Account") & "'") > sSQL = sSQL & (" ,'" & m_drSQL.Item("Name") & "'") > sSQL = sSQL & (" ,'" & m_drSQL.Item("company") & "')") > m_cmdSQL2 = New SqlClient.SqlCommand > With m_cmdSQL2 > .Connection = DBCon ----> USE THE 2ND DB CONNECTION > .CommandText = sSQL > End With > m_cmdSQL2.ExecuteQuery() > rs.CloseRS() > rs = Nothing > Loop > '------------ CLOSING THE 2ND DB CONNECTION ---------------- > If Not DBConIs Nothing Then > If DBConn.ConnectionString <> "" Then > DBConn.Close() > DBConn = Nothing > End If > End If > '------------------------------------- > > "Chris Dunaway" <dunaw***@gmail.com> wrote in message > news:1166038463.191299.21130@j72g2000cwa.googlegroups.com... >> fniles wrote: >> >>> Do While m_drSQL.Read >>> sSQL = "insert into tblB (Account,name,Company)" >>> sSQL = sSQL & (" VALUES ('" & m_drSQL.Item("Account") & "'") >>> sSQL = sSQL & (" ,'" & m_drSQL.Item("Name") & "'") >>> sSQL = sSQL & (" ,'" & m_drSQL.Item("company") & "')") >>> m_cmdSQL2 = New SqlClient.SqlCommand >>> With m_cmdSQL2 >>> .Connection = adoCon >>> .CommandText = sSQL >>> End With >>> m_drSQL2 = m_cmdSQL2.ExecuteReader() ---> ERROR: There is >>> already >>> an open DataReader associated with this Connection which must be closed >>> first. >>> rs.CloseRS() >>> rs = Nothing >>> Loop >> >> It makes no sense to call ExecuteReader on an INSERT statement. >> ExecuteReader is designed to retrieve data into a DataReader. To >> execute your INSERT statement, call ExecuteNonQuery. >> > > Not *that's* a handy tip. I usually paste it into a text editor
and then copy and paste it into OE, which fixes the readability problem too. Thanks! Robin S. --------------------------------------- Show quoteHide quote "Stephany Young" <noone@localhost> wrote in message news:ebmZR31HHHA.4056@TK2MSFTNGP03.phx.gbl... > In future, if you are going to post a code fragment, the copy it and > paste it verbatiom from your IDE. Once you do so, it might look like > rubbish, so select the text in question, select Format/Rich Text > (HTML) from the menu in Outlook Express and then select Format/Plain > Text. > Thanks, I hate to be one of the few that ask that all-important
question--why are you doing that? -- Show quoteHide quote____________________________________ William (Bill) Vaughn Author, Mentor, Consultant Microsoft MVP INETA Speaker www.betav.com/blog/billva www.betav.com Please reply only to the newsgroup so that others can benefit. This posting is provided "AS IS" with no warranties, and confers no rights. __________________________________ Visit www.hitchhikerguides.net to get more information on my latest book: Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition) and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook) ----------------------------------------------------------------------------------------------------------------------- "Stephany Young" <noone@localhost> wrote in message news:ebmZR31HHHA.4056@TK2MSFTNGP03.phx.gbl... > Am I the only one who is thinking 'what the hell is he doing this for?'? > > A much more efficient approach would be to get the Sql Server to do the > work. > > As far as I can see it is as simple as: > > Dim _con As New SqlConnection(<connection string>) > > Dim _com As New SqlCommand("insert into tblB(Account,name,Company) select > Account,Name,company from tblA", _con > > _con.Open() > > _com.ExecuteNonQuery() > > _con.Close() > > But aside from that here is your code with the problem areas annotated: > > ' Turn both Option Strict and Option Explicit on > > m_cmdSQL = New SqlCommand() > ' variable used without being explicitly defined > 'should be > Dim m_cmdSQL As New SqlCommand() > > With m_cmdSQL > .Connection = adoCon > ' adoCon is assumed to be a properly constructed SqlConnection object > .CommandText = "SELECT * FROM tblA" > End With > > m_drSQL = m_cmdSQL.ExecuteReader() > ' variable used without being explicitly defined > 'should be > Dim m_drSQL As SqlDataReader = m_cmdSQL.ExecuteReader() > > DBCon = New SqlConnection() > ' variable used without being explicitly defined > 'should be > Dim DBCon As New SqlConnection() > > With DBCon > .ConnectionString = DB_Path > .Open() > End With > > ' the above can be better written as > Dim DBCon As New SqlConnection(adoCon.ConnectionString) > DBCon.Open() > ' this way ensures that the connection string used for DBCon is the same > as the connectionstring used adoCon > > ' moved to here because the SqlCommand object need to be instantiated only > once > Dim m_cmdSQL2 As New SqlCommand("insert into tblB (Account,name,Company) > values(@account,@name,@company)", DBCon > ' but this way some parameters are required and these need their values > populated on each iteration > m_cmdSQL2.Parameters.Add("@account") > m_cmdSQL2.Parameters.Add("@name") > m_cmdSQL2.Parameters.Add("@company") > > ' although Do While/Loop is effectively the same as While/End While, I > consider the While/End While to be more intuitive and easier to read > While m_drSQL.Read() > ' supply the values for the parameters from the incoming values > m_cmdSQL2.Parameters("@account").Value = m_drSQL("Account") > m_cmdSQL2.Parameters("@name").Value = m_drSQL("Name") > m_cmdSQL2.Parameters("@company").Value = m_drSQL("company") > ' execute the insert statement > m_cmdSQL2.ExecuteNonQuery() > End While > > ' close the connection that was opened > DBCon.Close() > > ' close the SqlDataReader object > drSQL.Close() > > Now for the grumbly bit! > > The number of typos and other syntactically incorrectly elements in the > code you posted indicates that the code you posted is not the code that > you are trying to run because it would never compile. > > In future, if you are going to post a code fragment, the copy it and paste > it verbatiom from your IDE. Once you do so, it might look like rubbish, so > select the text in question, select Format/Rich Text (HTML) from the menu > in Outlook Express and then select Format/Plain Text. > > > > "fniles" <fni***@pfmail.com> wrote in message > news:O5bJjVvHHHA.3668@TK2MSFTNGP02.phx.gbl... >> Thank you, all. >> I changed it from ExecuteReader to ExecuteNonQuery, but without opening >> another Database connection, I still get the error "There is already an >> open >> DataReader associated with this Connection which must be closed first." >> >> Please confirm, if the following looks correct in terms of opening >> another Database connection: >> m_cmdSQL = New SqlClient.SqlCommand >> With m_cmdSQL >> .Connection = adoCon >> .CommandText = "SELECT * FROM tblA" >> End With >> m_drSQL = m_cmdSQL.ExecuteReader() >> '------------ OPENING ANOTHER DB CONNECTION ---------------- >> DBCon= New SqlClient.SqlConnection >> With DBCon >> .ConnectionString = DB_Path >> .Open() >> End With >> '---------------------------- >> Do While m_drSQL.Read >> sSQL = "insert into tblB (Account,name,Company)" >> sSQL = sSQL & (" VALUES ('" & m_drSQL.Item("Account") & "'") >> sSQL = sSQL & (" ,'" & m_drSQL.Item("Name") & "'") >> sSQL = sSQL & (" ,'" & m_drSQL.Item("company") & "')") >> m_cmdSQL2 = New SqlClient.SqlCommand >> With m_cmdSQL2 >> .Connection = DBCon ----> USE THE 2ND DB CONNECTION >> .CommandText = sSQL >> End With >> m_cmdSQL2.ExecuteQuery() >> rs.CloseRS() >> rs = Nothing >> Loop >> '------------ CLOSING THE 2ND DB CONNECTION ---------------- >> If Not DBConIs Nothing Then >> If DBConn.ConnectionString <> "" Then >> DBConn.Close() >> DBConn = Nothing >> End If >> End If >> '------------------------------------- >> >> "Chris Dunaway" <dunaw***@gmail.com> wrote in message >> news:1166038463.191299.21130@j72g2000cwa.googlegroups.com... >>> fniles wrote: >>> >>>> Do While m_drSQL.Read >>>> sSQL = "insert into tblB (Account,name,Company)" >>>> sSQL = sSQL & (" VALUES ('" & m_drSQL.Item("Account") & >>>> "'") >>>> sSQL = sSQL & (" ,'" & m_drSQL.Item("Name") & "'") >>>> sSQL = sSQL & (" ,'" & m_drSQL.Item("company") & "')") >>>> m_cmdSQL2 = New SqlClient.SqlCommand >>>> With m_cmdSQL2 >>>> .Connection = adoCon >>>> .CommandText = sSQL >>>> End With >>>> m_drSQL2 = m_cmdSQL2.ExecuteReader() ---> ERROR: There is >>>> already >>>> an open DataReader associated with this Connection which must be closed >>>> first. >>>> rs.CloseRS() >>>> rs = Nothing >>>> Loop >>> >>> It makes no sense to call ExecuteReader on an INSERT statement. >>> ExecuteReader is designed to retrieve data into a DataReader. To >>> execute your INSERT statement, call ExecuteNonQuery. >>> >> >> > > Sorry, the ADO.NET dataReader is crippled and you cannot have more than
one dataReader open for a single connection. Stupid but true ! Thats just one of the disadvantages of ADO.NET. Thats what happened when MS stupidly decided to make the underlying structure of ADO.NET XML based. The Grand Master William (Bill) Vaughn wrote: Show quoteHide quote > Thanks, I hate to be one of the few that ask that all-important > question--why are you doing that? > > -- > ____________________________________ > William (Bill) Vaughn > Author, Mentor, Consultant > Microsoft MVP > INETA Speaker > www.betav.com/blog/billva > www.betav.com > Please reply only to the newsgroup so that others can benefit. > This posting is provided "AS IS" with no warranties, and confers no rights. > __________________________________ > Visit www.hitchhikerguides.net to get more information on my latest book: > Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition) > and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook) > ----------------------------------------------------------------------------------------------------------------------- > > "Stephany Young" <noone@localhost> wrote in message > news:ebmZR31HHHA.4056@TK2MSFTNGP03.phx.gbl... > > Am I the only one who is thinking 'what the hell is he doing this for?'? > > > > A much more efficient approach would be to get the Sql Server to do the > > work. > > > > As far as I can see it is as simple as: > > > > Dim _con As New SqlConnection(<connection string>) > > > > Dim _com As New SqlCommand("insert into tblB(Account,name,Company) select > > Account,Name,company from tblA", _con > > > > _con.Open() > > > > _com.ExecuteNonQuery() > > > > _con.Close() > > > > But aside from that here is your code with the problem areas annotated: > > > > ' Turn both Option Strict and Option Explicit on > > > > m_cmdSQL = New SqlCommand() > > ' variable used without being explicitly defined > > 'should be > > Dim m_cmdSQL As New SqlCommand() > > > > With m_cmdSQL > > .Connection = adoCon > > ' adoCon is assumed to be a properly constructed SqlConnection object > > .CommandText = "SELECT * FROM tblA" > > End With > > > > m_drSQL = m_cmdSQL.ExecuteReader() > > ' variable used without being explicitly defined > > 'should be > > Dim m_drSQL As SqlDataReader = m_cmdSQL.ExecuteReader() > > > > DBCon = New SqlConnection() > > ' variable used without being explicitly defined > > 'should be > > Dim DBCon As New SqlConnection() > > > > With DBCon > > .ConnectionString = DB_Path > > .Open() > > End With > > > > ' the above can be better written as > > Dim DBCon As New SqlConnection(adoCon.ConnectionString) > > DBCon.Open() > > ' this way ensures that the connection string used for DBCon is the same > > as the connectionstring used adoCon > > > > ' moved to here because the SqlCommand object need to be instantiated only > > once > > Dim m_cmdSQL2 As New SqlCommand("insert into tblB (Account,name,Company) > > values(@account,@name,@company)", DBCon > > ' but this way some parameters are required and these need their values > > populated on each iteration > > m_cmdSQL2.Parameters.Add("@account") > > m_cmdSQL2.Parameters.Add("@name") > > m_cmdSQL2.Parameters.Add("@company") > > > > ' although Do While/Loop is effectively the same as While/End While, I > > consider the While/End While to be more intuitive and easier to read > > While m_drSQL.Read() > > ' supply the values for the parameters from the incoming values > > m_cmdSQL2.Parameters("@account").Value = m_drSQL("Account") > > m_cmdSQL2.Parameters("@name").Value = m_drSQL("Name") > > m_cmdSQL2.Parameters("@company").Value = m_drSQL("company") > > ' execute the insert statement > > m_cmdSQL2.ExecuteNonQuery() > > End While > > > > ' close the connection that was opened > > DBCon.Close() > > > > ' close the SqlDataReader object > > drSQL.Close() > > > > Now for the grumbly bit! > > > > The number of typos and other syntactically incorrectly elements in the > > code you posted indicates that the code you posted is not the code that > > you are trying to run because it would never compile. > > > > In future, if you are going to post a code fragment, the copy it and paste > > it verbatiom from your IDE. Once you do so, it might look like rubbish, so > > select the text in question, select Format/Rich Text (HTML) from the menu > > in Outlook Express and then select Format/Plain Text. > > > > > > > > "fniles" <fni***@pfmail.com> wrote in message > > news:O5bJjVvHHHA.3668@TK2MSFTNGP02.phx.gbl... > >> Thank you, all. > >> I changed it from ExecuteReader to ExecuteNonQuery, but without opening > >> another Database connection, I still get the error "There is already an > >> open > >> DataReader associated with this Connection which must be closed first." > >> > >> Please confirm, if the following looks correct in terms of opening > >> another Database connection: > >> m_cmdSQL = New SqlClient.SqlCommand > >> With m_cmdSQL > >> .Connection = adoCon > >> .CommandText = "SELECT * FROM tblA" > >> End With > >> m_drSQL = m_cmdSQL.ExecuteReader() > >> '------------ OPENING ANOTHER DB CONNECTION ---------------- > >> DBCon= New SqlClient.SqlConnection > >> With DBCon > >> .ConnectionString = DB_Path > >> .Open() > >> End With > >> '---------------------------- > >> Do While m_drSQL.Read > >> sSQL = "insert into tblB (Account,name,Company)" > >> sSQL = sSQL & (" VALUES ('" & m_drSQL.Item("Account") & "'") > >> sSQL = sSQL & (" ,'" & m_drSQL.Item("Name") & "'") > >> sSQL = sSQL & (" ,'" & m_drSQL.Item("company") & "')") > >> m_cmdSQL2 = New SqlClient.SqlCommand > >> With m_cmdSQL2 > >> .Connection = DBCon ----> USE THE 2ND DB CONNECTION > >> .CommandText = sSQL > >> End With > >> m_cmdSQL2.ExecuteQuery() > >> rs.CloseRS() > >> rs = Nothing > >> Loop > >> '------------ CLOSING THE 2ND DB CONNECTION ---------------- > >> If Not DBConIs Nothing Then > >> If DBConn.ConnectionString <> "" Then > >> DBConn.Close() > >> DBConn = Nothing > >> End If > >> End If > >> '------------------------------------- > >> > >> "Chris Dunaway" <dunaw***@gmail.com> wrote in message > >> news:1166038463.191299.21130@j72g2000cwa.googlegroups.com... > >>> fniles wrote: > >>> > >>>> Do While m_drSQL.Read > >>>> sSQL = "insert into tblB (Account,name,Company)" > >>>> sSQL = sSQL & (" VALUES ('" & m_drSQL.Item("Account") & > >>>> "'") > >>>> sSQL = sSQL & (" ,'" & m_drSQL.Item("Name") & "'") > >>>> sSQL = sSQL & (" ,'" & m_drSQL.Item("company") & "')") > >>>> m_cmdSQL2 = New SqlClient.SqlCommand > >>>> With m_cmdSQL2 > >>>> .Connection = adoCon > >>>> .CommandText = sSQL > >>>> End With > >>>> m_drSQL2 = m_cmdSQL2.ExecuteReader() ---> ERROR: There is > >>>> already > >>>> an open DataReader associated with this Connection which must be closed > >>>> first. > >>>> rs.CloseRS() > >>>> rs = Nothing > >>>> Loop > >>> > >>> It makes no sense to call ExecuteReader on an INSERT statement. > >>> ExecuteReader is designed to retrieve data into a DataReader. To > >>> execute your INSERT statement, call ExecuteNonQuery. > >>> > >> > >> > > > > The power is still out here in parts of Redmond so please pardon my
prolonged absence (since Thursday last week)... While some providers (not ADO.NET) do support multiple operations on a single connection (like Oracle), they do so because (for the most part) their connections are so complex. SQL Server (and others have far simpler (and cheaper) connection classes that can only support a single operation at a time. This means it often makes sense for applications to open two or more connections to the server to handle independent operations. Ok, that said, whenever I see someone trying to open more than one connection I start to look more closely at the problem they're trying to solve. In many (too many) cases, the operation should have been done on the server--not on the client or with bulk copy. That's why I asked, is this trip really necessary... Show quoteHide quote "Master Programmer" <master_program***@outgun.com> wrote in message news:1166166187.619949.272620@f1g2000cwa.googlegroups.com... > Sorry, the ADO.NET dataReader is crippled and you cannot have more than > one dataReader open for a single connection. Stupid but true ! > > Thats just one of the disadvantages of ADO.NET. > > Thats what happened when MS stupidly decided to make the underlying > structure of ADO.NET XML based. > > The Grand Master > > > William (Bill) Vaughn wrote: >> Thanks, I hate to be one of the few that ask that all-important >> question--why are you doing that? >> >> -- >> ____________________________________ >> William (Bill) Vaughn >> Author, Mentor, Consultant >> Microsoft MVP >> INETA Speaker >> www.betav.com/blog/billva >> www.betav.com >> Please reply only to the newsgroup so that others can benefit. >> This posting is provided "AS IS" with no warranties, and confers no >> rights. >> __________________________________ >> Visit www.hitchhikerguides.net to get more information on my latest book: >> Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition) >> and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook) >> ----------------------------------------------------------------------------------------------------------------------- >> >> "Stephany Young" <noone@localhost> wrote in message >> news:ebmZR31HHHA.4056@TK2MSFTNGP03.phx.gbl... >> > Am I the only one who is thinking 'what the hell is he doing this >> > for?'? >> > >> > A much more efficient approach would be to get the Sql Server to do the >> > work. >> > >> > As far as I can see it is as simple as: >> > >> > Dim _con As New SqlConnection(<connection string>) >> > >> > Dim _com As New SqlCommand("insert into tblB(Account,name,Company) >> > select >> > Account,Name,company from tblA", _con >> > >> > _con.Open() >> > >> > _com.ExecuteNonQuery() >> > >> > _con.Close() >> > >> > But aside from that here is your code with the problem areas annotated: >> > >> > ' Turn both Option Strict and Option Explicit on >> > >> > m_cmdSQL = New SqlCommand() >> > ' variable used without being explicitly defined >> > 'should be >> > Dim m_cmdSQL As New SqlCommand() >> > >> > With m_cmdSQL >> > .Connection = adoCon >> > ' adoCon is assumed to be a properly constructed SqlConnection object >> > .CommandText = "SELECT * FROM tblA" >> > End With >> > >> > m_drSQL = m_cmdSQL.ExecuteReader() >> > ' variable used without being explicitly defined >> > 'should be >> > Dim m_drSQL As SqlDataReader = m_cmdSQL.ExecuteReader() >> > >> > DBCon = New SqlConnection() >> > ' variable used without being explicitly defined >> > 'should be >> > Dim DBCon As New SqlConnection() >> > >> > With DBCon >> > .ConnectionString = DB_Path >> > .Open() >> > End With >> > >> > ' the above can be better written as >> > Dim DBCon As New SqlConnection(adoCon.ConnectionString) >> > DBCon.Open() >> > ' this way ensures that the connection string used for DBCon is the >> > same >> > as the connectionstring used adoCon >> > >> > ' moved to here because the SqlCommand object need to be instantiated >> > only >> > once >> > Dim m_cmdSQL2 As New SqlCommand("insert into tblB >> > (Account,name,Company) >> > values(@account,@name,@company)", DBCon >> > ' but this way some parameters are required and these need their values >> > populated on each iteration >> > m_cmdSQL2.Parameters.Add("@account") >> > m_cmdSQL2.Parameters.Add("@name") >> > m_cmdSQL2.Parameters.Add("@company") >> > >> > ' although Do While/Loop is effectively the same as While/End While, I >> > consider the While/End While to be more intuitive and easier to read >> > While m_drSQL.Read() >> > ' supply the values for the parameters from the incoming values >> > m_cmdSQL2.Parameters("@account").Value = m_drSQL("Account") >> > m_cmdSQL2.Parameters("@name").Value = m_drSQL("Name") >> > m_cmdSQL2.Parameters("@company").Value = m_drSQL("company") >> > ' execute the insert statement >> > m_cmdSQL2.ExecuteNonQuery() >> > End While >> > >> > ' close the connection that was opened >> > DBCon.Close() >> > >> > ' close the SqlDataReader object >> > drSQL.Close() >> > >> > Now for the grumbly bit! >> > >> > The number of typos and other syntactically incorrectly elements in the >> > code you posted indicates that the code you posted is not the code that >> > you are trying to run because it would never compile. >> > >> > In future, if you are going to post a code fragment, the copy it and >> > paste >> > it verbatiom from your IDE. Once you do so, it might look like rubbish, >> > so >> > select the text in question, select Format/Rich Text (HTML) from the >> > menu >> > in Outlook Express and then select Format/Plain Text. >> > >> > >> > >> > "fniles" <fni***@pfmail.com> wrote in message >> > news:O5bJjVvHHHA.3668@TK2MSFTNGP02.phx.gbl... >> >> Thank you, all. >> >> I changed it from ExecuteReader to ExecuteNonQuery, but without >> >> opening >> >> another Database connection, I still get the error "There is already >> >> an >> >> open >> >> DataReader associated with this Connection which must be closed >> >> first." >> >> >> >> Please confirm, if the following looks correct in terms of opening >> >> another Database connection: >> >> m_cmdSQL = New SqlClient.SqlCommand >> >> With m_cmdSQL >> >> .Connection = adoCon >> >> .CommandText = "SELECT * FROM tblA" >> >> End With >> >> m_drSQL = m_cmdSQL.ExecuteReader() >> >> '------------ OPENING ANOTHER DB CONNECTION ---------------- >> >> DBCon= New SqlClient.SqlConnection >> >> With DBCon >> >> .ConnectionString = DB_Path >> >> .Open() >> >> End With >> >> '---------------------------- >> >> Do While m_drSQL.Read >> >> sSQL = "insert into tblB (Account,name,Company)" >> >> sSQL = sSQL & (" VALUES ('" & m_drSQL.Item("Account") & >> >> "'") >> >> sSQL = sSQL & (" ,'" & m_drSQL.Item("Name") & "'") >> >> sSQL = sSQL & (" ,'" & m_drSQL.Item("company") & "')") >> >> m_cmdSQL2 = New SqlClient.SqlCommand >> >> With m_cmdSQL2 >> >> .Connection = DBCon ----> USE THE 2ND DB CONNECTION >> >> .CommandText = sSQL >> >> End With >> >> m_cmdSQL2.ExecuteQuery() >> >> rs.CloseRS() >> >> rs = Nothing >> >> Loop >> >> '------------ CLOSING THE 2ND DB CONNECTION ---------------- >> >> If Not DBConIs Nothing Then >> >> If DBConn.ConnectionString <> "" Then >> >> DBConn.Close() >> >> DBConn = Nothing >> >> End If >> >> End If >> >> '------------------------------------- >> >> >> >> "Chris Dunaway" <dunaw***@gmail.com> wrote in message >> >> news:1166038463.191299.21130@j72g2000cwa.googlegroups.com... >> >>> fniles wrote: >> >>> >> >>>> Do While m_drSQL.Read >> >>>> sSQL = "insert into tblB (Account,name,Company)" >> >>>> sSQL = sSQL & (" VALUES ('" & m_drSQL.Item("Account") & >> >>>> "'") >> >>>> sSQL = sSQL & (" ,'" & m_drSQL.Item("Name") & "'") >> >>>> sSQL = sSQL & (" ,'" & m_drSQL.Item("company") & "')") >> >>>> m_cmdSQL2 = New SqlClient.SqlCommand >> >>>> With m_cmdSQL2 >> >>>> .Connection = adoCon >> >>>> .CommandText = sSQL >> >>>> End With >> >>>> m_drSQL2 = m_cmdSQL2.ExecuteReader() ---> ERROR: There is >> >>>> already >> >>>> an open DataReader associated with this Connection which must be >> >>>> closed >> >>>> first. >> >>>> rs.CloseRS() >> >>>> rs = Nothing >> >>>> Loop >> >>> >> >>> It makes no sense to call ExecuteReader on an INSERT statement. >> >>> ExecuteReader is designed to retrieve data into a DataReader. To >> >>> execute your INSERT statement, call ExecuteNonQuery. >> >>> >> >> >> >> >> > >> > > Fniles,
In VB.Net 2003 you can only use one connection at a time, therefore as you wrote already, you have to close and open them when you need them. Which is by the best way normal practise while this is as well good for the connection pooling. Cor Show quoteHide quote "fniles" <fni***@pfmail.com> schreef in bericht news:%23qMycAuHHHA.3952@TK2MSFTNGP02.phx.gbl... >I am using VB.NET 2003, SQL 2000, and SqlDataReader. > As I read data from tblA, I want to populate tblB. I use SQLDataReader for > both tables. I do not use thread. > When I ExecuteReader on tblB, I get the error "There is already an open > DataReader associated with this Connection which must be closed first." > How can I fix this error ? > For each DataReader, do I want to open and close the connection (in this > case adoCon) to avoid this error ? > Thank you. > > m_cmdSQL = New SqlClient.SqlCommand > With m_cmdSQL > .Connection = adoCon > .CommandText = "SELECT * FROM tblA" > End With > m_drSQL = m_cmdSQL.ExecuteReader() > Do While m_drSQL.Read > sSQL = "insert into tblB (Account,name,Company)" > sSQL = sSQL & (" VALUES ('" & m_drSQL.Item("Account") & "'") > sSQL = sSQL & (" ,'" & m_drSQL.Item("Name") & "'") > sSQL = sSQL & (" ,'" & m_drSQL.Item("company") & "')") > m_cmdSQL2 = New SqlClient.SqlCommand > With m_cmdSQL2 > .Connection = adoCon > .CommandText = sSQL > End With > m_drSQL2 = m_cmdSQL2.ExecuteReader() ---> ERROR: There is > already an open DataReader associated with this Connection which must be > closed first. > rs.CloseRS() > rs = Nothing > Loop > > > > Cor,
I'm confused as to what you mean by "In VB.Net 2003 you can only use one connection at a time". Can you elaborate? Kerry Moorman Show quoteHide quote "Cor Ligthert [MVP]" wrote: > Fniles, > > In VB.Net 2003 you can only use one connection at a time, therefore as you > wrote already, you have to close and open them when you need them. Which is > by the best way normal practise while this is as well good for the > connection pooling. > > Cor > > "fniles" <fni***@pfmail.com> schreef in bericht > news:%23qMycAuHHHA.3952@TK2MSFTNGP02.phx.gbl... > >I am using VB.NET 2003, SQL 2000, and SqlDataReader. > > As I read data from tblA, I want to populate tblB. I use SQLDataReader for > > both tables. I do not use thread. > > When I ExecuteReader on tblB, I get the error "There is already an open > > DataReader associated with this Connection which must be closed first." > > How can I fix this error ? > > For each DataReader, do I want to open and close the connection (in this > > case adoCon) to avoid this error ? > > Thank you. > > > > m_cmdSQL = New SqlClient.SqlCommand > > With m_cmdSQL > > .Connection = adoCon > > .CommandText = "SELECT * FROM tblA" > > End With > > m_drSQL = m_cmdSQL.ExecuteReader() > > Do While m_drSQL.Read > > sSQL = "insert into tblB (Account,name,Company)" > > sSQL = sSQL & (" VALUES ('" & m_drSQL.Item("Account") & "'") > > sSQL = sSQL & (" ,'" & m_drSQL.Item("Name") & "'") > > sSQL = sSQL & (" ,'" & m_drSQL.Item("company") & "')") > > m_cmdSQL2 = New SqlClient.SqlCommand > > With m_cmdSQL2 > > .Connection = adoCon > > .CommandText = sSQL > > End With > > m_drSQL2 = m_cmdSQL2.ExecuteReader() ---> ERROR: There is > > already an open DataReader associated with this Connection which must be > > closed first. > > rs.CloseRS() > > rs = Nothing > > Loop > > > > > > > > > > > Kerry,
Per client program of course. The server can use much more connections althoug that seems to be mostly be set to 100 maximum. Cor Show quoteHide quote "Kerry Moorman" <KerryMoor***@discussions.microsoft.com> schreef in bericht news:9D4BEB08-115B-4280-9437-E900A4CF0AA5@microsoft.com... > Cor, > > I'm confused as to what you mean by "In VB.Net 2003 you can only use one > connection at a time". > > Can you elaborate? > > Kerry Moorman > > > "Cor Ligthert [MVP]" wrote: > >> Fniles, >> >> In VB.Net 2003 you can only use one connection at a time, therefore as >> you >> wrote already, you have to close and open them when you need them. Which >> is >> by the best way normal practise while this is as well good for the >> connection pooling. >> >> Cor >> >> "fniles" <fni***@pfmail.com> schreef in bericht >> news:%23qMycAuHHHA.3952@TK2MSFTNGP02.phx.gbl... >> >I am using VB.NET 2003, SQL 2000, and SqlDataReader. >> > As I read data from tblA, I want to populate tblB. I use SQLDataReader >> > for >> > both tables. I do not use thread. >> > When I ExecuteReader on tblB, I get the error "There is already an open >> > DataReader associated with this Connection which must be closed first." >> > How can I fix this error ? >> > For each DataReader, do I want to open and close the connection (in >> > this >> > case adoCon) to avoid this error ? >> > Thank you. >> > >> > m_cmdSQL = New SqlClient.SqlCommand >> > With m_cmdSQL >> > .Connection = adoCon >> > .CommandText = "SELECT * FROM tblA" >> > End With >> > m_drSQL = m_cmdSQL.ExecuteReader() >> > Do While m_drSQL.Read >> > sSQL = "insert into tblB (Account,name,Company)" >> > sSQL = sSQL & (" VALUES ('" & m_drSQL.Item("Account") & "'") >> > sSQL = sSQL & (" ,'" & m_drSQL.Item("Name") & "'") >> > sSQL = sSQL & (" ,'" & m_drSQL.Item("company") & "')") >> > m_cmdSQL2 = New SqlClient.SqlCommand >> > With m_cmdSQL2 >> > .Connection = adoCon >> > .CommandText = sSQL >> > End With >> > m_drSQL2 = m_cmdSQL2.ExecuteReader() ---> ERROR: There is >> > already an open DataReader associated with this Connection which must >> > be >> > closed first. >> > rs.CloseRS() >> > rs = Nothing >> > Loop >> > >> > >> > >> > >> >> >> That's not correct Cor. You can instantiate and open as many concurrent
connection objects as you want in a single application and, in .NET, this has been the case since the .Net Framework 1.0. The only thing that you're limited by is available resources whether thay be at the client end or the server end. Show quoteHide quote "Cor Ligthert [MVP]" <notmyfirstn***@planet.nl> wrote in message news:uaYZmtAIHHA.420@TK2MSFTNGP02.phx.gbl... > Kerry, > > Per client program of course. The server can use much more connections > althoug that seems to be mostly be set to 100 maximum. > > Cor > > "Kerry Moorman" <KerryMoor***@discussions.microsoft.com> schreef in > bericht news:9D4BEB08-115B-4280-9437-E900A4CF0AA5@microsoft.com... >> Cor, >> >> I'm confused as to what you mean by "In VB.Net 2003 you can only use one >> connection at a time". >> >> Can you elaborate? >> >> Kerry Moorman >> >> >> "Cor Ligthert [MVP]" wrote: >> >>> Fniles, >>> >>> In VB.Net 2003 you can only use one connection at a time, therefore as >>> you >>> wrote already, you have to close and open them when you need them. Which >>> is >>> by the best way normal practise while this is as well good for the >>> connection pooling. >>> >>> Cor >>> >>> "fniles" <fni***@pfmail.com> schreef in bericht >>> news:%23qMycAuHHHA.3952@TK2MSFTNGP02.phx.gbl... >>> >I am using VB.NET 2003, SQL 2000, and SqlDataReader. >>> > As I read data from tblA, I want to populate tblB. I use SQLDataReader >>> > for >>> > both tables. I do not use thread. >>> > When I ExecuteReader on tblB, I get the error "There is already an >>> > open >>> > DataReader associated with this Connection which must be closed >>> > first." >>> > How can I fix this error ? >>> > For each DataReader, do I want to open and close the connection (in >>> > this >>> > case adoCon) to avoid this error ? >>> > Thank you. >>> > >>> > m_cmdSQL = New SqlClient.SqlCommand >>> > With m_cmdSQL >>> > .Connection = adoCon >>> > .CommandText = "SELECT * FROM tblA" >>> > End With >>> > m_drSQL = m_cmdSQL.ExecuteReader() >>> > Do While m_drSQL.Read >>> > sSQL = "insert into tblB (Account,name,Company)" >>> > sSQL = sSQL & (" VALUES ('" & m_drSQL.Item("Account") & >>> > "'") >>> > sSQL = sSQL & (" ,'" & m_drSQL.Item("Name") & "'") >>> > sSQL = sSQL & (" ,'" & m_drSQL.Item("company") & "')") >>> > m_cmdSQL2 = New SqlClient.SqlCommand >>> > With m_cmdSQL2 >>> > .Connection = adoCon >>> > .CommandText = sSQL >>> > End With >>> > m_drSQL2 = m_cmdSQL2.ExecuteReader() ---> ERROR: There is >>> > already an open DataReader associated with this Connection which must >>> > be >>> > closed first. >>> > rs.CloseRS() >>> > rs = Nothing >>> > Loop >>> > >>> > >>> > >>> > >>> >>> >>> > > Stephany,
Maybe I have understood it wrong, I always thought that it was only possible to work with one open and active connection at a time. (You can of course have thousands connections if you like). Maybe was VENUS already there before MARS. :-) CorShow quoteHide quote "Stephany Young" <noone@localhost> schreef in bericht news:Oc6MS$AIHHA.960@TK2MSFTNGP04.phx.gbl... > That's not correct Cor. You can instantiate and open as many concurrent > connection objects as you want in a single application and, in .NET, this > has been the case since the .Net Framework 1.0. > > The only thing that you're limited by is available resources whether thay > be at the client end or the server end. > > > "Cor Ligthert [MVP]" <notmyfirstn***@planet.nl> wrote in message > news:uaYZmtAIHHA.420@TK2MSFTNGP02.phx.gbl... >> Kerry, >> >> Per client program of course. The server can use much more connections >> althoug that seems to be mostly be set to 100 maximum. >> >> Cor >> >> "Kerry Moorman" <KerryMoor***@discussions.microsoft.com> schreef in >> bericht news:9D4BEB08-115B-4280-9437-E900A4CF0AA5@microsoft.com... >>> Cor, >>> >>> I'm confused as to what you mean by "In VB.Net 2003 you can only use one >>> connection at a time". >>> >>> Can you elaborate? >>> >>> Kerry Moorman >>> >>> >>> "Cor Ligthert [MVP]" wrote: >>> >>>> Fniles, >>>> >>>> In VB.Net 2003 you can only use one connection at a time, therefore as >>>> you >>>> wrote already, you have to close and open them when you need them. >>>> Which is >>>> by the best way normal practise while this is as well good for the >>>> connection pooling. >>>> >>>> Cor >>>> >>>> "fniles" <fni***@pfmail.com> schreef in bericht >>>> news:%23qMycAuHHHA.3952@TK2MSFTNGP02.phx.gbl... >>>> >I am using VB.NET 2003, SQL 2000, and SqlDataReader. >>>> > As I read data from tblA, I want to populate tblB. I use >>>> > SQLDataReader for >>>> > both tables. I do not use thread. >>>> > When I ExecuteReader on tblB, I get the error "There is already an >>>> > open >>>> > DataReader associated with this Connection which must be closed >>>> > first." >>>> > How can I fix this error ? >>>> > For each DataReader, do I want to open and close the connection (in >>>> > this >>>> > case adoCon) to avoid this error ? >>>> > Thank you. >>>> > >>>> > m_cmdSQL = New SqlClient.SqlCommand >>>> > With m_cmdSQL >>>> > .Connection = adoCon >>>> > .CommandText = "SELECT * FROM tblA" >>>> > End With >>>> > m_drSQL = m_cmdSQL.ExecuteReader() >>>> > Do While m_drSQL.Read >>>> > sSQL = "insert into tblB (Account,name,Company)" >>>> > sSQL = sSQL & (" VALUES ('" & m_drSQL.Item("Account") & >>>> > "'") >>>> > sSQL = sSQL & (" ,'" & m_drSQL.Item("Name") & "'") >>>> > sSQL = sSQL & (" ,'" & m_drSQL.Item("company") & "')") >>>> > m_cmdSQL2 = New SqlClient.SqlCommand >>>> > With m_cmdSQL2 >>>> > .Connection = adoCon >>>> > .CommandText = sSQL >>>> > End With >>>> > m_drSQL2 = m_cmdSQL2.ExecuteReader() ---> ERROR: There is >>>> > already an open DataReader associated with this Connection which must >>>> > be >>>> > closed first. >>>> > rs.CloseRS() >>>> > rs = Nothing >>>> > Loop >>>> > >>>> > >>>> > >>>> > >>>> >>>> >>>> >> >> > > Cor,
Yes, you have misunderstood that. You can have as many open and active connections in your client that the server will allow. Many times you absolutely need to work with more than one active connection at a time. An example is to deal with the original question in this series: to be able to process two datareaders at the same time. Each datareader requires its own connection, at least pre-.Net 2005. I'm not sure if that is still the case. Kerry Moorman Show quoteHide quote "Cor Ligthert [MVP]" wrote: > Stephany, > > Maybe I have understood it wrong, I always thought that it was only possible > to work with one open and active connection at a time. (You can of course > have thousands connections if you like). > > Maybe was VENUS already there before MARS. > > :-) > > Cor > > "Stephany Young" <noone@localhost> schreef in bericht > news:Oc6MS$AIHHA.960@TK2MSFTNGP04.phx.gbl... > > That's not correct Cor. You can instantiate and open as many concurrent > > connection objects as you want in a single application and, in .NET, this > > has been the case since the .Net Framework 1.0. > > > > The only thing that you're limited by is available resources whether thay > > be at the client end or the server end. > > > > > > "Cor Ligthert [MVP]" <notmyfirstn***@planet.nl> wrote in message > > news:uaYZmtAIHHA.420@TK2MSFTNGP02.phx.gbl... > >> Kerry, > >> > >> Per client program of course. The server can use much more connections > >> althoug that seems to be mostly be set to 100 maximum. > >> > >> Cor > >> > >> "Kerry Moorman" <KerryMoor***@discussions.microsoft.com> schreef in > >> bericht news:9D4BEB08-115B-4280-9437-E900A4CF0AA5@microsoft.com... > >>> Cor, > >>> > >>> I'm confused as to what you mean by "In VB.Net 2003 you can only use one > >>> connection at a time". > >>> > >>> Can you elaborate? > >>> > >>> Kerry Moorman > >>> > >>> > >>> "Cor Ligthert [MVP]" wrote: > >>> > >>>> Fniles, > >>>> > >>>> In VB.Net 2003 you can only use one connection at a time, therefore as > >>>> you > >>>> wrote already, you have to close and open them when you need them. > >>>> Which is > >>>> by the best way normal practise while this is as well good for the > >>>> connection pooling. > >>>> > >>>> Cor > >>>> > >>>> "fniles" <fni***@pfmail.com> schreef in bericht > >>>> news:%23qMycAuHHHA.3952@TK2MSFTNGP02.phx.gbl... > >>>> >I am using VB.NET 2003, SQL 2000, and SqlDataReader. > >>>> > As I read data from tblA, I want to populate tblB. I use > >>>> > SQLDataReader for > >>>> > both tables. I do not use thread. > >>>> > When I ExecuteReader on tblB, I get the error "There is already an > >>>> > open > >>>> > DataReader associated with this Connection which must be closed > >>>> > first." > >>>> > How can I fix this error ? > >>>> > For each DataReader, do I want to open and close the connection (in > >>>> > this > >>>> > case adoCon) to avoid this error ? > >>>> > Thank you. > >>>> > > >>>> > m_cmdSQL = New SqlClient.SqlCommand > >>>> > With m_cmdSQL > >>>> > .Connection = adoCon > >>>> > .CommandText = "SELECT * FROM tblA" > >>>> > End With > >>>> > m_drSQL = m_cmdSQL.ExecuteReader() > >>>> > Do While m_drSQL.Read > >>>> > sSQL = "insert into tblB (Account,name,Company)" > >>>> > sSQL = sSQL & (" VALUES ('" & m_drSQL.Item("Account") & > >>>> > "'") > >>>> > sSQL = sSQL & (" ,'" & m_drSQL.Item("Name") & "'") > >>>> > sSQL = sSQL & (" ,'" & m_drSQL.Item("company") & "')") > >>>> > m_cmdSQL2 = New SqlClient.SqlCommand > >>>> > With m_cmdSQL2 > >>>> > .Connection = adoCon > >>>> > .CommandText = sSQL > >>>> > End With > >>>> > m_drSQL2 = m_cmdSQL2.ExecuteReader() ---> ERROR: There is > >>>> > already an open DataReader associated with this Connection which must > >>>> > be > >>>> > closed first. > >>>> > rs.CloseRS() > >>>> > rs = Nothing > >>>> > Loop > >>>> > > >>>> > > >>>> > > >>>> > > >>>> > >>>> > >>>> > >> > >> > > > > > > > Stephany and Kerry,
I tested it this morning, you both are right, therefore I learned again something in this newsgroups. Thanks both, Cor Show quoteHide quote "Kerry Moorman" <KerryMoor***@discussions.microsoft.com> schreef in bericht news:A466E498-D19A-46CC-8812-BBB9F2AB6605@microsoft.com... > Cor, > > Yes, you have misunderstood that. > > You can have as many open and active connections in your client that the > server will allow. > > Many times you absolutely need to work with more than one active > connection > at a time. An example is to deal with the original question in this > series: > to be able to process two datareaders at the same time. Each datareader > requires its own connection, at least pre-.Net 2005. I'm not sure if that > is > still the case. > > Kerry Moorman > > > "Cor Ligthert [MVP]" wrote: > >> Stephany, >> >> Maybe I have understood it wrong, I always thought that it was only >> possible >> to work with one open and active connection at a time. (You can of course >> have thousands connections if you like). >> >> Maybe was VENUS already there before MARS. >> >> :-) >> >> Cor >> >> "Stephany Young" <noone@localhost> schreef in bericht >> news:Oc6MS$AIHHA.960@TK2MSFTNGP04.phx.gbl... >> > That's not correct Cor. You can instantiate and open as many concurrent >> > connection objects as you want in a single application and, in .NET, >> > this >> > has been the case since the .Net Framework 1.0. >> > >> > The only thing that you're limited by is available resources whether >> > thay >> > be at the client end or the server end. >> > >> > >> > "Cor Ligthert [MVP]" <notmyfirstn***@planet.nl> wrote in message >> > news:uaYZmtAIHHA.420@TK2MSFTNGP02.phx.gbl... >> >> Kerry, >> >> >> >> Per client program of course. The server can use much more connections >> >> althoug that seems to be mostly be set to 100 maximum. >> >> >> >> Cor >> >> >> >> "Kerry Moorman" <KerryMoor***@discussions.microsoft.com> schreef in >> >> bericht news:9D4BEB08-115B-4280-9437-E900A4CF0AA5@microsoft.com... >> >>> Cor, >> >>> >> >>> I'm confused as to what you mean by "In VB.Net 2003 you can only use >> >>> one >> >>> connection at a time". >> >>> >> >>> Can you elaborate? >> >>> >> >>> Kerry Moorman >> >>> >> >>> >> >>> "Cor Ligthert [MVP]" wrote: >> >>> >> >>>> Fniles, >> >>>> >> >>>> In VB.Net 2003 you can only use one connection at a time, therefore >> >>>> as >> >>>> you >> >>>> wrote already, you have to close and open them when you need them. >> >>>> Which is >> >>>> by the best way normal practise while this is as well good for the >> >>>> connection pooling. >> >>>> >> >>>> Cor >> >>>> >> >>>> "fniles" <fni***@pfmail.com> schreef in bericht >> >>>> news:%23qMycAuHHHA.3952@TK2MSFTNGP02.phx.gbl... >> >>>> >I am using VB.NET 2003, SQL 2000, and SqlDataReader. >> >>>> > As I read data from tblA, I want to populate tblB. I use >> >>>> > SQLDataReader for >> >>>> > both tables. I do not use thread. >> >>>> > When I ExecuteReader on tblB, I get the error "There is already an >> >>>> > open >> >>>> > DataReader associated with this Connection which must be closed >> >>>> > first." >> >>>> > How can I fix this error ? >> >>>> > For each DataReader, do I want to open and close the connection >> >>>> > (in >> >>>> > this >> >>>> > case adoCon) to avoid this error ? >> >>>> > Thank you. >> >>>> > >> >>>> > m_cmdSQL = New SqlClient.SqlCommand >> >>>> > With m_cmdSQL >> >>>> > .Connection = adoCon >> >>>> > .CommandText = "SELECT * FROM tblA" >> >>>> > End With >> >>>> > m_drSQL = m_cmdSQL.ExecuteReader() >> >>>> > Do While m_drSQL.Read >> >>>> > sSQL = "insert into tblB (Account,name,Company)" >> >>>> > sSQL = sSQL & (" VALUES ('" & m_drSQL.Item("Account") & >> >>>> > "'") >> >>>> > sSQL = sSQL & (" ,'" & m_drSQL.Item("Name") & "'") >> >>>> > sSQL = sSQL & (" ,'" & m_drSQL.Item("company") & "')") >> >>>> > m_cmdSQL2 = New SqlClient.SqlCommand >> >>>> > With m_cmdSQL2 >> >>>> > .Connection = adoCon >> >>>> > .CommandText = sSQL >> >>>> > End With >> >>>> > m_drSQL2 = m_cmdSQL2.ExecuteReader() ---> ERROR: There >> >>>> > is >> >>>> > already an open DataReader associated with this Connection which >> >>>> > must >> >>>> > be >> >>>> > closed first. >> >>>> > rs.CloseRS() >> >>>> > rs = Nothing >> >>>> > Loop >> >>>> > >> >>>> > >> >>>> > >> >>>> > >> >>>> >> >>>> >> >>>> >> >> >> >> >> > >> > >> >> >> 
Other interesting topics
Q: DataColumn Expressions
What is WebBrowser control called now? Manipulating controls created by another thread Estimates on money lost because of VB.NET The below snippet does not work...anyone know how to reference a procedure? Form1.closing in VB2005??? DateTime Getting an Object Properties value... Q: DataView with Table with large number of rows Services Grants |
|||||||||||||||||||||||
