Coffee Shop Management Systems into MS-ACCESS Database Using VB.NET
I am Creating Windows application using VB.Net. In this Article you will learn about Coffee Shop Management Systems into MS-ACCESS Database Using VB.NET Mini Project . Here I am displaying POS system View.
I am Creating windows application in vb.net named Coffee Shop Management Systems. In this application fully billing sysyems i am using frontend visual Basic.net and backend access database.
Step 1 => Create MS-Access Database "coffee.accdb"
Create Table, Table name as "coffeeMaster"
Create Table, Table name as "OrderMaster"
Step 2 => Design a "ManageCoffee" Form's
I am using Following Controls and it's name
txt_coffeeCode.Text : It is used to get the Coffee Code.
txt_Coffeename.Text : It is used to get the Coffee.
Combo_size.Text : It is used to get the Coffee Group.
txt_price.Text : It is used to get the Price.
Check_Status.Checked.ToString : It is used to get the Status Available or Not Available.
Pic_Coffeeimg : It is used to get the Coffee Images.
Following Coding
Imports System.Data.OleDb
Imports System.IO
Public Class frmManageCoffeeEntry
Dim conn As New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Application.StartupPath & "\coffee.accdb"
Private Sub frmManageCoffeeEntry_Load(sender As Object, e As EventArgs) Handles MyBase.Load
End Sub
Private Sub btn_Browse_Click(sender As Object, e As EventArgs) Handles btn_Browse.Click
Dim pop As OpenFileDialog = New OpenFileDialog
If pop.ShowDialog <> Windows.Forms.DialogResult.Cancel Then
Pic_Coffeeimg.Image = Image.FromFile(pop.FileName)
End If
End Sub
Sub clear()
txt_coffeeCode.Clear()
txt_Coffeename.Clear()
txt_price.Clear()
Combo_size.Text = ""
Check_Status.Checked = False
Pic_Coffeeimg.Image = Nothing
End Sub
Private Sub Btn_Save_Click(sender As Object, e As EventArgs) Handles Btn_Save.Click
save()
End Sub
Sub save()
Try
conn.Open()
Dim cmd As New OleDb.OleDbCommand("insert into coffeeMaster(`coffeecode`,`coffeename`,`size`,`price`,`status`,`img`) values (@coffeecode,@coffeename,@size,@price,@status,@img)", conn)
Dim i As New Integer
cmd.Parameters.Clear()
cmd.Parameters.AddWithValue("@coffeecode", txt_coffeeCode.Text)
cmd.Parameters.AddWithValue("@coffeename", txt_Coffeename.Text)
cmd.Parameters.AddWithValue("@size", Combo_size.Text)
cmd.Parameters.AddWithValue("@price", txt_price.Text)
cmd.Parameters.AddWithValue("@status", CBool(Check_Status.Checked.ToString))
Dim FileSize As New UInt32
Dim mstream As New System.IO.MemoryStream
Pic_Coffeeimg.Image.Save(mstream, System.Drawing.Imaging.ImageFormat.Jpeg)
Dim picture() As Byte = mstream.GetBuffer
FileSize = mstream.Length
mstream.Close()
cmd.Parameters.AddWithValue("@img", picture)
i = cmd.ExecuteNonQuery
If i > 0 Then
MsgBox("New Coffee Register Success !", vbInformation)
Else
MsgBox("New Coffee Register Failed !", vbCritical)
End If
Me.Dispose()
Catch ex As Exception
MsgBox(ex.Message)
End Try
conn.Close()
frmManageCoffee.DGV_load()
Form1.imageload()
End Sub
Private Sub Btn_Edit_Click(sender As Object, e As EventArgs) Handles Btn_Edit.Click
edit()
End Sub
Sub edit()
Try
conn.Open()
Dim cmd As New OleDb.OleDbCommand("UPDATE `coffeeMaster` SET `coffeename`=@coffeename,`size`=@size,`price`=@price,`status`=@status,`img`=@img WHERE `coffeecode`=@coffeecode", conn)
Dim i As New Integer
cmd.Parameters.Clear()
cmd.Parameters.AddWithValue("@coffeename", txt_Coffeename.Text)
cmd.Parameters.AddWithValue("@size", Combo_size.Text)
cmd.Parameters.AddWithValue("@price", txt_price.Text)
cmd.Parameters.AddWithValue("@status", CBool(Check_Status.Checked.ToString))
'image convert to binary format
Dim FileSize As New UInt32
Dim mstream As New System.IO.MemoryStream
Pic_Coffeeimg.Image.Save(mstream, System.Drawing.Imaging.ImageFormat.Jpeg)
Dim picture() As Byte = mstream.GetBuffer
FileSize = mstream.Length
mstream.Close()
cmd.Parameters.AddWithValue("@img", picture)
cmd.Parameters.AddWithValue("@coffeecode", txt_coffeeCode.Text)
i = cmd.ExecuteNonQuery
If i > 0 Then
MsgBox("New Coffee Update Success !", vbInformation)
Else
MsgBox("New Coffee Update Failed !", vbCritical)
End If
Me.Dispose()
Catch ex As Exception
MsgBox(ex.Message)
End Try
conn.Close()
frmManageCoffee.DGV_load()
Form1.imageload()
End Sub
Private Sub btn_Delete_Click(sender As Object, e As EventArgs) Handles btn_Delete.Click
DELETE()
End Sub
Sub DELETE()
If MsgBox("Are you Sure Delete This Record !", vbQuestion + vbYesNo) = vbYes Then
Try
conn.Open()
Dim cmd As New OleDb.OleDbCommand("DELETE FROM `coffeeMaster` WHERE `coffeecode`=@coffeecode", conn)
Dim i As New Integer
cmd.Parameters.Clear()
cmd.Parameters.AddWithValue("@coffeecode", txt_coffeeCode.Text)
i = cmd.ExecuteNonQuery
If i > 0 Then
MsgBox("Coffee Deleted Successfully !", vbInformation)
Else
MsgBox("Coffee Deleted Failed !", vbCritical)
End If
Me.Dispose()
Catch ex As Exception
MsgBox(ex.Message)
End Try
conn.Close()
frmManageCoffee.DGV_load()
Form1.imageload()
End If
End Sub
Private Sub txt_price_KeyPress(sender As Object, e As KeyPressEventArgs) Handles txt_price.KeyPress
Select Case Asc(e.KeyChar)
Case 48 To 57
Case 46
Case 8
Case Else
e.Handled = True
End Select
End Sub
End Class
Step 2 => Design a "ManageCoffeeList" Form's
Following Coding
Imports System.Data.OleDb
Imports System.IO
Public Class frmManageCoffee
Dim conn As New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Application.StartupPath & "\coffee.accdb")
Dim dr As OleDbDataReader
Private Sub frmManageCoffee_Load(sender As Object, e As EventArgs) Handles MyBase.Load
DGV_load()
End Sub
Sub DGV_load()
DataGridView1.Rows.Clear()
Try
conn.Open()
Dim cmd As New OleDb.OleDbCommand("SELECT * FROM coffeeMaster", conn)
dr = cmd.ExecuteReader
While dr.Read
DataGridView1.Rows.Add(dr.Item("coffeecode"), dr.Item("coffeename"), dr.Item("size"), dr.Item("price"), CBool(dr.Item("status")), dr.Item("img"))
End While
Catch ex As Exception
MsgBox(ex.Message)
Finally
conn.Close()
End Try
End Sub
Private Sub btn_new_Click(sender As Object, e As EventArgs) Handles btn_new.Click
frmManageCoffeeEntry.ShowDialog()
End Sub
Private Sub DataGridView1_CellClick(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView1.CellClick
With frmManageCoffeeEntry
.txt_coffeeCode.Text = DataGridView1.CurrentRow.Cells(0).Value
.txt_Coffeename.Text = DataGridView1.CurrentRow.Cells(1).Value
.Combo_size.Text = DataGridView1.CurrentRow.Cells(2).Value
.txt_price.Text = DataGridView1.CurrentRow.Cells(3).Value
.Check_Status.Checked = CBool(DataGridView1.CurrentRow.Cells(4).Value)
Dim bytes As [Byte]() = DataGridView1.CurrentRow.Cells(5).Value
Dim ms As New MemoryStream(bytes)
.Pic_Coffeeimg.Image = Image.FromStream(ms)
.Btn_Save.Enabled = False
.txt_coffeeCode.ReadOnly = True
.ShowDialog()
End With
End Sub
End Class
Step 3 => Design a "CancelOrder" Form's
Following Coding
Imports System.Data.OleDb
Imports System.IO
Public Class frmCancelOrder
Dim conn As New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Application.StartupPath & "\coffee.accdb")
Dim dr As OleDbDataReader
Dim i As Integer
Dim btncategory As New Button
Private Sub frmCancelOrder_Load(sender As Object, e As EventArgs) Handles MyBase.Load
orderload()
End Sub
Sub orderload()
DataGridView1.Rows.Clear()
Try
conn.Open()
Dim cmd As New OleDb.OleDbCommand("SELECT sum(`price`) as `price`,`orderno` FROM OrderMaster group by orderno", conn)
dr = cmd.ExecuteReader
While dr.Read
DataGridView1.Rows.Add(dr.Item("orderno"), dr.Item("price"))
End While
Catch ex As Exception
MsgBox(ex.Message)
Finally
conn.Close()
End Try
End Sub
Private Sub txt_search_TextChanged(sender As Object, e As EventArgs) Handles txt_search.TextChanged
DataGridView1.Rows.Clear()
Try
conn.Open()
Dim cmd As New OleDb.OleDbCommand("SELECT sum(`price`) as `price`,`orderno` FROM OrderMaster where orderno like '%" & txt_search.Text & "%' group by orderno ", conn)
dr = cmd.ExecuteReader
While dr.Read
DataGridView1.Rows.Add(dr.Item("orderno"), dr.Item("price"))
End While
Catch ex As Exception
MsgBox(ex.Message)
Finally
conn.Close()
End Try
End Sub
Private Sub DataGridView1_CellContentClick(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView1.CellContentClick
If MsgBox("Are You Sure Cancel this Order", MsgBoxStyle.Information + vbYesNo) = vbYes Then
Dim colname As String = DataGridView1.Columns(e.ColumnIndex).Name
If colname = "Column2" Then
conn.Open()
Dim cmd As New OleDb.OleDbCommand("DELETE FROM `OrderMaster` WHERE orderno=@orderno", conn)
cmd.Parameters.Clear()
cmd.Parameters.AddWithValue("@orderno", DataGridView1.CurrentRow.Cells(0).Value.ToString)
i = cmd.ExecuteNonQuery
If i > 0 Then
MsgBox("Order Cancel Successfully")
End If
End If
conn.Close()
Else
Return
End If
orderload()
End Sub
End Class
Step 4 => Design a "Report" Form's
Following Coding
Imports System.Data.OleDb
Imports System.IO
Public Class frmReport
Dim conn As New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Application.StartupPath & "\coffee.accdb")
Dim dr As OleDbDataReader
Private Sub frmReport_Load(sender As Object, e As EventArgs) Handles MyBase.Load
loadReport()
End Sub
Sub loadReport()
Try
conn.Open()
Dim ds As New DataSet1
Dim da As New OleDb.OleDbDataAdapter
da.SelectCommand = New OleDbCommand("select * from OrderMaster", conn)
da.SelectCommand.Parameters.Clear()
da.Fill(ds, "DataTable1")
Dim rpt As New CrystalReport1
rpt.Load(Application.StartupPath & "\CrystalReport1.rpt")
rpt.SetDataSource(ds.Tables("DataTable1"))
CrystalReportViewer1.ReportSource = rpt
CrystalReportViewer1.RefreshReport()
Catch ex As Exception
MsgBox(ex.Message)
End Try
conn.Close()
End Sub
Private Sub Btn_Filter_Click(sender As Object, e As EventArgs) Handles Btn_Filter.Click
Try
conn.Open()
Dim ds As New DataSet1
Dim da As New OleDb.OleDbDataAdapter
da.SelectCommand = New OleDbCommand("select * from OrderMaster where orderdate between '" & DateTimePicker1.Value.ToString("dd/MM/yyyy") & "' and '" & DateTimePicker2.Value.ToString("dd/MM/yyyy") & "'", conn)
da.SelectCommand.Parameters.Clear()
da.Fill(ds, "DataTable1")
Dim rpt As New CrystalReport1
rpt.Load(Application.StartupPath & "\CrystalReport1.rpt")
rpt.SetDataSource(ds.Tables("DataTable1"))
CrystalReportViewer1.ReportSource = rpt
CrystalReportViewer1.RefreshReport()
Catch ex As Exception
MsgBox(ex.Message)
End Try
conn.Close()
End Sub
End Class
Step 5 => Design a "Billing" Form's
Following Coding
Imports System.Data.OleDb
Imports System.IO
Public Class Form1
Dim conn As New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Application.StartupPath & "\coffee.accdb")
Dim dr As OleDbDataReader
Dim i As Integer
Dim _filter As String = ""
Dim overtotal As Decimal
Private WithEvents pic As New PictureBox
Private WithEvents lbldesc As New Label
Private WithEvents lblprice As New Label
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
lbl_OrderDate.Text = Date.Now.ToString("dd/MM/yyyy")
lbl_orderno.Text = getorderno()
imageload()
End Sub
Function getorderno() As String
Try
Dim sdate As String = Now.ToString("yyyy")
conn.Open()
Dim cmd As New OleDb.OleDbCommand("select * from OrderMaster where orderno like '" & sdate & "%' order by id desc", conn)
dr = cmd.ExecuteReader
dr.Read()
If dr.HasRows Then
getorderno = CLng(dr.Item("orderno").ToString) + 1
Else
getorderno = sdate & "0001"
End If
conn.Close()
Catch ex As Exception
conn.Close()
MsgBox(ex.Message, vbCritical)
End Try
End Function
Private Sub Btn_Exit_Click(sender As Object, e As EventArgs) Handles Btn_Exit.Click
If MessageBox.Show("Are You Sure Exit", "CMS", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = vbYes Then
End
Else
Return
End If
End Sub
Private Sub btn_ManageCoffee_Click(sender As Object, e As EventArgs) Handles btn_ManageCoffee.Click
frmManageCoffee.ShowDialog()
End Sub
Sub imageload()
conn.Open()
Dim cmd As New OleDb.OleDbCommand("SELECT `img`,`coffeecode`,`coffeename`,`size`,`price`,`status` FROM coffeeMaster", conn)
dr = cmd.ExecuteReader
FlowLayoutPanel1.AutoScroll = True
FlowLayoutPanel1.Controls.Clear()
While dr.Read
Dim len As Long = dr.GetBytes(0, 0, Nothing, 0, 0)
Dim array(CInt(len)) As Byte
dr.GetBytes(0, 0, array, 0, CInt(len))
pic = New PictureBox
pic.Width = 120
pic.Height = 150
pic.BackgroundImageLayout = ImageLayout.Stretch
pic.Tag = dr.Item("coffeecode").ToString
lbldesc = New Label
lbldesc.ForeColor = Color.White
lbldesc.BackColor = Color.DodgerBlue
lbldesc.TextAlign = ContentAlignment.MiddleCenter
lbldesc.Dock = DockStyle.Top
lbldesc.Font = New Font("Segoe UI", 8, FontStyle.Bold)
lbldesc.Tag = dr.Item("coffeecode").ToString
lblprice = New Label
lblprice.ForeColor = Color.White
lblprice.BackColor = Color.DarkOrange
lblprice.Dock = DockStyle.Bottom
lblprice.TextAlign = ContentAlignment.MiddleCenter
lblprice.AutoSize = False
lblprice.Font = New Font("Segoe UI", 12, FontStyle.Bold)
lblprice.Tag = dr.Item("coffeecode").ToString
Dim ms As New System.IO.MemoryStream(array)
Dim bitmap As New System.Drawing.Bitmap(ms)
pic.BackgroundImage = bitmap
lbldesc.Text = dr.Item("coffeename").ToString
lblprice.Text = dr.Item("price").ToString
pic.Controls.Add(lbldesc)
pic.Controls.Add(lblprice)
FlowLayoutPanel1.Controls.Add(pic)
AddHandler pic.Click, AddressOf Selectimg_Click
AddHandler lbldesc.Click, AddressOf Selectimg_Click
AddHandler lblprice.Click, AddressOf Selectimg_Click
End While
dr.Dispose()
conn.Close()
End Sub
Public Sub Selectimg_Click(sender As Object, e As EventArgs)
conn.Open()
Dim cmd As New OleDb.OleDbCommand("select * from coffeeMaster where coffeecode like '" & sender.tag.ToString & "%' ", conn)
dr = cmd.ExecuteReader
While dr.Read = True
DataGridView1.Rows.Add(New String() {DataGridView1.Rows.Count + 1, dr.Item("coffeecode").ToString, dr.Item("coffeename").ToString, dr.Item("price"), 1})
End While
conn.Close()
End Sub
Sub QtyValidate()
End Sub
Sub overalltotal()
Dim i As Decimal
Dim sum As Decimal = 0
For i = 0 To DataGridView1.Rows.Count - 1
sum += DataGridView1.Rows(i).Cells(3).Value * DataGridView1.Rows(i).Cells(4).Value
Next
lbl_total.Text = CDec(sum)
End Sub
Private Sub Timer1_Tick(sender As Object, e As EventArgs) Handles Timer1.Tick
overalltotal()
End Sub
Private Sub txt_search_TextChanged(sender As Object, e As EventArgs) Handles txt_search.TextChanged
conn.Open()
Dim cmd As New OleDb.OleDbCommand("SELECT `img`,`coffeecode`,`coffeename`,`size`,`price`,`status` FROM coffeeMaster Where coffeecode like '%" & txt_search.Text & "%' or coffeename like '%" & txt_search.Text & "%'", conn)
dr = cmd.ExecuteReader
FlowLayoutPanel1.AutoScroll = True
FlowLayoutPanel1.Controls.Clear()
While dr.Read
Dim len As Long = dr.GetBytes(0, 0, Nothing, 0, 0)
Dim array(CInt(len)) As Byte
dr.GetBytes(0, 0, array, 0, CInt(len))
pic = New PictureBox
pic.Width = 120
pic.Height = 150
pic.BackgroundImageLayout = ImageLayout.Stretch
pic.Tag = dr.Item("coffeecode").ToString
lbldesc = New Label
lbldesc.ForeColor = Color.White
lbldesc.BackColor = Color.DodgerBlue
lbldesc.TextAlign = ContentAlignment.MiddleCenter
lbldesc.Dock = DockStyle.Top
lbldesc.Font = New Font("Segoe UI", 8, FontStyle.Bold)
lbldesc.Tag = dr.Item("coffeecode").ToString
lblprice = New Label
lblprice.ForeColor = Color.White
lblprice.BackColor = Color.DarkOrange
lblprice.Dock = DockStyle.Bottom
lblprice.TextAlign = ContentAlignment.MiddleCenter
lblprice.AutoSize = False
lblprice.Font = New Font("Segoe UI", 12, FontStyle.Bold)
lblprice.Tag = dr.Item("coffeecode").ToString
Dim ms As New System.IO.MemoryStream(array)
Dim bitmap As New System.Drawing.Bitmap(ms)
pic.BackgroundImage = bitmap
lbldesc.Text = dr.Item("coffeename").ToString
lblprice.Text = dr.Item("price").ToString
pic.Controls.Add(lbldesc)
pic.Controls.Add(lblprice)
FlowLayoutPanel1.Controls.Add(pic)
AddHandler pic.Click, AddressOf Selectimg_Click
AddHandler lbldesc.Click, AddressOf Selectimg_Click
AddHandler lblprice.Click, AddressOf Selectimg_Click
End While
dr.Dispose()
conn.Close()
End Sub
Sub clear()
lbl_orderno.Text = getorderno()
DataGridView1.Rows.Clear()
lbl_total.Text = "000.00"
End Sub
Private Sub Btn_Pay_Click(sender As Object, e As EventArgs) Handles Btn_Pay.Click
Try
conn.Open()
For j As Integer = 0 To DataGridView1.Rows.Count - 1 Step +1
Dim cmd As New OleDb.OleDbCommand("INSERT INTO `OrderMaster`(`orderno`, `orderdate`, `coffeecode`, `coffeename`, `price`, `qty`, `total`) VALUES (@orderno,@orderdate,@coffeecode,@coffeename,@price,@qty,@total)", conn)
cmd.Parameters.Clear()
cmd.Parameters.AddWithValue("@orderno", lbl_orderno.Text)
cmd.Parameters.AddWithValue("@orderdate", lbl_OrderDate.Text)
cmd.Parameters.AddWithValue("@coffeecode", DataGridView1.Rows(j).Cells(1).Value.ToString)
cmd.Parameters.AddWithValue("@coffeename", DataGridView1.Rows(j).Cells(2).Value.ToString)
cmd.Parameters.AddWithValue("@price", DataGridView1.Rows(j).Cells(3).Value.ToString)
cmd.Parameters.AddWithValue("@qty", CDec(DataGridView1.Rows(j).Cells(4).Value.ToString))
cmd.Parameters.AddWithValue("@total", lbl_total.Text)
i = cmd.ExecuteNonQuery
Next
Catch ex As Exception
MsgBox(ex.Message)
End Try
conn.Close()
frmBilling.Show()
clear()
End Sub
Private Sub Btn_NewOrder_Click(sender As Object, e As EventArgs) Handles Btn_NewOrder.Click
clear()
End Sub
Private Sub Btn_CancelOrder_Click(sender As Object, e As EventArgs) Handles Btn_CancelOrder.Click
frmCancelOrder.Show()
End Sub
Private Sub Btn_report_Click(sender As Object, e As EventArgs) Handles Btn_report.Click
frmReport.Show()
End Sub
End Class
Step 5 => Design a "Print Billing" Form's
Following Coding
Imports System.Data.OleDb
Imports System.IO
Public Class frmBilling
Dim conn As New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Application.StartupPath & "\coffee.accdb")
Dim dr As OleDbDataReader
Private Sub frmBilling_Load(sender As Object, e As EventArgs) Handles MyBase.Load
loadReport()
End Sub
Sub loadReport()
Try
conn.Open()
Dim ds As New DataSet1
Dim da As New OleDb.OleDbDataAdapter
da.SelectCommand = New OleDbCommand("select * from OrderMaster where orderno='" & Form1.lbl_orderno.Text & "'", conn)
da.SelectCommand.Parameters.Clear()
da.Fill(ds, "DataTable1")
Dim rpt As New CrystalReport2
rpt.Load(Application.StartupPath & "\CrystalReport2.rpt")
rpt.SetDataSource(ds.Tables("DataTable1"))
CrystalReportViewer1.ReportSource = rpt
CrystalReportViewer1.RefreshReport()
Catch ex As Exception
MsgBox(ex.Message)
End Try
conn.Close()
End Sub
End Class
Thanks
Happy Coding.....
Subscribe For More Projects...
![Inventory Management System in VB.NET and MySql database[Source code] | VB.NET Tutorial](https://lh3.googleusercontent.com/blogger_img_proxy/AEn0k_sVs_kaigjrf9nTwPOPfMaANUUa7TFlaj--5d6P-X-oRhtRC4iUiOBzrsYMSlYKR-eUwaGr_WT3tN2sJIb4xLMJz55Q2lPzGUVhkucyadgatWRR8mT5Vg=w72-h72-p-k-no-nu)
1 Comments
QtyValidate()
ReplyDeleteApakah pada bagian ini memang kosong?