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