Imports System.Data.OleDb
Public Class Mainmenu
Public constring As String = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" _
& Application.StartupPath & "\JarernDB.accdb"
Public con As New OleDbConnection
Public sqlCommand As New OleDbCommand
Public Sub formatGrid()
With DataGridView1
.Columns(0).HeaderText = "รหัสสินค้า"
.Columns(1).HeaderText = "ชื่อสินค้า"
.Columns(2).HeaderText = "หน่วยสินค้า"
.Columns(3).HeaderText = "จำนวนสินค้าในคลัง"
.Columns(4).HeaderText = "ราคาสินค้า"
.Columns(0).Width = 50
.Columns(1).Width = 150
.Columns(2).Width = 110
.Columns(3).Width = 80
.Columns(4).Width = 80
End With
End Sub
Public Sub formatGrid2()
With DataGridView2
.Columns(0).HeaderText() = "รหัสรายการ"
.Columns(1).HeaderText() = "รหัสสินค้า"
.Columns(2).HeaderText() = "ชื่อ"
.Columns(3).HeaderText() = "หน่วย"
.Columns(4).HeaderText() = "ราคา"
.Columns(5).HeaderText() = "จำนวน"
.Columns(6).HeaderText() = "รวม"
.Columns(0).Width = 120
.Columns(1).Width = 105
.Columns(2).Width = 155
.Columns(3).Width = 50
.Columns(4).Width = 100
.Columns(5).Width = 80
.Columns(6).Width = 135
End With
End Sub
Private Sub Mainmenu_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
con.ConnectionString = constring
If con.State = ConnectionState.Closed Then
con.Open()
End If
SetGrid()
formatGrid()
SelectPayType()
formatGrid2()
DataGridView2.DataSource.clear()
End Sub
Public Sub SetGrid()
Dim sqlQuery As String
Dim adapter As New OleDb.OleDbDataAdapter
Dim ds As DataSet = New DataSet
sqlQuery = "SELECT * FROM Tb_Product order by p_code "
DataGridView1.Font = New Font("Arial", 13, FontStyle.Bold)
adapter.SelectCommand = New OleDb.OleDbCommand(sqlQuery, con)
adapter.Fill(ds)
DataGridView1.DataSource = ds.Tables(0)
End Sub
Public Sub SelectPayType()
Dim sqlQuery As String
Dim adapter As New OleDb.OleDbDataAdapter
Dim ds As DataSet = New DataSet
Dim x As Integer
Dim sumamount As Double
sqlQuery = "SELECT * FROM Tb_pay "
DataGridView2.Font = New Font("Arial", 13, FontStyle.Bold)
adapter.SelectCommand = New OleDb.OleDbCommand(sqlQuery, con)
adapter.Fill(ds)
DataGridView2.DataSource = ds.Tables(0)
For x = 0 To DataGridView2.Rows.Count - 1 'คำนวณราคาทั้งหมด
sumamount = DataGridView2.Rows(x).Cells(6).Value + sumamount
Next
TextBox1.Text = sumamount
End Sub
Private Sub Add_product()
Dim sqlQuery As String
Try
sqlQuery = "Insert Into Tb_pay(pa_id,pa_code,pa_name,pa_unit,pa_price,pa_amount,pa_pay)Values('" & Tb_order.Text & "','" & Tb_id.Text & "','" & Tb_name.Text & "','" & Tb_unit.Text & "','" & Tb_price.Text & "','" & num_amount.Value & "','" & Tb_sum.Text & "')"
With sqlCommand
.CommandText = sqlQuery 'บันทึกข้อมูลใหม่
.Connection = con
.ExecuteNonQuery()
End With
Catch ex As Exception
MsgBox(ex.ToString)
End Try
End Sub
Private Sub Del_product()
Dim dtAdapter As OleDb.OleDbDataAdapter
Dim sqlQuery As String
Dim ds As New DataTable
sqlQuery = "DELETE * FROM Tb_pay WHERE pa_code='" & Tb_id.Text & "'"
dtAdapter = New OleDb.OleDbDataAdapter(sqlQuery, con)
dtAdapter.Fill(ds)
End Sub
Private Sub Runnumber()
End Sub
Private Sub B_Exit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles B_Exit.Click
If MsgBox("ต้องการออกจากระบบใช่หรือไม่", vbQuestion + vbYesNo) = vbYes Then
Me.Close()
End If
End Sub
Private Sub B_Storage_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles B_Storage.Click
warehouse.Show()
End Sub
Private Sub B_Search_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles B_Search.Click
Dim sqlQuery As String
Dim adapter As New OleDb.OleDbDataAdapter
Dim ds As DataSet = New DataSet
sqlQuery = "SELECT * FROM Tb_Product Where p_name LIKE '%" & TextBox2.Text & "%'order by p_code"
adapter.SelectCommand = New OleDb.OleDbCommand(sqlQuery, con)
adapter.Fill(ds)
DataGridView1.DataSource = ds.Tables(0)
End Sub
Private Sub DataGridView1_CellClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellClick
Dim i As Integer
i = DataGridView1.CurrentRow.Index
Dim amount As Integer = DataGridView1.Item(3, i).Value.ToString
num_amount.Maximum = amount
Tb_id.Text = DataGridView1.Item(0, i).Value.ToString
Tb_name.Text = DataGridView1.Item(1, i).Value.ToString
Tb_unit.Text = DataGridView1.Item(2, i).Value.ToString
Tb_price.Text = DataGridView1.Item(4, i).Value.ToString
Tb_sum.Text = Tb_price.Text * num_amount.Value
End Sub
Private Sub ToolStripButton1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripButton1.Click
Buyproduct.Show()
End Sub
Private Sub B_submit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles B_submit.Click
Dim sqlQuery As String
Dim ds As New DataTable
Dim dtAdapter As OleDb.OleDbDataAdapter
sqlQuery = "SELECT * FROM Tb_Product WHERE p_code='" & Tb_id.Text & "'"
dtAdapter = New OleDb.OleDbDataAdapter(sqlQuery, con)
dtAdapter.Fill(ds)
If Len(num_amount.Value) = 0 Or Len(Tb_id.Text) = 0 Then
MsgBox("กรุณาใส่จำนวน หรือ เลือกสินค้า", vbInformation)
Exit Sub
End If
Try
sqlQuery = "Update Tb_Product Set p_amount = p_amount- '" & num_amount.Value & "' Where p_code='" & Tb_id.Text & "'"
With sqlCommand
.CommandText = sqlQuery 'แก้ไขข้อมูล
.Connection = con
.ExecuteNonQuery()
End With
Catch ex As Exception
MsgBox(ex.ToString)
End Try
Add_product()
SelectPayType()
SetGrid()
End Sub
Private Sub B_cancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles B_cancel.Click
Dim sqlQuery As String
Dim ds As New DataTable
Dim dtAdapter As OleDb.OleDbDataAdapter
sqlQuery = "SELECT * FROM Tb_pay WHERE pa_code='" & Tb_id.Text & "'"
dtAdapter = New OleDb.OleDbDataAdapter(sqlQuery, con)
dtAdapter.Fill(ds)
Try
sqlQuery = "Update Tb_Product Set p_amount = p_amount+ '" & num_amount.Value & "' Where p_code='" & Tb_id.Text & "'"
With sqlCommand
.CommandText = sqlQuery 'แก้ไขข้อมูล
.Connection = con
.ExecuteNonQuery()
End With
Catch ex As Exception
MsgBox(ex.ToString)
End Try
num_amount.Value = 0
Del_product()
SelectPayType()
SetGrid()
End Sub
Private Sub DataGridView2_CellClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView2.CellClick
Dim i As Integer
i = DataGridView2.CurrentRow.Index
If i = DataGridView2.RowCount - 1 Then Exit Sub
Tb_id.Text = DataGridView2.Item(1, i).Value.ToString
Tb_name.Text = DataGridView2.Item(2, i).Value.ToString
Tb_unit.Text = DataGridView2.Item(3, i).Value.ToString
Tb_price.Text = DataGridView2.Item(4, i).Value.ToString
num_amount.Value = DataGridView2.Item(5, i).Value.ToString
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
End Sub
End Class
Dim count As Int32
query = "SELECT * FROM table WHERE ID = " & txtID.text
Dim cmd As New SqlCommand(sql, cnn)
cmd.Connection.Open()
count = cmd.ExecuteNonQuery
if count >=1 then
sql = "update table SET name = " & txtName.text & " WHERE ID = " & txtID.text
else
sql = "Insert into table values(" & txtID.text &"," & txtName.text & ")"
end if
Module Module1
Public Sub SQLExecuteNonQuery(ByVal sql As String, Optional ByVal strConn As String = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" _
& Application.StartupPath & "\JarernDB.accdb")
Using cnn As New SqlConnection(strConn)
Dim cmd As New SqlCommand(sql, cnn)
cmd.Connection.Open()
cmd.ExecuteNonQuery()
End Using
End Sub
End Module
Load balance : Server 04
