Error Dim r As Integer = Command.ExecuteNonQuery()

Imports System.Data
Imports System.Data.SqlClient
Public Class refFrm
    'ประกาศตัวแปรเฉพาะ ที่อ้างอิงในฟอร์ม form_load
    Private HasRows As Boolean = False
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        connection = New SqlConnection(conStr)
        If connection.State = ConnectionState.Closed Then
            connection.Open()
        End If

        SQL = "SELECT * FROM raiooy"
        command = New SqlCommand(sql, connection)
        'อ่านข้อมูลจากฐานข้อมูล
        Reader = Command.ExecuteReader
        'นำค่าที่อ่านได้ไปเก็บในตัวแปร hasrows
        HasRows = Reader.HasRows
        'ถ้ามีข้อมูล ให้แสดงในแท็กต่างๆ
        While Reader.Read
            TextId.Text = Reader("ID")
            DateTimerai.Text = Reader("RaiDate")
            Textr.Text = Reader("Menu")
            Combotype.Text = Reader("Type")
            NumericUpDown1.Text = Reader("Count")
            TextPrice.Text = Reader("Price")
            Texttotal.Text = Reader("Total")
        End While

        Combotype.Items.AddRange(New String() {"รับ", "จ่าย"})
        dataSt = New DataSet()
        bindingSrc = New BindingSource()

        Btnsave.Enabled = False

        If Reader.HasRows Then
            Reader.Close()
        End If
    End Sub
    Private Sub UpdateBinding()
        '-- ป้องกันการ Binding ซ้ำซ้อนโดยเคลียร์การ Binding ก่อนนี้
        For Each ctrl As Control In Me.Controls
            ctrl.DataBindings.Clear()
            If TypeOf ctrl Is TextBox Then
                ctrl.Text = ""
            End If
        Next
     

        Command = New SqlCommand(SQL, Connection)
        Adapter = New SqlDataAdapter(Command)
        dataSt = New DataSet()
        Adapter.Fill(DataST, "raiooy")

        ListBox1.DataSource = DataST.Tables("raiooy")
        ListBox1.DisplayMember = "Type"


        TextId.DataBindings.Add("Text", BindingSRC, "ID")
        DateTimerai.DataBindings.Add("Text", BindingSRC, "RaiDate")
        Textr.DataBindings.Add("Text", BindingSRC, "Menu")
        Combotype.DataBindings.Add("Text", BindingSRC, "Type")
        NumericUpDown1.DataBindings.Add("Value", BindingSRC, "Count")
        TextPrice.DataBindings.Add("Text", BindingSRC, "Price")
        Texttotal.DataBindings.Add("Text", BindingSRC, "Total")
    End Sub
    Private Sub Btnsave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Btnsave.Click
        Command.Parameters.Clear()
        SQL = "INSERT INTO raiooy(RaiDate,Menu,Type,Count,Price,Total)" & "VALUES(@RaiDate,@Menu,@Type,@Count,@Price,@Total)"

        AddParameters(SQL)

    Dim r As Integer = Command.ExecuteNonQuery()
        If r > 0 Then
            MessageBox.Show("บันทึกข้อมูลแล้ว")
            Btnadd.PerformClick()
        Else
            MessageBox.Show("เกิดข้อผิดพลาดในการบันทึกข้อมูล")
        End If
    End Sub
    Private Sub AddParameters(ByVal sql As String)
        Command.Parameters.Clear()
        Command.CommandText = sql
        Command.Parameters.AddWithValue("Menu", Textr.Text)
        Command.Parameters.AddWithValue("Type", Combotype.SelectedItem)
        Command.Parameters.AddWithValue("Count", NumericUpDown1.Value.ToString)
        Command.Parameters.AddWithValue("Price", TextPrice.Text)
        Command.Parameters.AddWithValue("Total", Texttotal.Text)

        Dim dateStr As String = DateTimerai.Value.Year
        dateStr &= "/" & DateTimerai.Value.Month
        dateStr &= "/" & DateTimerai.Value.Day

        Command.Parameters.AddWithValue("RaiDate", dateStr)
        
    End Sub

    Private Sub ListBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ListBox1.SelectedIndexChanged
        BindingSRC.Position = ListBox1.SelectedIndex
    End Sub

    Private Sub Btnadd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Btnadd.Click
        For Each ctrl As Control In Me.Controls
            If TypeOf ctrl Is TextBox Then
                ctrl.Text = ""
            End If
        Next

        '-- กำหนดค่่าต่างๆในขณะการเพิ่มข้อมูล
        If Btnadd.Text = "เพิ่มข้อมูล" Then
            Btnadd.Text = "ยกเลิก"
            Btnsave.Enabled = True
            ListBox1.Enabled = False
            
            Combotype.SelectedIndex = -1    '-- เพื่อบังคับให้ต้องเลือกหมายเลขห้องด้วย
        Else
            Btnadd.Text = "เพิ่มข้อมูล"
            Btnsave.Enabled = False
            ListBox1.Enabled = True
            UpdateBinding()
        End If
    End Sub

    Private Sub Btnedit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Btnedit.Click
        
        SQL = "UPDATE raiooy SET  RaiDate = @RaiDate,  Menu = @ Menu, Type = @Type, " & _
                   "Count = @Count, Price = @Price,Total = @Total"


        SQL &= "WHERE ID = @id"
        AddParameters(SQL)
        Command.Parameters.AddWithValue("id", TextId.Text)

        Dim r As Integer = Command.ExecuteNonQuery()
        If r > 0 Then
            MessageBox.Show("บันทึกการเปลี่ยนแปลงแล้ว")
            UpdateBinding()
        Else
            MessageBox.Show("เกิดข้อผิดพลาดในการแก้ไข")
        End If
    End Sub

    Private Sub Btndelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Btndelete.Click
        If TextId.Text = "" Then
            Exit Sub
        End If

        Dim msgResult As DialogResult
        msgResult = MessageBox.Show("ท่านต้องการข้อมูลของผู้เช่ารายนี้จริงหรือไม่", _
                                    "ยืนยันการลบ", MessageBoxButtons.OKCancel)

        If msgResult = DialogResult.Cancel Then
            Exit Sub
        End If

        SQL = "DELETE FROM raiooy WHERE ID = " & TextId.Text
        Command.Parameters.Clear()
        Command.CommandText = SQL
        Dim r As Integer = Command.ExecuteNonQuery()
        If r > 0 Then
            MessageBox.Show("ลบข้อมูลแล้ว")
            UpdateBinding()
        Else
            MessageBox.Show("เกิดข้อผิดพลาดในการลบข้อมูล")
        End If
    End Sub

End Class