Data reader already open?

  • Thread starter Thread starter Brett
  • Start date Start date
B

Brett

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

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?
 
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

W.G. Ryan eMVP said:
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 said:
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

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
Click to expand...
Click to expand...
 
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 said:
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 said:
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

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
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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

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
Click to expand...
Click to expand...
Click to expand...
 
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


W.G. Ryan eMVP said:
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 said:
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 said:
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
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

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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top