สอบถามการแก้ไขข้อมูลใน Database Access VB.Net โดยใช้คำสั่ง UPDATE

Imports System.Data
Imports System.Data.OleDb
Public Class Form1
    Private conn As OleDbConnection
    Private cmd As OleDbCommand
    Private data As DataSet
    Private adapter As OleDbDataAdapter
    Private bindingScr As BindingSource
    Private sql As String
    Private Sub UpdateBinding()
        TextID.DataBindings.Clear()
        TextName.DataBindings.Clear()
        TextDescr.DataBindings.Clear()
        PictureBox1.DataBindings.Clear()

        sql = "SELECT * FROM Categories"
        cmd = New OleDbCommand(sql, conn)
        adapter = New OleDbDataAdapter(cmd)
        data = New DataSet()
        adapter.Fill(data, "category")

        bindingScr = New BindingSource()
        bindingScr.DataSource = data.Tables("category")

        TextID.DataBindings.Add("Text", bindingScr, "CategoryID")
        TextName.DataBindings.Add("Text", bindingScr, "CategoryName")
        TextDescr.DataBindings.Add("Text", bindingScr, "Description")
        PictureBox1.DataBindings.Add("Image", bindingScr, "Picture", True)


        ListBox1.DataSource = data.Tables("category")
        ListBox1.DisplayMember = "CategoryName"
    End Sub
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim conStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\dknutza\Downloads\New\adddata-3-2ณัฐวัฒน์\Northwind.MDB"
        conn = New OleDbConnection(conStr)
        conn.Open()

        ButtonSave.Enabled = False

        UpdateBinding()

    End Sub
    Private Sub Form1_FormClosing(ByVal sender As System.Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
        conn.Close()
    End Sub
    Private Sub ListBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ListBox1.SelectedIndexChanged
        bindingScr.Position = ListBox1.SelectedIndex
        ToolStripStatusLabel1.Text = "แถวที่:" & (bindingScr.Position + 1) & "/" & bindingScr.Count
    End Sub

    Private Sub LinkLabel1_LinkClicked(ByVal sender As System.Object, ByVal e As System.Windows.Forms.LinkLabelLinkClickedEventArgs) Handles LinkLabel1.LinkClicked
        OpenFileDialog1.Filter = "Image File(*.jpg,*.png,*.gif,*.bmp)|*.jpg;*.png;*.gif;*.bmp"
        If OpenFileDialog1.ShowDialog() = Windows.Forms.DialogResult.OK Then
            PictureBox1.Image = Bitmap.FromFile(OpenFileDialog1.FileName)
        End If

    End Sub
 
    Private Function ReadImage() As Byte()
        Dim memStream As New IO.MemoryStream()

        PictureBox1.Image.Save(memStream, Drawing.Imaging.ImageFormat.Bmp)
        Return memStream.ToArray()
    End Function
    Private Function IsDataComplete()
        TextName.Text.Trim(TextDescr.Text.Trim())

        If TextName.Text = "" OrElse TextDescr.Text = "" Then
            MessageBox.Show("กรุณาใส่ข้อมูลให้ครบ")
            Return False
        Else
            Return True
        End If
    End Function


    Private Sub OpenFileDialog1_FileOk(ByVal sender As System.Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles OpenFileDialog1.FileOk

    End Sub

   
    Private Sub ButtonSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonSave.Click
        If IsDataComplete() = False Then
            Exit Sub
        End If

        sql = "INSERT INTO Categories"
        If Not (PictureBox1.Image Is Nothing) Then
            sql &= "(CategoryName,Description,Picture)"
            sql &= "VALUES(@name,@descr,@pic)"
        Else
            sql &= "(CategoryName,Description)"
            sql &= "VALUES(@name,@descr)"
        End If

        cmd.Parameters.Clear()
        cmd.CommandText = sql
        cmd.Parameters.AddWithValue("name", TextName.Text)
        cmd.Parameters.AddWithValue("descr", TextDescr.Text)

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


        Dim result As Integer = cmd.ExecuteNonQuery()
        If result = -1 Then
            MessageBox.Show("เกิดข้อผิดพลาด ไม่สามารถเพิ่มข้อมูลได้")
        Else
            MessageBox.Show("บันทึกข้อมูลแล้ว")
            ButtonAdd.PerformClick()
        End If

    End Sub

    Private Sub ButtonAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonAdd.Click
        TextID.Text = ""
        TextName.Text = ""
        TextDescr.Text = ""
        PictureBox1.Image = Nothing

        If ButtonAdd.Text = "เพิ่มข้อมูล" Then
            ButtonAdd.Text = "ยกเลิก"
            ButtonSave.Enabled = True
            ListBox1.Enabled = False
        ElseIf ButtonAdd.Text = "ยกเลิก" Then
            ButtonAdd.Text = "เพิ่มข้อมูล"
            ButtonSave.Enabled = False
            ListBox1.Enabled = True

            UpdateBinding()
        End If

    End Sub

    Private Sub ButtonDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonDelete.Click
        If TextID.Text = "" Then
            Exit Sub
        End If

        Dim result As DialogResult = MessageBox.Show("ต้องการลบข้อมูลแถวนี้จริงหรอไม่", "ยืนยันการลบ", MessageBoxButtons.OKCancel)
        If result = DialogResult.Cancel Then

            Exit Sub
        End If


        sql = "DELETE FROM Categories WHERE CategoryID =" & TextID.Text
        cmd.Parameters.Clear()
        cmd.CommandText = sql
        cmd.ExecuteNonQuery()
        UpdateBinding()

    End Sub

    Private Sub ButtonUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonUpdate.Click
        If TextID.Text = "" Or IsDataComplete() = False Then
            Exit Sub
        End If



        Dim s As String
        If Not (PictureBox1.Image Is Nothing) Then
            s = " ,Picture = @pic"
        Else
            s = " "
        End If

        sql = "UPDATE Categories SET CategoryName = @name, Description = @descr, Picture = @pic WHERE CategoryID = @id"

        cmd.Parameters.Clear()
        cmd.CommandText = sql
        cmd.Parameters.AddWithValue("name", TextName.Text)
        cmd.Parameters.AddWithValue("descr", TextDescr.Text)
        cmd.Parameters.AddWithValue("id", TextID.Text)


        If Not (PictureBox1.Image Is Nothing) Then
            Dim byteArr() As Byte = ReadImage()
            cmd.Parameters.AddWithValue("pic", byteArr)
        End If

        If cmd.ExecuteNonQuery() = 1 Then
            MessageBox.Show("บันทึกการเปลี่ยนแปลงแล้ว")
            UpdateBinding()
        Else
            MessageBox.Show("เกิดข้อผิดพลาด")
        End If
    End Sub

End Class