Imports System.Data.OleDb
Imports System.Data.DataTable 'Namespace สำหรับคำสั่งเกี่ยวกับฐานข้อมูล Oledb
Imports System.IO
Public Class frmStapleHistory
Dim conStr As String = "Provider=Microsoft.JET.OLEDB.4.0;Data Source=Database/Staple.mdb;"
Dim conn As New OleDbConnection(conStr)
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
OpenDB()
Dim d_ate1 As Integer
Dim m_onth1 As Integer
Dim y_ear1 As Integer
d_ate1 = DateTimePicker1.Value.Day
m_onth1 = DateTimePicker1.Value.Month
y_ear1 = DateTimePicker1.Value.Year
Dim d_ate2 As Integer
Dim m_onth2 As Integer
Dim y_ear2 As Integer
d_ate2 = DateTimePicker2.Value.Day
m_onth2 = DateTimePicker2.Value.Month
y_ear2 = DateTimePicker2.Value.Year
Dim a As String = "#" & d_ate1 & "/" & m_onth1 & "/" & y_ear1 & "#"
Dim b As String = "#" & d_ate2 & "/" & m_onth2 & "/" & y_ear2 & "#"
Dim sql As String
sql = "SELECT Staple_In.Staple_ID, Staple.Staple_Style, Staple_In.Unit,Staple_In.CostPerUnit,Staple_In.Lot,Staple_In.Datetimes,Staple_In.RestInlot FROM Staple INNER JOIN Staple_In ON Staple.Staple_ID = Staple_In.Staple_ID WHERE day(Staple_In.DateTimes) between " & d_ate1 & " and " & d_ate2 & " and month(Staple_In.DateTimes) between " & m_onth1 & " and " & m_onth2 & " and year(Staple_In.DateTimes) between " & y_ear1 & " and " & y_ear2 & ""
Dim cmd As New OleDbCommand(sql, conn)
Dim adapter As New OleDbDataAdapter(cmd)
Dim data As New DataTable()
adapter.Fill(data)
DataGridView1.DataSource = data
FormatDataGridView()
End Sub
Private Sub FormatDataGridView()
With DataGridView1
.Columns(0).HeaderText = "รหัสวัตถุดิบ"
.Columns(1).HeaderText = "ชื่อวัตถุดิบ"
.Columns(2).HeaderText = "จำนวน"
.Columns(3).HeaderText = "ราคาต่อหน่วย"
.Columns(4).HeaderText = "ล๊อต"
.Columns(5).HeaderText = "วันที่"
.Columns(6).HeaderText = "คงเหลือล๊อต"
End With
End Sub
Sub OpenDB() 'เปิดการเชื่อมต่อ DB
With conn 'ทำการเชื่อมต่อกับฐานขัอมูล
If .State = ConnectionState.Open Then .Close()
.ConnectionString = conStr
.Open() 'เปิดการเชื่อมต่อ
End With
End Sub
Private Sub btnReport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnReport.Click
Try
'เคลี ยร์ ค่ าใน dtEx ออก
Dim ds_Ex As New DataSetRe1
If ds_Ex.StapleHistory.Rows.Count > 0 Then
ds_Ex.StapleHistory.Rows.Clear()
End If
'วน loop เพือเก็ บค่าจาก dgv1 ใส่ ใน DataTable StapleHistory
'For i As Integer = 0 To DataGridView1.Rows.Count - 1
'Dim dr As DataGridViewRow
'dr = DataGridView1.Rows(i)
'ds_Ex.StapleHistory.Rows.Add(dr.Cells(0).Value, dr.Cells(1).Value, dr.Cells(2).Value, dr.Cells(3).Value, dr.Cells(4).Value, dr.Cells(5).Value, dr.Cells(6).Value)
'ต้องตรงตามจํานวน column
'Next
'แสดงรายงาน
Dim crv As New CrystalReport1
crv.SetDataSource(ds_Ex)
Dim frm As New CtrViewer_1
frm.CrystalReportViewer1.ReportSource = crv
frm.ShowDialog()
Catch ex As Exception
End Try
End Sub
End Class