|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Data reader already open?[code] Me.SqlCmd_BlacklistUpdateStatus.CommandText = "dbo.[SpamUpdateReport_blacklist]" Me.SqlCmd_BlacklistUpdateStatus.CommandType = System.Data.CommandType.StoredProcedure Me.SqlCmd_BlacklistUpdateStatus.Connection = Me.cn2 Me.SqlCmd_BlacklistUpdateStatus.Parameters.Add(New System.Data.SqlClient.SqlParameter("@RETURN_VALUE", System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.ReturnValue, False, CType(0, Byte), CType(0, Byte), "", System.Data.DataRowVersion.Current, Nothing)) Me.SqlCmd_BlacklistUpdateStatus.Parameters.Add(New System.Data.SqlClient.SqlParameter("@messageid", System.Data.SqlDbType.Int, 4)) Me.SqlCmd_BlacklistUpdateStatus.Parameters.Add(New System.Data.SqlClient.SqlParameter("@subpart", System.Data.SqlDbType.SmallInt, 2)) ' cn2.Open() 'loop through returned records Dim drspfblacklist As SqlDataReader drspfblacklist = SqlCmd_SpamUpdate_spfBlackUnprocessed.ExecuteReader() While drspfblacklist.Read [/code] At the while statement, I get a data ready already open for this connection error. If I remove the line cn2.open(), I get an error about the connection being closed. This should say that no other resource is using the connection. What else could be wrong? Thanks, Brett If you just use Command.ExecuteReader() will that work? It looks like cn2
is already associated with SqlCmd_BlacklistUpdateStatus - but is there a connection associated with SqlCmd_SpamUpdate_spfBlackUnprocessed. Just to be sure, use a new SqlConnection (just for testing) using the same connection string as cn2- but use this connection for the command you're calling ExecuteReader against. See if this doesn't fix it. Also, is it blowing up on the Read() line or is it the ExecuteReader line? Show quoteHide quote "Brett" <no@spam.com> wrote in message System.Data.SqlDbType.Int,news:%23DYG73VBFHA.4004@tk2msftngp13.phx.gbl... > I'm using this code in VS .NET 2003 > > [code] > > Me.SqlCmd_BlacklistUpdateStatus.CommandText = > "dbo.[SpamUpdateReport_blacklist]" > Me.SqlCmd_BlacklistUpdateStatus.CommandType = > System.Data.CommandType.StoredProcedure > Me.SqlCmd_BlacklistUpdateStatus.Connection = Me.cn2 > Me.SqlCmd_BlacklistUpdateStatus.Parameters.Add(New > System.Data.SqlClient.SqlParameter("@RETURN_VALUE", > System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.ReturnValue, > False, CType(0, Byte), CType(0, Byte), "", > System.Data.DataRowVersion.Current, Nothing)) > Me.SqlCmd_BlacklistUpdateStatus.Parameters.Add(New > System.Data.SqlClient.SqlParameter("@messageid", Show quoteHide quote > 4)) > Me.SqlCmd_BlacklistUpdateStatus.Parameters.Add(New > System.Data.SqlClient.SqlParameter("@subpart", > System.Data.SqlDbType.SmallInt, 2)) > ' > > > cn2.Open() > > 'loop through returned records > Dim drspfblacklist As SqlDataReader > drspfblacklist = SqlCmd_SpamUpdate_spfBlackUnprocessed.ExecuteReader() > While drspfblacklist.Read > [/code] > > At the while statement, I get a data ready already open for this connection > error. If I remove the line cn2.open(), I get an error about the connection > being closed. This should say that no other resource is using the > connection. What else could be wrong? > > Thanks, > Brett > > SqlCmd_SpamUpdate_spfBlackUnprocessed is associated with cn2 by this line:
Me.SqlCmd_BlacklistUpdateStatus.Connection = Me.cn2 cn2 is only associated with Me.SqlCmd_BlacklistUpdateStatus.Connection. I did a search on "cn2" and that is its only association. I did create a new connection, "cn3", and associated it to Me.SqlCmd_BlacklistUpdateStatus.Connection. Same error. It occurs on this line: drspfblacklist = SqlCmd_SpamUpdate_spfBlackUnprocessed.ExecuteReader() It actually doesn't make it to the read. Thanks, Brett Show quoteHide quote "W.G. Ryan eMVP" <WilliamR***@gmail.com> wrote in message news:eOTxKpWBFHA.1188@tk2msftngp13.phx.gbl... > If you just use Command.ExecuteReader() will that work? It looks like cn2 > is already associated with SqlCmd_BlacklistUpdateStatus - but is there a > connection associated with SqlCmd_SpamUpdate_spfBlackUnprocessed. > > Just to be sure, use a new SqlConnection (just for testing) using the same > connection string as cn2- but use this connection for the command you're > calling ExecuteReader against. See if this doesn't fix it. > > Also, is it blowing up on the Read() line or is it the ExecuteReader line? > > -- > W.G. Ryan, MVP > > www.tibasolutions.com | www.devbuzz.com | www.knowdotnet.com > "Brett" <no@spam.com> wrote in message > news:%23DYG73VBFHA.4004@tk2msftngp13.phx.gbl... >> I'm using this code in VS .NET 2003 >> >> [code] >> >> Me.SqlCmd_BlacklistUpdateStatus.CommandText = >> "dbo.[SpamUpdateReport_blacklist]" >> Me.SqlCmd_BlacklistUpdateStatus.CommandType = >> System.Data.CommandType.StoredProcedure >> Me.SqlCmd_BlacklistUpdateStatus.Connection = Me.cn2 >> Me.SqlCmd_BlacklistUpdateStatus.Parameters.Add(New >> System.Data.SqlClient.SqlParameter("@RETURN_VALUE", >> System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.ReturnValue, >> False, CType(0, Byte), CType(0, Byte), "", >> System.Data.DataRowVersion.Current, Nothing)) >> Me.SqlCmd_BlacklistUpdateStatus.Parameters.Add(New >> System.Data.SqlClient.SqlParameter("@messageid", > System.Data.SqlDbType.Int, >> 4)) >> Me.SqlCmd_BlacklistUpdateStatus.Parameters.Add(New >> System.Data.SqlClient.SqlParameter("@subpart", >> System.Data.SqlDbType.SmallInt, 2)) >> ' >> >> >> cn2.Open() >> >> 'loop through returned records >> Dim drspfblacklist As SqlDataReader >> drspfblacklist = SqlCmd_SpamUpdate_spfBlackUnprocessed.ExecuteReader() >> While drspfblacklist.Read >> [/code] >> >> At the while statement, I get a data ready already open for this > connection >> error. If I remove the line cn2.open(), I get an error about the > connection >> being closed. This should say that no other resource is using the >> connection. What else could be wrong? >> >> Thanks, >> Brett >> >> > > I am also doing this just above my sub() but before the class declaration:
Friend WithEvents sqlCmd_spf_Update As System.Data.SqlClient.SqlCommand Friend WithEvents cnAs System.Data.SqlClient.SqlConnection Friend WithEvents cn2 As System.Data.SqlClient.SqlConnection Friend WithEvents cn3 As System.Data.SqlClient.SqlConnection Friend WithEvents SqlCmd_BlacklistUpdateStatus As System.Data.SqlClient.SqlCommand Friend WithEvents SqlCmd_SpamUpdate_spfBlackUnprocessed As System.Data.SqlClient.SqlCommand Friend WithEvents SqlCmd_spamBlacklist_Insert As System.Data.SqlClient.SqlCommand Will the above effect anything? Here's where this all started: I originally had everything in one form. It worked fine. I needed to free up the form so I created a class file and put all the database object code in the class file. I just cut and pasted the code created from dragging and dropping into the form. I wasn't sure how else to get database objects into the class file since these aren't available on the toolbar. From the form, I push a button, which starts a new thread for the class file. Thanks, Brett Show quoteHide quote "W.G. Ryan eMVP" <WilliamR***@gmail.com> wrote in message news:eOTxKpWBFHA.1188@tk2msftngp13.phx.gbl... > If you just use Command.ExecuteReader() will that work? It looks like cn2 > is already associated with SqlCmd_BlacklistUpdateStatus - but is there a > connection associated with SqlCmd_SpamUpdate_spfBlackUnprocessed. > > Just to be sure, use a new SqlConnection (just for testing) using the same > connection string as cn2- but use this connection for the command you're > calling ExecuteReader against. See if this doesn't fix it. > > Also, is it blowing up on the Read() line or is it the ExecuteReader line? > > -- > W.G. Ryan, MVP > > www.tibasolutions.com | www.devbuzz.com | www.knowdotnet.com > "Brett" <no@spam.com> wrote in message > news:%23DYG73VBFHA.4004@tk2msftngp13.phx.gbl... >> I'm using this code in VS .NET 2003 >> >> [code] >> >> Me.SqlCmd_BlacklistUpdateStatus.CommandText = >> "dbo.[SpamUpdateReport_blacklist]" >> Me.SqlCmd_BlacklistUpdateStatus.CommandType = >> System.Data.CommandType.StoredProcedure >> Me.SqlCmd_BlacklistUpdateStatus.Connection = Me.cn2 >> Me.SqlCmd_BlacklistUpdateStatus.Parameters.Add(New >> System.Data.SqlClient.SqlParameter("@RETURN_VALUE", >> System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.ReturnValue, >> False, CType(0, Byte), CType(0, Byte), "", >> System.Data.DataRowVersion.Current, Nothing)) >> Me.SqlCmd_BlacklistUpdateStatus.Parameters.Add(New >> System.Data.SqlClient.SqlParameter("@messageid", > System.Data.SqlDbType.Int, >> 4)) >> Me.SqlCmd_BlacklistUpdateStatus.Parameters.Add(New >> System.Data.SqlClient.SqlParameter("@subpart", >> System.Data.SqlDbType.SmallInt, 2)) >> ' >> >> >> cn2.Open() >> >> 'loop through returned records >> Dim drspfblacklist As SqlDataReader >> drspfblacklist = SqlCmd_SpamUpdate_spfBlackUnprocessed.ExecuteReader() >> While drspfblacklist.Read >> [/code] >> >> At the while statement, I get a data ready already open for this > connection >> error. If I remove the line cn2.open(), I get an error about the > connection >> being closed. This should say that no other resource is using the >> connection. What else could be wrong? >> >> Thanks, >> Brett >> >> > > Brett, I figured it was the Execute command causing the problem. I'm
thinking that your problem may be that you're trying to access an connection or command created in one thread in another thread. Is that a possibility? "Brett" <no@spam.com> wrote in message System.Data.SqlClient.SqlCommandnews:eorWSKXBFHA.3368@TK2MSFTNGP10.phx.gbl... > I am also doing this just above my sub() but before the class declaration: > > Friend WithEvents sqlCmd_spf_Update As Show quoteHide quote > Friend WithEvents cnAs System.Data.SqlClient.SqlConnection System.Data.ParameterDirection.ReturnValue,> Friend WithEvents cn2 As System.Data.SqlClient.SqlConnection > Friend WithEvents cn3 As System.Data.SqlClient.SqlConnection > Friend WithEvents SqlCmd_BlacklistUpdateStatus As > System.Data.SqlClient.SqlCommand > Friend WithEvents SqlCmd_SpamUpdate_spfBlackUnprocessed As > System.Data.SqlClient.SqlCommand > Friend WithEvents SqlCmd_spamBlacklist_Insert As > System.Data.SqlClient.SqlCommand > > Will the above effect anything? > > Here's where this all started: I originally had everything in one form. It > worked fine. I needed to free up the form so I created a class file and put > all the database object code in the class file. I just cut and pasted the > code created from dragging and dropping into the form. I wasn't sure how > else to get database objects into the class file since these aren't > available on the toolbar. From the form, I push a button, which starts a > new thread for the class file. > > Thanks, > Brett > > > "W.G. Ryan eMVP" <WilliamR***@gmail.com> wrote in message > news:eOTxKpWBFHA.1188@tk2msftngp13.phx.gbl... > > If you just use Command.ExecuteReader() will that work? It looks like cn2 > > is already associated with SqlCmd_BlacklistUpdateStatus - but is there a > > connection associated with SqlCmd_SpamUpdate_spfBlackUnprocessed. > > > > Just to be sure, use a new SqlConnection (just for testing) using the same > > connection string as cn2- but use this connection for the command you're > > calling ExecuteReader against. See if this doesn't fix it. > > > > Also, is it blowing up on the Read() line or is it the ExecuteReader line? > > > > -- > > W.G. Ryan, MVP > > > > www.tibasolutions.com | www.devbuzz.com | www.knowdotnet.com > > "Brett" <no@spam.com> wrote in message > > news:%23DYG73VBFHA.4004@tk2msftngp13.phx.gbl... > >> I'm using this code in VS .NET 2003 > >> > >> [code] > >> > >> Me.SqlCmd_BlacklistUpdateStatus.CommandText = > >> "dbo.[SpamUpdateReport_blacklist]" > >> Me.SqlCmd_BlacklistUpdateStatus.CommandType = > >> System.Data.CommandType.StoredProcedure > >> Me.SqlCmd_BlacklistUpdateStatus.Connection = Me.cn2 > >> Me.SqlCmd_BlacklistUpdateStatus.Parameters.Add(New > >> System.Data.SqlClient.SqlParameter("@RETURN_VALUE", > >> System.Data.SqlDbType.Int, 4, Show quoteHide quote > >> False, CType(0, Byte), CType(0, Byte), "", > >> System.Data.DataRowVersion.Current, Nothing)) > >> Me.SqlCmd_BlacklistUpdateStatus.Parameters.Add(New > >> System.Data.SqlClient.SqlParameter("@messageid", > > System.Data.SqlDbType.Int, > >> 4)) > >> Me.SqlCmd_BlacklistUpdateStatus.Parameters.Add(New > >> System.Data.SqlClient.SqlParameter("@subpart", > >> System.Data.SqlDbType.SmallInt, 2)) > >> ' > >> > >> > >> cn2.Open() > >> > >> 'loop through returned records > >> Dim drspfblacklist As SqlDataReader > >> drspfblacklist = SqlCmd_SpamUpdate_spfBlackUnprocessed.ExecuteReader() > >> While drspfblacklist.Read > >> [/code] > >> > >> At the while statement, I get a data ready already open for this > > connection > >> error. If I remove the line cn2.open(), I get an error about the > > connection > >> being closed. This should say that no other resource is using the > >> connection. What else could be wrong? > >> > >> Thanks, > >> Brett > >> > >> > > > > > > I created a new app from scratch. Started with a Form1 and Class1. Then
started copying and pasting from the old app into the Class1. Form1 only has Start and End buttons. As I progressed through the copying/pasting, everything worked fine. I reached a point where everything above the While drspfblacklist.Read statement was the same in both apps. The data reader error never appeared. I figured this was good enough. The app works fine now to that point. Now the question is, what exactly is wrong in the old app since the two apps are more or less copies of each other. Finding out what is wrong in the old app will very useful. It's not feasible to recontruct an app just to find a bug. There must be a better way. Any suggestions on how I can start? Perhaps just breaking down the old app? Thanks, Brett Show quoteHide quote "W.G. Ryan eMVP" <WilliamR***@gmail.com> wrote in message news:%23wEDtqYBFHA.3524@TK2MSFTNGP15.phx.gbl... > Brett, I figured it was the Execute command causing the problem. I'm > thinking that your problem may be that you're trying to access an > connection > or command created in one thread in another thread. Is that a > possibility? > > -- > W.G. Ryan, MVP > > www.tibasolutions.com | www.devbuzz.com | www.knowdotnet.com > "Brett" <no@spam.com> wrote in message > news:eorWSKXBFHA.3368@TK2MSFTNGP10.phx.gbl... >> I am also doing this just above my sub() but before the class >> declaration: >> >> Friend WithEvents sqlCmd_spf_Update As > System.Data.SqlClient.SqlCommand >> Friend WithEvents cnAs System.Data.SqlClient.SqlConnection >> Friend WithEvents cn2 As System.Data.SqlClient.SqlConnection >> Friend WithEvents cn3 As System.Data.SqlClient.SqlConnection >> Friend WithEvents SqlCmd_BlacklistUpdateStatus As >> System.Data.SqlClient.SqlCommand >> Friend WithEvents SqlCmd_SpamUpdate_spfBlackUnprocessed As >> System.Data.SqlClient.SqlCommand >> Friend WithEvents SqlCmd_spamBlacklist_Insert As >> System.Data.SqlClient.SqlCommand >> >> Will the above effect anything? >> >> Here's where this all started: I originally had everything in one form. > It >> worked fine. I needed to free up the form so I created a class file and > put >> all the database object code in the class file. I just cut and pasted >> the >> code created from dragging and dropping into the form. I wasn't sure how >> else to get database objects into the class file since these aren't >> available on the toolbar. From the form, I push a button, which starts a >> new thread for the class file. >> >> Thanks, >> Brett >> >> >> "W.G. Ryan eMVP" <WilliamR***@gmail.com> wrote in message >> news:eOTxKpWBFHA.1188@tk2msftngp13.phx.gbl... >> > If you just use Command.ExecuteReader() will that work? It looks like > cn2 >> > is already associated with SqlCmd_BlacklistUpdateStatus - but is there > a >> > connection associated with SqlCmd_SpamUpdate_spfBlackUnprocessed. >> > >> > Just to be sure, use a new SqlConnection (just for testing) using the > same >> > connection string as cn2- but use this connection for the command >> > you're >> > calling ExecuteReader against. See if this doesn't fix it. >> > >> > Also, is it blowing up on the Read() line or is it the ExecuteReader > line? >> > >> > -- >> > W.G. Ryan, MVP >> > >> > www.tibasolutions.com | www.devbuzz.com | www.knowdotnet.com >> > "Brett" <no@spam.com> wrote in message >> > news:%23DYG73VBFHA.4004@tk2msftngp13.phx.gbl... >> >> I'm using this code in VS .NET 2003 >> >> >> >> [code] >> >> >> >> Me.SqlCmd_BlacklistUpdateStatus.CommandText = >> >> "dbo.[SpamUpdateReport_blacklist]" >> >> Me.SqlCmd_BlacklistUpdateStatus.CommandType = >> >> System.Data.CommandType.StoredProcedure >> >> Me.SqlCmd_BlacklistUpdateStatus.Connection = Me.cn2 >> >> Me.SqlCmd_BlacklistUpdateStatus.Parameters.Add(New >> >> System.Data.SqlClient.SqlParameter("@RETURN_VALUE", >> >> System.Data.SqlDbType.Int, 4, > System.Data.ParameterDirection.ReturnValue, >> >> False, CType(0, Byte), CType(0, Byte), "", >> >> System.Data.DataRowVersion.Current, Nothing)) >> >> Me.SqlCmd_BlacklistUpdateStatus.Parameters.Add(New >> >> System.Data.SqlClient.SqlParameter("@messageid", >> > System.Data.SqlDbType.Int, >> >> 4)) >> >> Me.SqlCmd_BlacklistUpdateStatus.Parameters.Add(New >> >> System.Data.SqlClient.SqlParameter("@subpart", >> >> System.Data.SqlDbType.SmallInt, 2)) >> >> ' >> >> >> >> >> >> cn2.Open() >> >> >> >> 'loop through returned records >> >> Dim drspfblacklist As SqlDataReader >> >> drspfblacklist = SqlCmd_SpamUpdate_spfBlackUnprocessed.ExecuteReader() >> >> While drspfblacklist.Read >> >> [/code] >> >> >> >> At the while statement, I get a data ready already open for this >> > connection >> >> error. If I remove the line cn2.open(), I get an error about the >> > connection >> >> being closed. This should say that no other resource is using the >> >> connection. What else could be wrong? >> >> >> >> Thanks, >> >> Brett >> >> >> >> >> > >> > >> >> > >  
Other interesting topics
Question about SQLDataReader
Concurrency in N tiers How do I filter a child table through its parent? help to nav through DataReader infoMessage Truncated # of pooled connections seem too high OleDbParameter doesn't update value correctly What is the Best way to fetch the most recent updated row in a database preventing new rows in datagrids when using relations Dataset created in Code cannot be used in Crystal Reports designer |
|||||||||||||||||||||||
