The connection is already Open (state=Open, Fetching).

Hello again.

This problem relates to a Business Object that I've created. I get the above error when I try t call a Sub within the business object that inserts into the database. I have various other Subs and Functions that Select from the database and they don't seem to have a problem. I can't see where the connection is being left open... But I've been staring at this all day now so theres a good chance I'm missing something obvious...

The VB for the business object is below. The sub in the aspx page that calls it is:

Sub Button1_Click(Source As Object, E As EventArgs)
dim objForum as New BidwellsDevelopments.PublicForum
objForum.ReplyToForum(pubThreadID, pubUser,oEdit1.Content)
End Sub

And the business object code (the sub in question is at the bottom in bold):

' VB Document
Imports System
Imports System.Data
Imports System.Data.OleDb

Namespace BidwellsDevelopments

Public Class DevelopmentDetails
public DevelopmentID as Integer
public Name' as string
public Description' as string
public Keywords' as string
'public Live' as integer
public Logo' as string
public MainImage' as string
public HomePage' as string
public PostCode' as string
public Address1' as string
public Address2'' as string
public Address3' as string
public Town' as string
public County' as string
public Country' as string
'' public ClientID' as integer
' public ManagerID' as integer
' public CreatorID' as integer
End Class

Public Class Development
Private objConn as new OleDbConnection (System.Configuration.ConfigurationSettings.AppSettings("ConnectionString"))

public function GetDevelopment(developmentID) as DevelopmentDetails

Dim objCmd As New OleDbCommand("spGetDevelopment", objConn)
objCmd.CommandType = CommandType.StoredProcedure

Dim objParam As New OleDbParameter("@dotnet.itags.org.DevelopmentID", OleDbType.Char)
objParam.Value = developmentID
objCmd.Parameters.Add(objParam)
Dim objReader as OleDbDataReader

try
objConn.Open()
objReader = objCmd.ExecuteReader
catch ex as OleDbException
throw ex
end try
dim objDetails as new DevelopmentDetails

while objReader.Read()
objDetails.DevelopmentID = developmentID
objDetails.Name = objReader(1)
objDetails.Description = objReader(2)
objDetails.Keywords = objReader(3)
'objDetails.Live = objReader.GetInt32(4)
objDetails.Logo = objReader(5)
objDetails.MainImage = objReader(6)
objDetails.HomePage = objReader(7)
objDetails.PostCode = objReader(8)
objDetails.Address1 = objReader(9)
objDetails.Address2 = objReader(10)
objDetails.Address3 = objReader(11)
objDetails.Town = objReader(12)
objDetails.County = objReader(13)
objDetails.Country = objReader(14)
'objDetails.ClientID = objReader.GetString(15)
'objDetails.ManagerID = objReader.GetString(16)
'objDetails.CreatorID = objReader.GetString(17)
end while
objReader.Close

return objDetails
End function

End Class

Public Class PublicForum
Private objConn as new OleDbConnection (System.Configuration.ConfigurationSettings.AppSettings("ConnectionString"))

Public function DisplayForum(developmentID) As OleDbDataReader

Dim objCmd As New OleDbCommand("spDisplayPFTopics", objConn)
objCmd.CommandType = CommandType.StoredProcedure

Dim objParam As New OleDbParameter("@dotnet.itags.org.DevelopmentID", OleDbType.Char)
objParam.Value = developmentID
objCmd.Parameters.Add(objParam)
Dim objReader as OleDbDataReader

try
'objCmd.Connection.Open()
objConn.Open()
return objCmd.ExecuteReader
catch ex as OleDbException
throw ex
end try
End Function

Public function DisplayThreads(topicID) As OleDbDataReader
Dim objCmd As New OleDbCommand("spDisplayPFThreads", objConn)
objCmd.CommandType = CommandType.StoredProcedure

Dim objParam As New OleDbParameter("@dotnet.itags.org.TopicID", OleDbType.Char)
objParam.Value = topicID
objCmd.Parameters.Add(objParam)
Dim objReader as OleDbDataReader

try
objConn.Open()
return objCmd.ExecuteReader
catch ex as OleDbException
throw ex
end try
End Function

Public function DisplayMessages(threadID) As OleDbDataReader
Dim objCmd As New OleDbCommand("spDisplayPFEntries", objConn)
objCmd.CommandType = CommandType.StoredProcedure

Dim objParam As New OleDbParameter("@dotnet.itags.org.ThreadID", OleDbType.Char)
objParam.Value = threadID
objCmd.Parameters.Add(objParam)
Dim objReader as OleDbDataReader

try
objConn.Open()
return objCmd.ExecuteReader
catch ex as OleDbException
throw ex
end try
End Function

Sub ReplyToForum (threadID, creatorID, entry)
Dim objCmd As New OleDbCommand("spAddPFEntry", objConn)
objCmd.CommandType = CommandType.StoredProcedure

Dim objParam As New OleDbParameter("@dotnet.itags.org.ThreadID", OleDbType.Char)
objParam.Value = threadID
objCmd.Parameters.Add(objParam)

objParam = New OleDbParameter("@dotnet.itags.org.CreatorID", OleDbType.Char)
objParam.Value = creatorID
objCmd.Parameters.Add(objParam)

objParam = New OleDbParameter("@dotnet.itags.org.Entry", OleDbType.Char)
objParam.Value = entry
objCmd.Parameters.Add(objParam)

try
objConn.Open()
objCmd.ExecuteNonQuery
objConn.Close()
catch ex as OleDbException
throw ex
end try
End Sub


End Class
End Namespace

Any hints greatly received!

Thank you for your time

Shaun

Wow.. I don't think the problem lies within the code that you have bolded but within all the functions that return DataReader objects. You're going to have to pass them as:
return objDataReader(CommandBehavior.CloseConnection)
and then in your main code that is actually calling the function and expecting a reader in return you must do:
dim dr as SqlDataReader
dr = Class.MethodReturnsDatareader
dr.close()
Also, you may want to add Finally blocks to all those Try blocks and explicitly close the connection there. Because if any of those methods error out, the connection is still open...

Thanks Optik.

I will try what you suggest. I'm a bit confused though, as I've simply adapted examples out of my Teach Yourself ASP.Net in 21 Days Book. Would you be able to shed a little light on why I need to use objDataReader(CommandBehavior.CloseConnection)? What was it that I was doing wrong?
Many thanks
Shaun

Hi Optik

I would just like to say that this did infact solve the problem. So this issue is now resolved.

It would be good if I could just understand why!?!

Many thanks for your time

Shaun

Quoted from MSDN
"While theSqlDataReader is in use, the associatedSqlConnection is busy serving theSqlDataReader. While in this state, no other operations can be performed on theSqlConnection other than closing it. This is the case until you call theSqlDataReader.Close method. If theSqlDataReader is created withCommandBehavior set toCloseConnection, closing theSqlDataReader closes the connection automatically."

Cheers me dears!