hi i currently facing this prblem" the connection was not closed. the connection's current state is open" how to solve it

Imports System.Data.OleDb
Public Class frmOrderSummary
    Dim con As OleDbConnection
    Dim selected_Row As Integer
    Dim selected_ID As String
    Dim order_id, food_id, orderdate, quantity, food_name As String
    Const CONNECTION_STR As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\dbCafeteria.accdb"
    Private Sub frmOrderSummary_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        con = New OleDbConnection(CONNECTION_STR)
        showOrder()
        con.Close()
    End Sub
    Private Sub btnPrint_Click(sender As Object, e As EventArgs) Handles btnPrint.Click
        update_Selected_ID()
        Selected_Order()
        PrintPreviewDialog1.Document = PrintDocument1
        PrintPreviewDialog1.ShowDialog()



    End Sub
    Sub update_Selected_ID()
        If dgOrder.Rows.Count > 0 Then
            selected_Row = dgOrder.CurrentRow.Index
            selected_ID = dgOrder.Rows.Item(selected_Row).Cells(0).Value()
        End If
    End Sub
    Sub showOrder()
        Try
            con.Open()
            Dim sql As String = "SELECT * FROM tblorder"
            Dim cmd = New OleDbCommand(sql, con)
            Dim da = New OleDbDataAdapter(cmd)
            Dim ds = New DataSet()

            da.Fill(ds, "Order")

            dgOrder.DataSource = ds.Tables("Order")
            dgOrder.Columns(0).HeaderText = "Order ID"
            dgOrder.Columns(1).HeaderText = "Food ID"
            dgOrder.Columns(2).HeaderText = "Order Date"
            dgOrder.Columns(3).HeaderText = "Quantity"

            con.Close()
        Catch ex As Exception
            MessageBox.Show("Error" & ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
    End Sub
    Sub Selected_Order()
        Try
            con.Open()
            Dim sql As String = "SELECT * FROM tblOrder where orderID = '" & selected_ID & "'"
            Dim cmd = New OleDbCommand(sql, con)
            Dim da = New OleDbDataAdapter(cmd)
            Dim ds = New DataSet()

            da.Fill(ds, "order")
            If ds.Tables("order").Rows.Count > 0 Then
                order_id = ds.Tables("order").Rows(0).Item(0)
                food_id = ds.Tables("order").Rows(0).Item(1)
                orderdate = ds.Tables("order").Rows(0).Item(2)
                quantity = ds.Tables("order").Rows(0).Item(3)

                getfoodname(ds.Tables("order").Rows(0).Item(1))
            Else
                MessageBox.Show("Order is not exist!")
            End If
            con.Close()
        Catch ex As Exception
            MessageBox.Show("Error" & ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
    End Sub
    Sub getfoodname(ByVal foodid As String)
        Try
            con.Open()
            Dim sql As String = "SELECT name FROM tblFood where id = '" & foodid & "'"
            Dim cmd = New OleDbCommand(sql, con)
            Dim da = New OleDbDataAdapter(cmd)
            Dim ds = New DataSet()

            da.Fill(ds, "food")
            If ds.Tables("Food").Rows.Count > 0 Then
                food_name = ds.Tables("Food").Rows(0).Item(0)
            Else
                MessageBox.Show("Food is not exist!")
            End If
            con.Close()
        Catch ex As Exception
            MessageBox.Show("Error" & ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
    End Sub

    Private Sub PrintDocument1_PrintPage(sender As Object, e As Printing.PrintPageEventArgs) Handles PrintDocument1.PrintPage
        Dim PrintFont As New Font("Arial", 12)
        Dim PrintFont2 As New Font("Arial", 16, FontStyle.Bold)
        Dim LineHeightSingle As Single = PrintFont.GetHeight + 2
        Dim HorizontalPrintLocationSingle As Single = e.MarginBounds.Left
        Dim VerticalPrintLocationSingle As Single = e.MarginBounds.Top

        Dim PrintLineString As String = ""

        Try

            PrintLineString &= "[ Welcome to UCSI Food Ordering System ]" & vbCrLf
            PrintLineString &= "Order ID: " & order_id & vbCrLf
            PrintLineString &= orderdate & vbCrLf

            PrintLineString &= "____________________________" & vbCrLf
            PrintLineString &= "Customer Order :" & vbCrLf
            PrintLineString &= vbCrLf

            PrintLineString &= "Food" & food_name & vbCrLf & "X " & quantity
            PrintLineString &= vbCrLf
            PrintLineString &= "____________________________" & vbCrLf
            PrintLineString &= "Total Due: RM" & (getFoodPrice(food_name) * quantity) & vbCrLf

            e.Graphics.DrawString(PrintLineString, PrintFont, Brushes.Black, HorizontalPrintLocationSingle, VerticalPrintLocationSingle)
        Catch ex As Exception
            MessageBox.Show("Error" & ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
    End Sub

    Private Sub btnExit_Click(sender As Object, e As EventArgs) Handles btnExit.Click
        Dim result = MessageBox.Show("Are you sure?", "Exit", MessageBoxButtons.OKCancel, MessageBoxIcon.Exclamation)
        If result = MsgBoxResult.Ok Then
            frmLogin.Show()
            Me.Close()
        End If
    End Sub
    Function getFoodPrice(ByVal food_name) As String
        Dim result As String = ""
        Try
            con.Open()
            Dim sql As String = "SELECT price FROM tblFood where Name = '" & food_name & "'"
            Dim cmd = New OleDbCommand(sql, con)
            Dim da = New OleDbDataAdapter(cmd)
            Dim ds = New DataSet()

            da.Fill(ds, "price")
            If ds.Tables("price").Rows.Count > 0 Then
                result = ds.Tables("price").Rows(0).Item(0)
            End If
            con.Close()
        Catch ex As Exception
            MessageBox.Show("Error" & ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
        Return result
    End Function
End Class

Hello, you are closing the connection on line 11 which is not correct because you did that at the end of showOrder method.

With DB connection you must pay attention to details and posible scenarios because you must close on each, so I recommend to use try...catch and close it in the finally clause.
If the DB API don't dispose the object you could do it manually. Other thing you could before opening the connection is to check if it is not already opened.

Hopefully this give you direction,
Regards.

Possible on line 51 open con, then when you do getfoodname you again open on line 75

This article has been dead for over six months. Start a new discussion instead.