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!
