Close connection if there is an error 'connection not closed'?

Question

0.00/5 (No votes)

See more:

My vb.net application shows an error.

"Connection not closed. Connection's current state is open"
Here is my code. Please help me rectify the error:

VB

Private Sub saveBtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles          saveBtn.Click
        addSubject()
       End Sub

    Private Function addSubject() As Boolean
      Dim cmd As New SqlCommand
      Dim ok As Boolean = False
      cmd.Connection = con
      cmd.CommandText = " INSERT INTO Subjects(subject_name) VALUES(@inn)"
      cmd.CommandType = CommandType.Text
      cmd.Parameters.AddWithValue("inn", subjectTxt.Text)

      Try
        con.Open()
        cmd.ExecuteNonQuery()
        ok = True

      Catch ex As Exception
          MessageBox.Show(ex.Message, "Add failed")
         ok = False
       End Try
      cmd.Dispose()
      con.Close()
      Return ok

      con.Close()
    End Function

Posted 11-Mar-14 18:52pm

hlsc1983

Add a Solution

2 solutions

Solution 2

declare connection as Public ,then First close connection if anything is open Then Open it

Posted 11-Mar-14 19:09pm

Kan07

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Aravindba · Accepted Answer · 2014-03-11T19:00:00

Solution 1

Hi u r try to open connection,but that connection is already opened,it not in close state,so remove con.open and try

Remove any one con.Close() form catch block.

Where u declare con ? did u declare con as public variable ?

Posted 11-Mar-14 19:00pm

Aravindba

Updated 11-Mar-14 19:04pm

v4

Comments

hlsc1983 12-Mar-14 1:12am

i removed con and now its showing another error.
" there's already an open datareader associated with this connection which must be close first!"

con is declared in a module

Aravindba 12-Mar-14 1:21am

ok u check each function ,in any one function con is not closed,then u try to open con in above mention function ,so fully u check ur code and close con in every function ,use try...catch...finally, in finally block u need to dispose first any datareader or datable or dataset or cmd and then try to close con.

Try
con.Open()
cmd.ExecuteNonQuery()
ok = True
con.Close()// Add here close con
Catch ex As Exception
MessageBox.Show(ex.Message, "Add failed")
ok = False
End Try
cmd.Dispose()
con.Close()
Return ok

con.Close()//Remove this
End Function

Try to use finally in try catch,so only if success or any error it goes to finally block and u can dispose and close