สอบถามเรื่องโค้ด เกี่ยวกับการเชื่อมตารางใน ฟอร์ม vb.net 2010

Imports System.Data
Imports System.Data.OleDb
Public Class Product

    Private connection As OleDbConnection
    Private sql As String
    Private command As OleDbCommand
    Private adapter As OleDbDataAdapter
    Private dataSt As DataSet
    Private bindingSrc As BindingSource
    Dim reader As OleDbDataReader




    Private Sub Product_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        Dim conStr As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\Project55\Database.accdb"
        Dim connection As New OleDbConnection(conStr)

        If connection.State = ConnectionState.Closed Then
            connection.Open()
        End If
        BindingNavigator1.DeleteItem = Nothing
        BindingData()
    End Sub

    Private Sub BindingData(Optional cmd As OleDbCommand = Nothing)
        Dim tbx As TextBox
        Dim pbx As PictureBox
        Dim cmb As ComboBox

        For Each ctrl As Control In Me.Controls
            If TypeOf ctrl Is TextBox Then
                tbx = CType(ctrl, TextBox)
                tbx.DataBindings.Clear()
                tbx.Text = ""
            ElseIf TypeOf ctrl Is PictureBox Then
                pbx = CType(ctrl, PictureBox)
                pbx.DataBindings.Clear()
                pbx.Image = Nothing

            ElseIf TypeOf ctrl Is ComboBox Then
                cmb = CType(ctrl, ComboBox)
                cmb.DataBindings.Clear()
                IIf(cmb.Items.Count > 0, cmb.SelectedIndex = 0, Nothing)
            End If
        Next

        If cmd Is Nothing Then
            command.CommandText = "SELECT * FROM Product"
        Else
            command = cmd
        End If

        adapter = New OleDbDataAdapter(command)
        dataSt = New DataSet()
        adapter.Fill(dataSt, "Product")
        bindingSrc = New BindingSource(dataSt, "Product")

        TextID.DataBindings.Add("Text", bindingSrc, "Pro_ID")
        TextName.DataBindings.Add("Text", bindingSrc, "Pro_Name")

        'TextCost.Text = String.Format("#,#")
        TextCost.DataBindings.Add("Text", bindingSrc, "Cost")
        TextCost.Text = CInt(TextCost.Text).ToString("#,#")

        'TextPrice.Text = String.Format("#,#")
        TextPrice.DataBindings.Add("Text", bindingSrc, "Price")
        TextPrice.Text = CInt(TextPrice.Text).ToString("#,#")

        TextStock.DataBindings.Add("Text", bindingSrc, "Quantity")
        TextDetail.DataBindings.Add("Text", bindingSrc, "Pro_Details")

        PictureBox1.DataBindings.Add("Image", bindingSrc, "Picture", True)

        '-- Complex Binding for Category
        command.CommandText = "SELECT Cat_ID, Cat_Name FROM category"
        adapter.SelectCommand = command
        adapter.Fill(dataSt, "category")
        ComboCategry.DataSource = dataSt.Tables("category")
        ComboCategry.DisplayMember = "Cat_Name"   '-- คอลัมน์ที่ใช้ในการแสดงผล
        ComboCategry.ValueMember = "Cat_ID"       '-- คอลัมน์ที่ใช้เชื่อมโยงกับตารางอื่น
        ComboCategry.DataBindings.Add("SelectedValue", bindingSrc, "Cat_ID")  '-- เพิ่มใน BindingSource


        BindingNavigator1.BindingSource = bindingSrc
        CreateAutoComplete()
    End Sub

    Private Sub LinkLabel1_LinkClicked(sender As System.Object, e As System.Windows.Forms.LinkLabelLinkClickedEventArgs) Handles LinkLabel1.LinkClicked


        OpenFileDialog1.Filter ="Image File(*.jpg,*.png,*.gif,*.bmp)|*.jpg;*.png;*.gif;*.bmp"
        Try
            If OpenFileDialog1.ShowDialog() = DialogResult.OK Then
                PictureBox1.Image = Image.FromFile(OpenFileDialog1.FileName)
            End If
        Catch

            MessageBox.Show("EROOR")
        End Try

    End Sub

    Private Sub SaveToolStripButton_Click(sender As System.Object, e As System.EventArgs) Handles SaveToolStripButton.Click
        If TextID.Text = "" Then
            InsertData()
        Else
            UpdateData()
        End If
    End Sub
    Private Sub InsertData()
        sql = "INSERT INTO Product (Cat_ID, Pro_Name, Pro_Details,Quantity, Cost,Price, Picture)VALUES(@cid, @pid, @name, @dtl,@qut @cot, @prc, @pic)"

        command.CommandText = sql
        command.Parameters.Clear()
        '-- ข้อมูลที่ทำ Complex Binding ต้องอ่านค่าจากพร็อปเพอร์ตี้ SelectedValue 
        '-- ซึ่งเป็นค่า CatID ที่เชื่อมโยงระหว่างตาราง Category กับตาราง Product
        '-- และ SupID ก็เชื่อมโยงระหว่างตาราง Supplier กับตาราง Product เช่นเดียวกัน
        command.Parameters.AddWithValue("cid", ComboCategry.SelectedValue)

        command.Parameters.AddWithValue("name", TextName.Text)
        command.Parameters.AddWithValue("dtl", TextDetail.Text)
        command.Parameters.AddWithValue("qut", TextStock.Text)
        command.Parameters.AddWithValue("cot", TextCost.Text)
        command.Parameters.AddWithValue("prc", TextPrice.Text)


        

        If Not (PictureBox1.Image Is Nothing) Then
            Dim pic() As Byte = ReadImage()
            command.Parameters.AddWithValue("pic", pic)
        Else
            command.Parameters.AddWithValue("pic", DBNull.Value)
        End If

        Dim r As Integer = command.ExecuteNonQuery()
        If r = -1 Then
            MessageBox.Show("เกิดข้อผิดพลาด ไม่สามารถเพิ่มข้อมูลได้")
        Else
            MessageBox.Show("บันทึกข้อมูลแล้ว")
            BindingData()
        End If
    End Sub
    '(@cid, @pid, @name, @dtl,@qut @cot, @prc, @pic)
    Private Sub UpdateData()
        sql = "UPDATE Product  SET Cat_ID = @cid, Pro_Name = @name,Pro_Details =@dte,Quantity = @qut,Cost = @cot,Price = @prc, Picture = @pic WHERE Pro_ID = @pid"

        command.CommandText = sql
        command.Parameters.Clear()
        

        command.Parameters.AddWithValue("pid", TextID.Text)
        command.Parameters.AddWithValue("cid", ComboCategry.SelectedValue)
        command.Parameters.AddWithValue("name", TextName.Text)
        command.Parameters.AddWithValue("dtl", TextDetail.Text)
        command.Parameters.AddWithValue("qut", TextStock.Text)
        command.Parameters.AddWithValue("cot", TextCost.Text)
        command.Parameters.AddWithValue("prc", TextPrice.Text)
  

        If Not (PictureBox1.Image Is Nothing) Then
            Dim pic() As Byte = ReadImage()
            command.Parameters.AddWithValue("pic", pic)
        Else
            command.Parameters.AddWithValue("pic", DBNull.Value)
        End If

        Dim r As Integer = command.ExecuteNonQuery()
        If r = -1 Then
            MessageBox.Show("เกิดข้อผิดพลาด ไม่สามารถแก้ไขข้อมูลได้")
        Else
            MessageBox.Show("ข้อมูลได้รับการแก้ไขแล้ว")
            BindingData()
        End If
    End Sub

    Private Function ReadImage() As Byte()
        Dim memStream As New IO.MemoryStream()
        PictureBox1.Image.Save(memStream, PictureBox1.Image.RawFormat)
        Return memStream.ToArray()
    End Function

    Private Sub BindingNavigatorDeleteItem_Click(sender As System.Object, e As System.EventArgs) Handles BindingNavigatorDeleteItem.Click
        Dim result As DialogResult =
         MessageBox.Show("ท่านต้องการลบข้อมูลลูกค้ารายนี้จริงหรือไม่", "ยืนยันการลบ",
       MessageBoxButtons.OKCancel)

        If result = DialogResult.Cancel Then
            Exit Sub
        End If

        sql = "DELETE FROM Product WHERE Pro_ID = @id"
        command.CommandText = sql
        command.Parameters.Clear()
        command.Parameters.AddWithValue("id", TextID.Text)

        Dim r As Integer = command.ExecuteNonQuery()
        If r = -1 Then
            MessageBox.Show("เกิดข้อผิดพลาด ไม่สามารถลบข้อมูลได้")
        Else
            MessageBox.Show("ข้อมูลถูกลบแล้ว")
            BindingData()
        End If
    End Sub
    Private Sub CreateAutoComplete()
        sql = "Select Pro_Name FROM Product"
        command.CommandText = sql
        reader = command.ExecuteReader()
        Dim autoComp As New AutoCompleteStringCollection()
        While reader.Read()
            autoComp.Add(reader("Pro_Name"))
        End While
        reader.Close()
        TextSearch.AutoCompleteMode = AutoCompleteMode.Suggest
        TextSearch.AutoCompleteSource = AutoCompleteSource.CustomSource
        TextSearch.AutoCompleteCustomSource = autoComp
    End Sub

    Private Sub Buttonok_Click(sender As System.Object, e As System.EventArgs) Handles Buttonok.Click
        If String.IsNullOrEmpty(TextSearch.Text) Then
            BindingData()
            Exit Sub
        End If

        sql = "Select * FROM Product  WHERE ProName Like '%' + @n + '%'"
        command.CommandText = sql
        command.Parameters.Clear()
        command.Parameters.AddWithValue("n", TextSearch.Text)
        BindingData(command)
    End Sub

End Class