Download : order_northwind.zip

' ปรับปรุง 5 ธันวาคม 2550
Imports system.data.oledb
Public Class myorder
    Dim strdb As String = "c:\northwind.mdb"
    Dim strconn As String = "provider=microsoft.jet.oledb.4.0;data source=" & strdb
    Dim strsql As String
    Dim cn As New System.Data.OleDb.OleDbConnection(strconn)
    Dim cm As New OleDbCommand(strsql, cn)
    Dim dr As OleDbDataReader
    Dim instance As New Form
    Dim lb As New ListBox
    Dim unitinstock As Integer
    Private Sub myorder_Load(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles MyBase.Load
        strsql = "select orderid from [orders] order by orderid desc"
        cm = New OleDbCommand(strsql, cn)
        cn.Open()
        dr = cm.ExecuteReader()
        If dr.Read() Then TextBox1.Text = dr.Item(0) + 1
        cn.Close()
        TextBox1.Enabled = False
        TextBox3.Enabled = False
        TextBox4.Enabled = False
        TextBox6.Enabled = False
        TextBox7.Enabled = False
        TextBox9.Enabled = False
        TextBox10.Enabled = False
        TextBox5.Enabled = False
        TextBox8.Enabled = False
        TextBox11.Enabled = False
        TextBox12.Enabled = False
        TextBox13.Enabled = False
    End Sub
    Sub select_customerid()
        instance = New Form
        lb = New ListBox
        lb.Left = 10 ' pixels
        lb.Width = 200
        lb.BackColor = Color.Yellow
        strsql = "select customerid,companyname,contactname from customers"
        cm = New OleDbCommand(strsql, cn)
        cn.Open()
        dr = cm.ExecuteReader()
        While dr.Read()
            lb.Items.Add(dr.Item(0) & "," & dr.Item(1) & "," & dr.Item(2))
        End While
        cn.Close()
        AddHandler lb.Click, AddressOf lbcustEventHandler
        instance.Size = New Size(300, 200)
        instance.Controls.Add(lb)
        instance.ShowDialog()
    End Sub
    Public Sub lbcustEventHandler(ByVal sender As Object, _
ByVal e As System.EventArgs)
        Dim s As ListBox = sender
        TextBox2.Text = Split(s.Items(s.SelectedIndex.ToString), ",")(0)
        TextBox3.Text = Split(s.Items(s.SelectedIndex.ToString), ",")(1)
        TextBox4.Text = Split(s.Items(s.SelectedIndex.ToString), ",")(2)
        instance.Dispose()
    End Sub
    Private Sub Button1_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles Button1.Click
        select_customerid()
    End Sub
    Sub select_employeeid()
        instance = New Form
        lb = New ListBox
        lb.Left = 10 ' pixels
        lb.Width = 200
        lb.BackColor = Color.LightGreen
        strsql = "select employeeid,lastname,firstname from employees"
        cm = New OleDbCommand(strsql, cn)
        cn.Open()
        dr = cm.ExecuteReader()
        While dr.Read()
            lb.Items.Add(dr.Item(0) & "," & dr.Item(1) & "," & dr.Item(2))
        End While
        cn.Close()
        AddHandler lb.Click, AddressOf lbemplEventHandler
        instance.Size = New Size(300, 200)
        instance.Controls.Add(lb)
        instance.ShowDialog()
    End Sub
    Public Sub lbemplEventHandler(ByVal sender As Object, _
ByVal e As System.EventArgs)
        Dim s As ListBox = sender
        TextBox5.Text = Split(s.Items(s.SelectedIndex.ToString), ",")(0)
        TextBox6.Text = Split(s.Items(s.SelectedIndex.ToString), ",")(1)
        TextBox7.Text = Split(s.Items(s.SelectedIndex.ToString), ",")(2)
        instance.Dispose()
    End Sub
    Private Sub Button2_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles Button2.Click
        select_employeeid()
    End Sub
    Private Sub TextBox5_Leave(ByVal sender As Object, _
ByVal e As System.EventArgs) Handles TextBox5.Leave
        If (Val(TextBox5.Text) > 0) Then
            strsql = "select employeeid,lastname,firstname from employees where employeeid = " & TextBox5.Text
            cm = New OleDbCommand(strsql, cn)
            cn.Open()
            dr = cm.ExecuteReader()
            If dr.Read() Then
                TextBox6.Text = dr.Item(1)
                TextBox7.Text = dr.Item(2)
            End If
            cn.Close()
        End If
    End Sub
    Private Sub Button3_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles Button3.Click
        MsgBox(DateTimePicker1.Text)
    End Sub
    Sub select_productid()
        instance = New Form
        lb = New ListBox
        lb.Left = 10 ' pixels
        lb.Width = 200
        lb.BackColor = Color.LightGreen
        strsql = "select productid,productname,unitprice,unitsinstock from products"
        cm = New OleDbCommand(strsql, cn)
        cn.Open()
        dr = cm.ExecuteReader()
        While dr.Read()
            lb.Items.Add(dr.Item(0) & "," & dr.Item(1) & "," & dr.Item(2) & "," & dr.Item(3))
        End While
        cn.Close()
        AddHandler lb.Click, AddressOf lbproductEventHandler
        instance.Size = New Size(300, 200)
        instance.Controls.Add(lb)
        instance.ShowDialog()
    End Sub
    Public Sub lbproductEventHandler(ByVal sender As Object, _
ByVal e As System.EventArgs)
        Dim s As ListBox = sender
        TextBox8.Text = Split(s.Items(s.SelectedIndex.ToString), ",")(0)
        TextBox9.Text = Split(s.Items(s.SelectedIndex.ToString), ",")(1)
        TextBox10.Text = Split(s.Items(s.SelectedIndex.ToString), ",")(2)
        TextBox13.Text = Split(s.Items(s.SelectedIndex.ToString), ",")(3)
        unitinstock = Split(s.Items(s.SelectedIndex.ToString), ",")(3)
        instance.Dispose()
    End Sub
    Private Sub Button4_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles Button4.Click
        select_productid()
    End Sub
    Private Sub TextBox8_Leave(ByVal sender As Object, _
ByVal e As System.EventArgs) Handles TextBox8.Leave
        If TextBox8.Text.Length > 0 Then
            strsql = "select productid,productname,unitprice,unitsinstock from products where productid= " & TextBox8.Text
            cm = New OleDbCommand(strsql, cn)
            cn.Open()
            dr = cm.ExecuteReader()
            If dr.Read() Then
                TextBox9.Text = dr.Item(1)
                TextBox10.Text = dr.Item(2)
                TextBox13.Text = dr.Item(3)
                unitinstock = dr.Item(3)
            End If
            cn.Close()
        End If
    End Sub
    Private Sub Button5_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles Button5.Click
        Dim x As Integer = DataGridView1.Rows.Count()
        If x = 0 Then
            DataGridView1.Columns.Add("productid", "productid")
            DataGridView1.Columns.Add("productname", "productname")
            DataGridView1.Columns.Add("unitprice", "unitprice")
            DataGridView1.Columns.Add("quantity", "quantity")
            DataGridView1.Columns.Add("discount", "discount")
        End If
        If (Val(TextBox8.Text) > 0 And Val(TextBox11.Text) > 0) Then
            If check_same_product() = 1 Then
                clear_product()
                Exit Sub
            End If
        Else
            Exit Sub
        End If
        x = DataGridView1.Rows.Count() - 1
        DataGridView1.Rows.Add()
        DataGridView1.Rows(x).Cells(0).Value = TextBox8.Text
        DataGridView1.Rows(x).Cells(1).Value = TextBox9.Text
        DataGridView1.Rows(x).Cells(2).Value = TextBox10.Text
        DataGridView1.Rows(x).Cells(3).Value = TextBox11.Text
        DataGridView1.Rows(x).Cells(4).Value = TextBox12.Text
        DataGridView1.Refresh()
        clear_product()
    End Sub
    Sub clear_product()
        TextBox8.Text = ""
        TextBox9.Text = ""
        TextBox10.Text = ""
        TextBox11.Text = ""
        TextBox12.Text = ""
    End Sub
    Function check_same_product() As Integer
        Dim x As Integer = DataGridView1.Rows.Count()
        Dim y As Integer = DataGridView1.Columns.Count()
        Dim i, j As Integer
        Dim s As String = ""
        For i = 0 To x - 1
            If TextBox8.Text = DataGridView1.Rows(i).Cells(0).Value Then
                DataGridView1.Rows(i).Cells(3).Value += Val(TextBox11.Text)
                DataGridView1.Refresh()
                Return 1
            End If
        Next
    End Function
    Private Sub Button6_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles Button6.Click
        Dim x As Integer = DataGridView1.Rows.Count()
        Dim y As Integer = DataGridView1.Columns.Count()
        Dim i, j As Integer
        Dim s As String = ""
        For i = 0 To x - 1
            s &= "- "
            For j = 0 To y - 1
                s &= DataGridView1.Rows(i).Cells(j).Value
            Next
            s &= Chr(10)
        Next
        MsgBox(s)
    End Sub
    Private Sub TextBox2_TextChanged(ByVal sender As Object, _
ByVal e As System.EventArgs) Handles TextBox2.TextChanged
        If (TextBox2.Text.Length > 0) Then TextBox5.Enabled = True
    End Sub
    Private Sub TextBox5_TextChanged(ByVal sender As Object, _
ByVal e As System.EventArgs) Handles TextBox5.TextChanged
        If (TextBox5.Text.Length > 0) Then TextBox8.Enabled = True
    End Sub
    Private Sub TextBox8_TextChanged(ByVal sender As Object, _
ByVal e As System.EventArgs) Handles TextBox8.TextChanged
        If (TextBox8.Text.Length > 0) Then
            TextBox11.Enabled = True
            TextBox12.Enabled = True
        End If
    End Sub
    Private Sub Button7_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles Button7.Click
        strsql = "insert into orders (customerid, employeeid, orderdate)values("
        strsql &= "'" & TextBox2.Text & "'"
        strsql &= "," & TextBox5.Text
        strsql &= ",#" & Format(DateTimePicker1.Text, "short date") & "#)"
        'MsgBox(strsql)
        cm = New OleDbCommand(strsql, cn)
        cn.Open()
        dr = cm.ExecuteReader
        cn.Close()
        '
        Dim x As Integer = DataGridView1.Rows.Count()
        Dim y As Integer = DataGridView1.Columns.Count()
        Dim i As Integer
        For i = 0 To x - 1
            If (DataGridView1.Rows(i).Cells(0).Value > 0) Then
                strsql = "insert into [order details] values("
                strsql &= "" & TextBox1.Text & ""
                strsql &= "," & DataGridView1.Rows(i).Cells(0).Value & ""
                strsql &= "," & DataGridView1.Rows(i).Cells(2).Value & ""
                strsql &= "," & DataGridView1.Rows(i).Cells(3).Value & ""
                strsql &= "," & DataGridView1.Rows(i).Cells(4).Value / 100 & ""
                strsql &= ");"
                cm = New OleDbCommand(strsql, cn)
                cn.Open()
                dr = cm.ExecuteReader
                cn.Close()
                strsql = "update products set "
                strsql &= "unitsinstock = unitsinstock - " & DataGridView1.Rows(i).Cells(3).Value & " "
                strsql &= "where productid =" & DataGridView1.Rows(i).Cells(0).Value & ";"
                MsgBox(strsql)
                cm = New OleDbCommand(strsql, cn)
                cn.Open()
                dr = cm.ExecuteReader
                cn.Close()
            End If
        Next
    End Sub
End Class