mysql maximum connection error

Here is my sample connection code and error why it is not closing connection and i always clean them manually? This is workin on webservice
Dim cn As New MySqlConnection
        cn.ConnectionString = ConfigurationSettings.AppSettings("ConnectionString")
        Dim dr As MySqlDataReader
        Dim cmd As New MySqlCommand(sql.resimler, cn)
        cmd.Parameters.Add("?bolum", bolum).MySqlDbType = MySqlDbType.VarChar
        cmd.Parameters.Add("?bolumno", bolumno).MySqlDbType = MySqlDbType.VarChar
        cn.Open()
        dr = cmd.ExecuteReader
        Dim resim = ""
        If dr.Read = False Then
            Return "Yok,"
        Else
            resim += dr.Item(2) & ","
            While dr.Read()
                resim += dr.Item(2) & ","
            End While
        End If
        Return Left(resim, Len(resim) - 1)
        dr.Close()
        cn.Close()

Open in new window

hatamysql.jpg
LVL 1
CahitEyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kevin CrossChief Technology OfficerCommented:
You are returning before the close of the dr and cn objects.  Set your return value to a variable and return it at the very end instead.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Kevin CrossChief Technology OfficerCommented:
More like this:
Dim returnVal As String = "Yok"
 
Using cn As New MySqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
	Using dr As MySqlDataReader
		Dim cmd As New MySqlCommand(sql.resimler, cn)
		cmd.Parameters.Add("?bolum", bolum).MySqlDbType = MySqlDbType.VarChar
		cmd.Parameters.Add("?bolumno", bolumno).MySqlDbType = MySqlDbType.VarChar
		
		cn.Open()
		dr = cmd.ExecuteReader
		
		If dr.Read Then
			returnVal = dr.Item(2)
			
			While dr.Read()
				returnVal &= "," & dr.Item(2)
			End While
		End If
		
		dr.Close()
	End Using
	
	cn.Close()
End Using
 
Return returnVal

Open in new window

0
Kevin CrossChief Technology OfficerCommented:
The using statements should take care of both close() and dispose() of the objects also...
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

CahitEyAuthor Commented:
Thanks for both answers it is ok now , the both answer is the solution
0
CahitEyAuthor Commented:
i made point 500 to give 250 for each answer thanks again
0
CahitEyAuthor Commented:
Thanks
0
Kevin CrossChief Technology OfficerCommented:
CahitEy,

You are most welcome.

Thank you very much for the points.

Happy coding!

Regards,
Kevin
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.