CODEWITHSUNDEEP
sqlvb.net
ruedi
ruedi

Reputation: 5545

Automatically closed db connection within using statement

I wrote this code to write my data into a database

Public Class SQL_Client

    Sub AddData(ByRef myGDI As GeneralInfo)
        Dim conStr As String = "Server=NB01009;Database=DEV_DW;Trusted_Connection=true"

        Dim query As String = String.Empty
        query = "INSERT INTO GeneralInfo(id, name, data) VALUES (@id, @name, @data)"

        Using con As SqlConnection = New SqlConnection(conStr)
            Using comm As New SqlCommand
                With comm
                    .Connection = con
                    .CommandType = CommandType.Text
                    .CommandText = query
                    .Parameters.AddWithValue("@id", myGDI.id)
                    .Parameters.AddWithValue("@name", myGDI.name)
                    .Parameters.AddWithValue("@data", "Hallo Data")
                End With
                Try
                    con.Open()
                    comm.ExecuteNonQuery()
                Catch ex As SqlException
                    Console.WriteLine(ex.Message.ToString(), "Error Message")
                End Try
            End Using

            Dim subquery As String = String.Empty
            query = "INSERT INTO FullData(GeneralInfoID, userRunID VALUES(@GeneralInfoID, @userRunID)"

            For Each myData As FullData In myGDI.data
                Using command As New SqlCommand
                    With command
                        .Connection = con
                        .CommandType = CommandType.Text
                        .CommandText = query
                        .Parameters.AddWithValue("@GeneralInfoID", myGDI.id)
                        .Parameters.AddWithValue("@userRunID", myData.userRunID)
                    End With
                    Try
                        con.Open()
                        command.ExecuteNonQuery()
                    Catch ex As SqlException
                        Console.WriteLine(ex.Message.ToString(), "Error Message")
                    End Try
                End Using
            Next

        End Using
    End Sub
End Class

Since I have a nested object I want to write into the database (see here) I use a loop and a seconed Using Statement. At the second command.ExecuteNonQuery() it get the undhandled Exception: 

System.INvalidOperationException: 'The connection was not closed. The connection's current state is open.'

I actually thought that after leaving the Using statement the connection would be closed automatically. But it looks like this assumption was wrong. Could anyone tell me how to handle this correctly?

Upvotes: 0

Views: 115

Answers (1)

MatSnow
MatSnow

Reputation: 7517

You're opening the connection twice before the connection is closed. It will automatically be closed after the last End Using.

Remove the second con.Open() and it should be fine.

Upvotes: 2

Related Questions