Too many connection error

[ramnujs]

I am developing an vb.net application right now and i am using MySQL as my back end database. Since i started coding i open then close connection and dispose them everytime i access or update or add record in the database.
A too many connection error occurs...... how can i fix this error.
anybody who can help me please? ASAP

thanks!

 

[kulrom]

post the code ! otherwise i don't see a way how we can help you

Regards

 

[ramnujs]

here is the sample code

objects are group into three layers...problem occurs in dataaccess layer .
here is a sample code in RoomsDAO class . all of my Data Access Layer Classes are in these format...

By the way there are classes which are/is use to help parameter adding in command objects..

Imports Jinisys.Hotel.BusinessSharedClasses
Imports MySql.Data.MySqlClient
Namespace DataAccessLayer
Public Class RoomDAO
Public Sub New()
End Sub
Private connectionStr As String
Public Sub New(ByVal connectionString As String)
connectionStr = connectionString
End Sub
Private oDataSetConverter As New DataReaderToDatasetConverter
Private oRoom As New Jinisys.Hotel.Configuration.BusinessLayer.Room
Public Function GetRooms() As Jinisys.Hotel.Configuration.BusinessLayer.Room
Dim Connection As New MySqlConnection(connectionStr)
Connection.Open()
oRoom = New Jinisys.Hotel.Configuration.BusinessLayer.Room
Try
Dim selectCommand As New MySqlCommand("spSelectRooms", Connection)
selectCommand.CommandType = CommandType.StoredProcedure
Dim dataReader As MySqlDataReader = selectCommand.ExecuteReader
oDataSetConverter.convertDataReaderToDataSet(dataReader, "Rooms", oRoom)
Dim primaryKey(0) As DataColumn
primaryKey(0) = oRoom.Tables("Rooms").Columns("RoomId")
oRoom.Tables("Rooms").PrimaryKey = primaryKey
If Not IsNothing(oRoom) Then
Return oRoom
Else
Return Nothing
End If
Catch ex As Exception
MsgBox("roomtype error: " & ex.Message())
Throw ex
Finally
If Connection.State = ConnectionState.Open Then
Connection.Close()
Connection.Dispose()
End If
If Not IsNothing(oRoom) Then _
oRoom.Dispose()
End Try
End Function
Public Function GetRoomIDs() As ArrayList
Dim Connection As New MySqlConnection(connectionStr)
Connection.Open()
oRoom = New Jinisys.Hotel.Configuration.BusinessLayer.Room
Dim RoomIDs As New ArrayList
Try
Dim selectCommand As New MySqlCommand("spSelectRoomID", Connection)
selectCommand.CommandType = CommandType.StoredProcedure
Dim dataReader As MySqlDataReader = selectCommand.ExecuteReader
Do While (dataReader.Read())
RoomIDs.Add(dataReader.GetValue(0))
Loop
dataReader.Close()
If Not IsNothing(RoomIDs) Then
Return RoomIDs
Else
Return Nothing
End If
Catch ex As Exception
MsgBox("roomtype error: " & ex.Message())
Throw ex
Finally
If Connection.State = ConnectionState.Open Then
Connection.Close()
Connection.Dispose()
End If
If Not IsNothing(oRoom) Then _
oRoom.Dispose()
End Try
End Function
Public Function GetRoomIDs(ByVal RoomTypeCode As String) As ArrayList
Dim Connection As New MySqlConnection(connectionStr)
Connection.Open()
oRoom = New Jinisys.Hotel.Configuration.BusinessLayer.Room
Dim RoomIDs As New ArrayList
Try
Dim selectCommand As New MySqlCommand("spSelectRoomIDByRoomType", Connection)
selectCommand.CommandType = CommandType.StoredProcedure
Dim param As New MySqlParameter
param.ParameterName = "pRoomTypeCode"
param.Direction = ParameterDirection.Input
param.DbType = DbType.String
param.SourceColumn = "RoomTypeCode"
param.Value = RoomTypeCode
selectCommand.Parameters.Add(param)
Dim dataReader As MySqlDataReader = selectCommand.ExecuteReader
Do While (dataReader.Read())
RoomIDs.Add(dataReader.GetValue(0))
Loop
dataReader.Close()
If Not IsNothing(RoomIDs) Then
Return RoomIDs
Else
Return Nothing
End If
Catch ex As Exception
MsgBox("roomtype error: " & ex.Message())
Throw ex
Finally
If Connection.State = ConnectionState.Open Then
Connection.Close()
Connection.Dispose()
End If
If Not IsNothing(oRoom) Then _
oRoom.Dispose()
End Try
End Function
Public Sub InsertRoom(ByRef Room As Jinisys.Hotel.Configuration.BusinessLayer.Room)
Dim Connection As New MySqlConnection(connectionStr)
'Try
Connection.Open()
Dim sqlStatement As String = "spInsertRoom"
Dim InsertCommand As New MySqlCommand(sqlStatement, Connection)
InsertCommand.CommandType = CommandType.StoredProcedure
Dim exemptedFields As New ArrayList
exemptedFields.Add("maxoccupants")
exemptedFields.Add("noofbeds")
exemptedFields.Add("noofadult")
exemptedFields.Add("noofchild")
exemptedFields.Add("sharetype")
exemptedFields.Add("stateflag")
ParameterHelper.AddParameters(Room, exemptedFields, InsertCommand, "Rooms")
MsgBox(InsertCommand.ExecuteNonQuery() & " record(s) inserted!")
Dim D As DataRow = Room.Tables("Rooms").NewRow()
D("HotelID") = Room.HotelID
D("RoomId") = Room.RoomId
D("RoomTypecode") = Room.RoomTypecode
D("MaxOccupants") = Room.MaxOccupants
D("NoOfBeds") = Room.NoOfBeds
D("NoOfAdult") = Room.NoOfAdult
D("Floor") = Room.Floor
D("DirFacing") = Room.DirFacing
D("AdjLeft") = Room.AdjLeft
D("AdjRight") = Room.AdjRight
D("RoomImage") = Room.RoomImage
Room.Tables("Rooms").Rows.Add(D)
Room.Tables("Rooms").AcceptChanges()
If Connection.State = ConnectionState.Open Then
Connection.Close()
Connection.Dispose()
End If
End Sub
Public Sub DeleteRoom(ByVal RoomId As Integer, ByVal Room As Jinisys.Hotel.Configuration.BusinessLayer.Room)
Dim Connection As MySqlConnection
Try
Connection = New MySqlConnection(connectionStr)
Connection.Open()
Dim DeleteCommand As New MySqlCommand("spDeleteRoom", Connection)
DeleteCommand.CommandType = CommandType.StoredProcedure
Dim param As New MySqlParameter
param.ParameterName = "proomid"
param.Direction = ParameterDirection.Input
param.DbType = DbType.Int64
param.SourceColumn = "roomid"
param.Value = RoomId
DeleteCommand.Parameters.Add(param)
DeleteCommand.ExecuteNonQuery()
Dim primaryKeyVal As New Object
primaryKeyVal = RoomId
Dim row As DataRow = Room.Tables("Rooms").Rows.Find(primaryKeyVal)
Room.Tables("Rooms").Rows.Remove(row)
Room.AcceptChanges()
Catch ex As Exception
MsgBox("err:" & ex.Message())
Finally
If Connection.State = ConnectionState.Open Then
Connection.Close()
Connection.Dispose()
End If
End Try
End Sub
Public Sub UpdateRoom(ByRef Room As Jinisys.Hotel.Configuration.BusinessLayer.Room, ByVal primaryKeyVal As Object)
Dim Connection As New MySqlConnection(connectionStr)
'Try
Connection.Open()
Dim sqlStatement As String = "spUpdateRoom"
Dim updateCommand As New MySqlCommand(sqlStatement, Connection)
updateCommand.CommandType = CommandType.StoredProcedure
Dim exemptedFields As New ArrayList
exemptedFields.Add("stateflag")
ParameterHelper.AddParameters(Room, exemptedFields, updateCommand, "Rooms")
MsgBox(updateCommand.ExecuteNonQuery() & " row(s) affected!")
Dim D As DataRow = Room.Tables("Rooms").Rows.Find(primaryKeyVal)
D.BeginEdit()
D("HotelId") = Room.HotelID
D("RoomTypeCode") = Room.RoomTypecode
D("MaxOccupants") = Room.MaxOccupants
D("NoOfBeds") = Room.NoOfBeds
D("NoOfAdult") = Room.NoOfAdult
D("NoOfChild") = Room.NoOfChild
D("Floor") = Room.Floor
D("DirFacing") = Room.DirFacing
D("AdjLeft") = Room.AdjLeft
D("AdjRight") = Room.AdjRight
D("RoomImage") = Room.RoomImage
D.EndEdit()
D.AcceptChanges()
Room.Tables("Rooms").AcceptChanges()
If Connection.State = ConnectionState.Open Then
Connection.Close()
Connection.Dispose()
End If
End Sub
Private ParameterHelper As New ParameterHelper
Public Sub UpdateRoomCoordinates(ByVal roomId As String, ByVal xCoordinate As Integer, ByVal yCoordinate As Integer)
Dim CONNECTION As New MySqlConnection(connectionStr)
CONNECTION.Open()
Dim updateCommand As New MySqlCommand("spUpdateRoomCoordinates", CONNECTION)
updateCommand.CommandType = CommandType.StoredProcedure
Dim pRoomId As New MySqlParameter
Dim pXCoordinate As New MySqlParameter
Dim pYCoordinate As New MySqlParameter
ParameterHelper.AddParameters(pRoomId, "pRoomId", ParameterDirection.Input, DbType.String, roomId, updateCommand)
ParameterHelper.AddParameters(pXCoordinate, "pXCoordinate", ParameterDirection.Input, DbType.Int32, xCoordinate, updateCommand)
ParameterHelper.AddParameters(pYCoordinate, "pYCoordinate", ParameterDirection.Input, DbType.Int32, yCoordinate, updateCommand)
MsgBox(updateCommand.ExecuteNonQuery() & " record/s affected")
If CONNECTION.State = ConnectionState.Open Then
CONNECTION.Close()
CONNECTION.Dispose()
End If
End Sub
Public Sub DeleteRoomAmenity(ByRef RoomId As String, ByRef AmenityId As Integer)
Dim Connection As MySqlConnection
Try
Connection = New MySqlConnection(connectionStr)
Connection.Open()
Dim DeleteCommand As New MySqlCommand("spDeleteRoomAmenity", Connection)
DeleteCommand.CommandType = CommandType.StoredProcedure
Dim paramRoomId As New MySqlParameter
paramRoomId.ParameterName = "proomid"
paramRoomId.Direction = ParameterDirection.Input
paramRoomId.DbType = DbType.Int64
paramRoomId.SourceColumn = "roomid"
paramRoomId.Value = RoomId
DeleteCommand.Parameters.Add(paramRoomId)
Dim paramAmenityId As New MySqlParameter
paramAmenityId.ParameterName = "pamenityid"
paramAmenityId.Direction = ParameterDirection.Input
paramAmenityId.DbType = DbType.Int64
paramAmenityId.SourceColumn = "amenityid"
paramAmenityId.Value = AmenityId
DeleteCommand.Parameters.Add(paramAmenityId)
DeleteCommand.ExecuteNonQuery()
Catch ex As Exception
MsgBox("err:" & ex.Message())
Finally
If Connection.State = ConnectionState.Open Then
Connection.Close()
Connection.Dispose()
End If
End Try
End Sub
Public Sub AddRoomAmenity(ByRef RoomId As Integer, ByRef AmenityId As Integer)
Dim Connection As MySqlConnection
Try
Connection = New MySqlConnection(connectionStr)
Connection.Open()
Dim InsertCommand As New MySqlCommand("spAddRoomAmenity", Connection)
InsertCommand.CommandType = CommandType.StoredProcedure
Dim paramRoomId As New MySqlParameter
paramRoomId.ParameterName = "proomid"
paramRoomId.Direction = ParameterDirection.Input
paramRoomId.DbType = DbType.Int64
paramRoomId.SourceColumn = "roomid"
paramRoomId.Value = RoomId
InsertCommand.Parameters.Add(paramRoomId)
Dim paramAmenityId As New MySqlParameter
paramAmenityId.ParameterName = "pamenityid"
paramAmenityId.Direction = ParameterDirection.Input
paramAmenityId.DbType = DbType.Int64
paramAmenityId.SourceColumn = "amenityid"
paramAmenityId.Value = AmenityId
InsertCommand.Parameters.Add(paramAmenityId)
InsertCommand.ExecuteNonQuery()
 
Catch ex As Exception
MsgBox("err:" & ex.Message())
Finally
If Connection.State = ConnectionState.Open Then
Connection.Close()
Connection.Dispose()
End If
End Try
End Sub
Public Sub SetRoomStatus(ByRef roomID As Integer, ByVal stateFlag As String)
Dim CONNECTION As New MySqlConnection(connectionStr)
CONNECTION.Open()
Dim setStatusCommand As New MySqlCommand("spSetRoomStateFlag", CONNECTION)
setStatusCommand.CommandType = CommandType.StoredProcedure
Dim paramRoomID As New MySqlParameter, paramStateFlag As New MySqlParameter
ParameterHelper.AddParameters(paramRoomID, "pRoomID", ParameterDirection.Input, DbType.Int64, roomID, setStatusCommand)
ParameterHelper.AddParameters(paramStateFlag, "pStateFlag", ParameterDirection.Input, DbType.String, stateFlag, setStatusCommand)
setStatusCommand.ExecuteNonQuery()
If CONNECTION.State = ConnectionState.Open Then
CONNECTION.Close()
CONNECTION.Dispose()
End If
End Sub
End Class
End Namespace

 

[kulrom]

Look this is too much code for reading but i'll give you some tips which may be of help.
1st is that you should declare the public connection inside the module or settings.
2nd is that it's good practice/manner if you use try catch block(s) when you deal with connections as Finally portion where i usually put the conn's close statement will be executed no matter what. It means all opened connections will be closed even if there happens an exception
i.e.

Try
   conn.Open()
   'do something
Catch ex as Exception
   MessageBox.Show(ex.Message)
Finally
   conn.Close()
End Try

HTH
Regards

 

[ramnujs]

i have fixed the problem!

i have used only one intance connection object all through out the application and the problem is gone!

 

[Mohan]

same pbm here...how to use single connection class for entire application please help

 

[ramnujs]

single persistent connection!

1. On your application , declare a class that holds public shared connection so that it can be access without creating an instance. (create this as a separate project with type class library)
2.Referance the DLL to all projects in your acpplication that requires the connection.
3.You should decide on which part of the application you will open up your connection.If have a security module in our application, you may open an connection during the user logs on with the system. If user is authenticated, assign that connection object to the one that you declared in the class containing the shared connection object.
4.You may used that connection to any module that requires access to database. To make it more dynamic, set a connection object as an argument to a method or you may have it as one of the constructor argument in creating instances of an object.

 

[Mohan]

thaks for ur reply,

i'm new to vb.net, i just finished learning n started to do application with sql server as backend.

so if possible please send me code..so i can follow up and begin my application

it will be great help fromur side

thanks

 

[ramnujs]

single persistent connection!

Try the sample code in the attachment!

 

[Mohan]

thanks ramnuj...

i'm seeing ur sample project,

really i feel like i have to learn lot to do project, i want to know more in detail.

i want to know y we v have to wtite imports system.data.sqlclient in all form, can't we put everything in a module so that it is available to all the forms.

u have used class library to declare a sqlconnection and u r importing in openconnectionform.

again in the same form ur using another connection called con in button click event...

then it means ur not using whatever u declared in singleconnectionused class..

really not able to understand.

my question is very simple, In VB6 i used to declare public connection and i used open it when my application starts and code was written in the same module in a procedure.

can't we write same thing here...

 

[ramnujs]

I guess u need to do some readings about how vb.net Object-Oriented Programming works. All forms in vb.net are treated as classes. classes contains member variables , properties and methods which u can expose in different levels. public, private,friend,protected. declaring a connection object that will be available to all forms is not possible unless your forms and connection objects resides in the same namespace.
The open connection project in my example is importing the sigleconnection project to get reference of the class singleconnectionclass and assigned an openned connection to it. Doing so, your singleconnectionclass.myconnection(not sure with the name i gave) has now a valid and open connection. if you have several projects in a solution, all of the classes or objects inside in any projects can make reference to that open connection. This example of mine is based on Modular Programming in which one Enterprise Wide Solution is Devide into several modules and they represents diffient projects in the solution.

One thing i would like to add:
read about 3-tier programming, i have attacted 3-tier programming model

Good Luck!

 

[Mohan]

thanks ramnuj, i read as u said...

i some how started to do programming with database, if i need any help i come back to u...

thanks

 

[ramnujs]

Good Luck!

Good luck you!


Ill be glad to help if u need one!


Cheers

ramnujs