"the connection was not closed. the connection's current state is open."

[Peric]

Thing is...

I'm trying to insert a new records into database, one of those records is a "datetime"...when I type a wrong date, and try to insert those values into database, i get an error (about wrong typed date etc.)...ok, that's fine, things need to work that way.
But here comes the problem, I correct the date, and then try to insert values again...here I get an error from the post title. And this doesn't work until I close that form and run it again...here's part of my code:

When I call my connection from that form I was talking about...

Dim cvLicence As New SQL

Try
                cvLicence.upis("INSERT INTO blabla (itsnotimportant) VALUES ('something')")
                MsgBox("everything's fine", MsgBoxStyle.Information)
            Catch ex As Exception
                MsgBox("error!!!", MsgBoxStyle.Critical)
                MsgBox(ex.Message)
            End Try

and this is class/procedure i'm calling for inserting those records

Public Class SQL

 Public Sub upis(ByVal upit As String)
        myConnection.Open()
        Dim sq As New SqlCommand(upit, myConnection)
        sq.ExecuteNonQuery()
        myConnection.Close()
    End Sub

Protected Overrides Sub Finalize()
        MyBase.Finalize()
    End Sub

End Class

Does anyone know what could be my problem?

[GeekByChoiCe]

i get an error (about wrong typed date etc.)...ok, that's fine, things need to work that way.
But here comes the problem, I correct the date, and then try to insert values again

how about to close the connection if the exception is thrown?

[Peric]

how about to close the connection if the exception is thrown?

But how can I close this connection from another form?
Because this connection is under different class, "cvLicence" is my instance of SQL class and "upis" is a procedure under that class...and it has own method for closing connection but I don't know why it doesn't work if something goes wrong.
And then again...I'm calling this connection for about 20 times, and I think that it isn't solution to close it manually every time after exception is thrown.

[GeekByChoiCe]

Public Sub upis(ByVal upit As String)
Try
     If Not myconnection.State = ConnectionState.Open Then
            myConnection.Open()
        End If

        Dim sq As New SqlCommand(upit, myConnection)
        sq.ExecuteNonQuery()
        myConnection.Close()
catch ex as exception
'makes more sense to catch here the exception 
End Try
    End Sub

[TomW]

Your error should be tripped on the line actually executing your statement. Therefore all that coding in your class sub should be within a Try Catch Finally block. In the finally section you should add the statement to close your connection so that it is closed whether or not an error occurs.

Also you may want to consider just holding your connection string at a global level and recreate db connections as needed in a USING block. I know it sounds odd to keep recreating the same object over & over but my testing has shown it to be faster then re-using it. Also this ensures the connections life only lasts for that of the USING block and that connection pooling can be reset for each of your calls.

[Peric]

Public Sub upis(ByVal upit As String)
Try
     If Not myconnection.State = ConnectionState.Open Then
            myConnection.Open()
        End If

        Dim sq As New SqlCommand(upit, myConnection)
        sq.ExecuteNonQuery()
        myConnection.Close()
catch ex as exception
'makes more sense to catch here the exception 
End Try
    End Sub

This works great...thanks a lot...
just make this correction, I suppose you wrote that accidentally:

If Not myconnection.State = ConnectionState.Closed Then
...

[TomW]

In that example you are still not closing the connection if there is an error. I would add a FINALLY section to that Try/Catch block to close the connection.

[Peric]

In that example you are still not closing the connection if there is an error. I would add a FINALLY section to that Try/Catch block to close the connection.

I'm so sorry...my error was "...current stat is closed."...my mistake. don't worry, this works fine.